May 7, 2012 at 5:35 am
Experts,
SQL Server 2005 Analysis Services.
I want to export data from cube to CSV file.
Have done the same for normal OLTP database. But never did from Cubes.
Can anyone help me pls. Would be great if you can explain step by step.
Basically I am new to this and it's bit urgent.
Any help would be highly appreciated.
Thanks in advance.
Smith.
May 7, 2012 at 11:46 pm
None has done it before ??
Thanks,
Smith
May 8, 2012 at 5:46 am
I can offer a couple of idea...
1. You can write the MDX and export the query results.
2. Use Report Builder to create a Report which you can then export to Excel, CSV, pdf.
3. Use the Drill down functionality in the cubes to drill into the detail from an Excel pivot table
May 8, 2012 at 8:20 am
Thanks a lot for the response.
1. You can write the MDX and export the query results.
I have prepared the query and put in a "Execute SQL Task". But I don't understand how can I export it to CSV file now.
2. Use Report Builder to create a Report which you can then export to Excel, CSV, pdf.
Number of records are millions. Do you think it will work fine in terms of speed etc; ?
Data has to be fetched from multiple dimensions from the cube.
3. Use the Drill down functionality in the cubes to drill into the detail from an Excel pivot table
I completely unaware about it, think either of the above solutions should be OK for me.
Kindly guide me how to proceed on this.
Thanks a ton.
Joy.
May 8, 2012 at 8:33 am
Are you using SSIS? MDX is not SQL and thus you cannot execute from a SSIS SQL Task (unless you create a linkedserver and an open query). That is completely different. If you are exporting millions of rows, I am not sure SSAS/cube is the correct tool. I would recommend exporting directly from the DataWarehouse via SQL.
Report Builder should still work as would SSRS... basically you would create a report to export to CSV even with that many rows.
May 8, 2012 at 8:45 am
Are you using SSIS? MDX is not SQL and thus you cannot execute from a SSIS SQL Task (unless you create a linkedserver and an open query). That is completely different.
Yes I am using SSIS. "Execute SQL" task validated my MDX query. I selected OLEB for Analysis service as provider.
Though I din't ran it, looks fine 🙂
If you are exporting millions of rows, I am not sure SSAS/cube is the correct tool. I would recommend exporting directly from the DataWarehouse via SQL.
Can you pls explain a bit more or can you give me some links so that I can go through it.
Report Builder should still work as would SSRS... basically you would create a report to export to CSV even with that many rows.
I will try that. Since am new to this and totally confused, can you brief the steps I need to take with the query I have prepared.
Thanks a lot.
May 8, 2012 at 9:07 am
Joy Smith San (5/8/2012)
Are you using SSIS? MDX is not SQL and thus you cannot execute from a SSIS SQL Task (unless you create a linkedserver and an open query). That is completely different.
Yes I am using SSIS. "Execute SQL" task validated my MDX query. I selected OLEB for Analysis service as provider.
Though I din't ran it, looks fine 🙂
You can just create your MDX query then to use in your Data Flow source. You can do this in SSMS or you can build it by hand in the OLEDB Source.
Next Add a Data flow and then Create an Data Flow Source OLEDB Source and use the MDX query you created above as . Create a Data Flow Flat File Destination for your CSV result.
If you are exporting millions of rows, I am not sure SSAS/cube is the correct tool. I would recommend exporting directly from the DataWarehouse via SQL.
Can you pls explain a bit more or can you give me some links so that I can go through it.
Same process as above except instead of using the MDX,instead using regular sql.
You would add dataflow... add an OLEDB Source to the dataflow.... insert your sql statement into the OLEDB source. Then add a Flat File Destination....
There are plenty of sites to teach you to do this.. just do a google search.
Report Builder should still work as would SSRS... basically you would create a report to export to CSV even with that many rows.
I will try that. Since am new to this and totally confused, can you brief the steps I need to take with the query I have prepared.
This process is more than a brief few steps. Do a google search on creating a SSRS report.
Thanks a lot.
May 8, 2012 at 11:38 am
Joy Smith San (5/8/2012)
If you are exporting millions of rows, I am not sure SSAS/cube is the correct tool. I would recommend exporting directly from the DataWarehouse via SQL.
I agree.
Why would you want to export as csv from the cube?
And how do you intend to use this file?
It could end up being larger than windows could handle.
May 9, 2012 at 12:44 am
I agree.
Why would you want to export as csv from the cube?
And how do you intend to use this file?
It could end up being larger than windows could handle.
CSV data is the input for someother application which we we are not aware of.
We are doing this as SSIS package from the current OLTP DB, run once in week and works fine as well.
They want the data from cube, because all the reports in the new version of the application is based on cubes, and it will be much faster than the normal OLTP system as the data will be saved in cubes and these cubes can be used for reporting purposes.
Thanks,
Smith.
May 9, 2012 at 8:32 am
Exporting from a cube does not 'give' you a cube, only data at a summary level.
An SSAS cube can be backed up and restored on another machine, if they both are using SSAS cubes.
And exposing the current cube to the the application would be preferred (at least in our environment) if at all possible.
May 10, 2012 at 12:12 am
Exporting from a cube does not 'give' you a cube, only data at a summary level.
We want data only, in CSV format. We don't want cube.
We hide "mesure value", and display only other "details".
Thanks.
May 10, 2012 at 4:29 am
A new problem now.... :w00t:
At last I did it with "OLEDB DataSource."
Put the MDX query in "SQL Command" screen (It was quite confusing... I was expecting "MDX command" there) and it was showing preview properly. But when I execute it fails with the following errors.
"Error: 0xC0202009 at Data Flow Task, OLE DB Source 1 [222]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.
Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "OLE DB Source 1" (222) failed the pre-execute phase and returned error code 0xC0202009."
It's SQL Server 2005 with SP4. I am not able to find any solution from google as well though someone has reported this issue.
Can anyone help pls.
Thanks,
Smith.
May 10, 2012 at 2:36 pm
Can you please post the MDX query you are using. It is most likely a data type problem that is preventing the package from executing.
The way you are extracting data from a OLAP Cube is very unconventional (in my experience), so you may it difficult to get help on this topic. I will do my best to help though.
May 11, 2012 at 12:46 am
Can you please post the MDX query you are using. It is most likely a data type problem that is preventing the package from executing.
SELECT NON EMPTY { [Measures].[cmp Count] } ON COLUMNS,
NON EMPTY
{(
[ASE].[mnfctr].[mnfctr].ALLMEMBERS
* [ASE].[pd nm].children
* [ASE].[pd Version].[pd Version].ALLMEMBERS
* [ASE].[File nm].[File nm].ALLMEMBERS
* [cmp].[cmp nm].[cmp nm].ALLMEMBERS
)
} ON ROWS
FROM ( SELECT ( Filter( [cl].[cl nm].[cl nm].ALLMEMBERS
, [cl].[cl nm].currentmember.Properties( 'Member_Caption' ) = 'All cmps') ) ON COLUMNS
FROM [AS - Std]
)
I have one more doubt.
There are many dimensions in one of the data source.
And for a particular cube only the necessary dimensions have been included.
But there will be too many records in that cube when we process.
we do not want all the records to be available in cube.
For eg. that cube should have data for specific applications only.
I don't understand where can I filter for those applications.
I CANNOT filter the records in DSV as it's used in other Cubes as well.
Also, don't want to filter at report side as there are some 100 application names.
Can you pls guide on this as well.
The way you are extracting data from a OLAP Cube is very unconventional (in my experience), so you may it difficult to get help on this topic. I will do my best to help though.
Thanks a ton for your support.
May 11, 2012 at 7:02 am
Hello,
It looks like you are attempting to return a large number of records. I would recommend you first create a new MDX Query that returns a small amount of data across a single dimension just to make sure you have setup the permissions properly. Once that is working you can go back to your orginal statement but include a condition to make it a small subset of the data.
As for your second question, if I understand correctly I think you will need to create separate partitions in your cube to address those issues. If you create separate partitions you can limit the data size without affecting the other cubes.
Please let me know how that works out for you.
James
Viewing 15 posts - 1 through 15 (of 37 total)
You must be logged in to reply to this topic. Login to reply