Outside of Report script or HAL, is there a different method of extracting data out of ASO cube? I have an ASO cube with 17 dimensions but I need to extract subset data of the cube to put into different SQL database. Thanks!
Scoring disabled. You must be logged in to score posts.
The best method for extracting data, currently, is to use Report scripts.
See this blog post
http://essbaselabs.blogspot.com/2009/06/speeding-up-aso-extracts.html
Other method would be to use MDX, but as you will see in the blog post, MDX did not render as good a result as the report script.
If this is something you have to do on a regular basis and performance is a factor, you might want to talk to the guys at Star Analytics and look at their product.
Scoring disabled. You must be logged in to score posts.
Star Analytics: A nice tool that serves a great need, but not in this case. I'm sure it uses MDX expressions to extract data from ASO cubes, so it has the same limitations.
Didn't they used to have a free version? What happened to that?
Scoring disabled. You must be logged in to score posts.
I just had a request to export an ASO cube. I prototyped exports using MDX and Report Scripts, and based on some small samplings, it would have taken months to export everything, and the output would have been unwieldy.
We ended up finding another solution to the problem (that didn't involve exporting the cube).
Scoring disabled. You must be logged in to score posts.
FYI, there is another way. We developed an SSIS package that reads through all of the 'txt' files created from a full database export. The steps were as follows:
1. Read the outline (using VBA) and generate a unique index number for each Level 0 member
2. Build Dimension tables in SQL server from the Essbase dimensions
3. Read the export files one by one from the export folder and for each (using a script process):
- Read the first line (compression dimension entries specifying the data order)
- Read the subsequent lines converting dimension members to index keys to save space
- Create output rows which are fed into a Fact table
This all took some time as we were newbies to SQL Server when we started but now works well. We have also refined the process so that we can use 'filters' to select particular Measures/Time periods to read into the tables. We then have alternative EIS Models/Metaoutlines pointing at this tables to re-read the data back into ASO cubes if necessary.
Post a reply with a contact email if you want more info
Scoring disabled. You must be logged in to score posts.
I don't want to sound ignorant about this, but what is the point of this?
ASO exports would be level 0 only, so if I understand correctly, you are exporting level 0 data from ASO, loading into a relational table and then using other scripts to mimic the outline is SQL so you can use SQL queries to run reports and generate aggregated values.
Doesn't that seem to defeat the entire purpose?
If you ask me, in almost all of these situations, the process is backwards. To me ASO should be the end of the line where data goes to aggregate and be reported on. In all our ASO models, all data or staged in relational models ahead of time. If anyone needs subsets of level 0 data we can easily run from SQL, aggregated values come from ASO because it is better at aggregating values.
Scoring disabled. You must be logged in to score posts.
You are (as ever!) spot on and in an ideal world we would stage in a relational DW first then populate the ASO cube second.
However, we have a situation where we have a small processing window. Tables are made available to us around 7:00. We have a limited amount of time to update the cube with prior day data before the start of the business day. Therefore we made the decision to update Essbase directly from the source tables.
In addition our option allows us to archive off selected historical data. For example, for some Measures we only need to archive the month-end (i.e. last day of the month) position.
I am sure there are others out there too who, for various reasons, cannot or do not mirror the source data in relational tables and for whom this type of export may be of use.
I know this is heresy and I should flayed alive for taking this approach, but hey nobody died...yet
Scoring disabled. You must be logged in to score posts.
