Migrating to cloud is sometimes hard when few things are on premise and few are moved to cloud. It becomes harder when cloud is connecting back to on premise systems. Say we have a large warehouse on premise and some application is on cloud and now cloud application wants to connect to this warehouse.
Some cloud providers came with *** Direct Connect, few came with **** Connect.
We may have to open the system and ports to public network and maintain those ports backed by strong IT team. Well, good luck with that!
One other approach could be to upload the huge files and process those files on cloud. Not a bad approach security wise but time consuming.
What could be really cool is someone comes up with a solution where we do not have to open the ports but we can stream on premise data to the cloud using industry standard HTTPS.
Wish listened! OAC- Essbase streaming command line will allow us to do that as it comes with a tiny stream loader for a giant problem.
Let's try it out...
In this example, we will try to load the data from MySQL database which is on premise and we will load the data to famous Sample/Basic cube.
Your database can be any database which supports JDBC. e.g. Redshift, SQL Server, Teradata, DB2, SalesForce, ...
I created a sample data table in MySQL like below:
Now created a rule file like below with SQL query inside it. We can keep the query empty in rules and dynamically provide as well. Query should be JDBC compliant and should not contain any Essbase substitution variables.
Download Essbase CLI and install it at your favorite location.
login to CLI.
>esscs.bat login -url https://host/essbase -u
Create a local connection so that it can be used multiple times or in scripting.
>esscs.bat createlocalconnection -cs "jdbc:mysql://localhost:3306/world" -D "com.mysql.cj.jdbc.Driver" -u <user> -p <password> -N mysql1
Before trying out actual data load or dim build , make sure that you add an EXTERNAL_CLASSPATH with JDBC jars of your database.
Only Oracle DB jdbc jars are provided with CLI.
e.g. echo command on windows box should print your jar like below.
>echo %EXTERNAL_CLASSPATH%
D:\study\mysql\mysql-connector-java-8.0.11\mysql-connector-java-8.0.11.jar
Now, load the data to database using below command:
>esscs.bat dataload -application Sample -db Basic -rule data1.rul -user root -stream -connection mysql1
Streaming to Essbase...
........
Streamed 4224 rows to cube
In the CLI/log directory, a log file will have an entry with time taken to load these many records.
Total time taken for streaming 0 secs 270 millis
Note: Rule file should exist in the DB directory.
You can use this for building a dimension as well. See the details here:
https://docs.oracle.com/en/cloud/paas/analytics-cloud/essug/loading-dimensions-and-data-streaming-remote-database.html
Try it out with your favorite database and let us know if you like it.
This command only supports loading the data from JDBC sources; what if we have a legacy system which cannot expose JDBC but we want to load data from that...
Can OAC expose a REST api to achieve this??
Stay tuned!
Some cloud providers came with *** Direct Connect, few came with **** Connect.
We may have to open the system and ports to public network and maintain those ports backed by strong IT team. Well, good luck with that!
One other approach could be to upload the huge files and process those files on cloud. Not a bad approach security wise but time consuming.
What could be really cool is someone comes up with a solution where we do not have to open the ports but we can stream on premise data to the cloud using industry standard HTTPS.
Wish listened! OAC- Essbase streaming command line will allow us to do that as it comes with a tiny stream loader for a giant problem.
Let's try it out...
In this example, we will try to load the data from MySQL database which is on premise and we will load the data to famous Sample/Basic cube.
Your database can be any database which supports JDBC. e.g. Redshift, SQL Server, Teradata, DB2, SalesForce, ...
I created a sample data table in MySQL like below:
Now created a rule file like below with SQL query inside it. We can keep the query empty in rules and dynamically provide as well. Query should be JDBC compliant and should not contain any Essbase substitution variables.
Download Essbase CLI and install it at your favorite location.
login to CLI.
>esscs.bat login -url https://host/essbase -u
Create a local connection so that it can be used multiple times or in scripting.
>esscs.bat createlocalconnection -cs "jdbc:mysql://localhost:3306/world" -D "com.mysql.cj.jdbc.Driver" -u <user> -p <password> -N mysql1
Before trying out actual data load or dim build , make sure that you add an EXTERNAL_CLASSPATH with JDBC jars of your database.
Only Oracle DB jdbc jars are provided with CLI.
e.g. echo command on windows box should print your jar like below.
>echo %EXTERNAL_CLASSPATH%
D:\study\mysql\mysql-connector-java-8.0.11\mysql-connector-java-8.0.11.jar
Now, load the data to database using below command:
>esscs.bat dataload -application Sample -db Basic -rule data1.rul -user root -stream -connection mysql1
Streaming to Essbase...
........
Streamed 4224 rows to cube
In the CLI/log directory, a log file will have an entry with time taken to load these many records.
Total time taken for streaming 0 secs 270 millis
Note: Rule file should exist in the DB directory.
You can use this for building a dimension as well. See the details here:
https://docs.oracle.com/en/cloud/paas/analytics-cloud/essug/loading-dimensions-and-data-streaming-remote-database.html
Try it out with your favorite database and let us know if you like it.
This command only supports loading the data from JDBC sources; what if we have a legacy system which cannot expose JDBC but we want to load data from that...
Can OAC expose a REST api to achieve this??
Stay tuned!
Thanks for the good article. Are you going to support substitution variables in the future? IT is very typical to want only part of the data. For example in a GL cube to load the current year except at the beginning of the year. I will usually HAVE TWO VARIABLES &sQLStartYear AND &SQLEndYear so the admins can control the process without having to modify the SQL
ReplyDeleteAgree with Glenn that it would be great to include the use of substitution variables.
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteYes, substitution variables will be supported.
ReplyDelete