SQL Connecting Directly into Essbase

SQL Connecting Directly into Essbase

jacques
jacques

January 6th, 2011, 10:33 am #1

Hi

This is a strange request from one of my users,

Is it possible to link a SQL database to Essbase and move the data from Essbase into the SQL without using the text file.

I am not sure why the user is using SQL and not Essbase but I agreed to investigate the request, has anyone tried this and if so is ther perhaps any documentaion I can read

Thanks
Quote
Share

Cameron Lackpour
Cameron Lackpour

January 6th, 2011, 12:36 pm #2

The most simple is SQL load rules:
http://download.oracle.com/docs/cd/E172 ... aunch.html

The old fashioned way is EIS:
http://download.oracle.com/docs/cd/E172 ... aunch.html
http://download.oracle.com/docs/cd/E172 ... aunch.html

The modern way:
http://download.oracle.com/docs/cd/E172 ... aunch.html

These links and more are available on:
http://download.oracle.com/docs/cd/E172 ... rtal_3.htm

SQL load rules are just like regular load rules except the data source is a table, not a file. And oh yes, you can do SQL manipulation via the SELECT statement, although that isn't a great place to do it.

EIS and Studio make sense in the context of a true data warehouse with a nice star schema all ready to go (or something pretty darn close).

It has been my experience that many data warehouses are "We dump all of our completely unrelated tables used for querying into schema/database X. Henceforth, schema/database X is our Data Warehouse." If that's the case, you (or someone) are going to spend a lot of time writing SQL trying to get the tables into a format that is ready for EIS/Studio. Actually, no matter what tool you use, a lot of time is likely to be spent writing SQL to get meaningful data/metadata out.

Once upon a time, Hyperion charged dearly for the SQL interface and its use was limited. Since Oracle took over, it's been included in the base Essbase license and I see it everywhere.

I'm not 100% sure of this, but I *believe* Studio/EIS are also now part of the standard install.

Regards,

Cameron Lackpour
Quote
Share

Jacques
Jacques

January 6th, 2011, 12:50 pm #3

Hi Cameron

Thanks for the information, I understand the process via SQL -> EIS -> Essbase

What the user wants is Essbase data -> SLQ (without useing a text file or excel file).

Basically they do not want to load the data file generated via jexport from Essbase, they are looking for a way to load the SQl database using a direct connection to Essbase.

Essbase -> SQL

Just from an ODBC settting point of view, it does not seem possible to create this for Essbase, I always used essbase as the end result as it pulls data form any data source, I have never tried to use Essbase as a database source to load another database (if this make sense).

In excel the VBS allows to create a connection via commants.

While typing this response I just realised that even if there is a way to load/create a SQL table, it will still neeed to be done manually, I cannot remember if I ever saw a automated task schedular in SQl to automate loading, I have always loaded data manaully - trust a user to come up with this type of query.
Quote
Share

Javier
Javier

January 6th, 2011, 1:06 pm #4

You could look into a calc script / business rule to use the DATAEXPORT command and write directly to a DSN target. It might take forever but, if parameterized, your users could choose what they want to query via SQL.

Disclaimer: I've never exported directly to a table.
Quote
Share

Bob
Bob

January 6th, 2011, 1:22 pm #5

This works well; we did it to move data from one cube to another, while keeping all of the source data for cubes in one place (Oracle).

Another option would be to run an export/report to put the data to an external table for your RDBMS. This can be easier for end users and more easily maintained, security wise.
Quote
Share

Azmat Bhatti
Azmat Bhatti

January 6th, 2011, 2:16 pm #6

Hi

This is a strange request from one of my users,

Is it possible to link a SQL database to Essbase and move the data from Essbase into the SQL without using the text file.

I am not sure why the user is using SQL and not Essbase but I agreed to investigate the request, has anyone tried this and if so is ther perhaps any documentaion I can read

Thanks
Hi,

Yes, i have done this in the past. We wrote a maxl script to extract data out into a txt format and mapped the SQL table layout fields. Once the file was placed on the SQL server, a job ran on the server to load the data into SQL.

Thanks,

Azmat Bhatti
Quote
Share

Cameron Lackpour
Cameron Lackpour

January 6th, 2011, 2:20 pm #7

This works well; we did it to move data from one cube to another, while keeping all of the source data for cubes in one place (Oracle).

Another option would be to run an export/report to put the data to an external table for your RDBMS. This can be easier for end users and more easily maintained, security wise.
The export to a table via DATAEXPORT works pretty well, and it can be run from Business Rules, so you can parameterize it to your heart's content and execute it that way.

NB -- Business Rules can talk directly to Essbase; Planning is not required.

Glenn Schwartzberg did a great multipart post on DATAEXPORT.

This (I think) is the first:
http://glennschwartzbergs-essbase-blog. ... great.html

Regards,

Cameron Lackpour
Quote
Share

Cameron Lackpour
Cameron Lackpour

January 6th, 2011, 2:23 pm #8

Which can also export to SQL, as can Star Analytics' Star Integration Server.

Regards,

Cameron Lackpour
Quote
Share

Indra
Indra

January 6th, 2011, 3:13 pm #9

Hi

This is a strange request from one of my users,

Is it possible to link a SQL database to Essbase and move the data from Essbase into the SQL without using the text file.

I am not sure why the user is using SQL and not Essbase but I agreed to investigate the request, has anyone tried this and if so is ther perhaps any documentaion I can read

Thanks
Hi,


No, you cannot go from Essbase --> SQL without any additional tools (ODI or Informatica with Essbase Adapter).
The only available option is using text file which are already mentioned above.
HTH.


Indra
Quote
Share

GlennS
GlennS

January 6th, 2011, 9:47 pm #10

Indra,
You are oncorrect. AS others have posted, you could use the DataExport command with the relational option to load into SQL. Someone mentioned Jexport, which if you set up the supporting files correctly can import to relational using the odbc-jdbc bridge, Star analytics can do it or you could write something with one of the APIs to extract from Essbase and load into SQL.

I've used both jexport and Dataexport to do this task extensively. each as its advantages.
Cameron, thanks for the blog plug.
Quote
Share


Confirmation of reply: