Extracting data from SSAS cube into a table using SSIS

Question

Hi,

I was trying to extract data from an SSAS cube into a relational table, basically create a fact table out of the cube, where I'd have the dimension's natural keys and the measure values.

I tried using the Microsoft OLE DB Provider for Analysis Services 9.0 to connect to the cube, and using the SQL Command option I was able to query the dimension tables (select * from [$Date].[$Date]) and push the data into a relational table. However for the cube data, I was only able to query the measure group and the cube dimension values separately. Apparently the 'tables' in which the measure group data and the cube dimension data is different, and I'm not able to find a relationship between them. Also for the cube dimension data I see one table for each cube-dimension combination. Am I taking the right approach?

I also tried using Linked Server to my OLAP database, and then use MDX to get data, but the problem remained that I wasnt able to write a MDX that would just give me rows of data with all the dimension keys and measures flattened out in a table.

Is there any other approach to get data from the cubes into a relational table?

Thanks

Answer 1

Hi,

another option is to use DRILLTHROUGH (drillthrough is an operation when you specify a cube cell and query returns all individual items that contributed to that cell) mdx command, example:

DRILLTHROUGH MAXROWS 10
SELECT
    {[Measures].[Store Sales]} on 0,
    {[Customer].[Geography].[USA]} on 1
FROM [Sales]
WHERE
    {[Time].[Year].[2005]}
RETURN
    [Measures].[Store Sales] AS [Sales],
    [Measures].[Store Cost] AS [Cost],
    [Measures].[Unit Sales] AS [Units Sold],
    Caption[Customer].[Name]) AS [Customer Name],
    Caption([Store].[Name]) AS [Store Name],
    Caption([Product].[Name]) AS [Product Name],
    MemberValue([Time].[Day]) AS [Date Of Sale]


Radim

Answer 2

There are 2 simple ways  (There are many ways but in the end it all comes to use the correct prvider , credentials and MDX)

1. Created Linked Server and use OPENQUERY http://geekswithblogs.net/darrengosbell/archive/2006/04/26/76418.aspx

2. USE OLEDB Provider for MSOLAP

1. Create a New SSIS package
2. Add Execute SQL Task
3. Select Provider as MSOALP (i selected Microsoft OLEDB Provider For analysis Service 10 as I have SQL 2008 but for 2005 it is 9 and 2000 it is 8 and so on)
4. Create connection to the SSAS engine using the above mentioned provider
5. Paste the MDX  query in the SQL Statement

3. Use Data Flow Task

          1. Create a new SSIS package

2. Drag and Drop a DFT

3. In the source specify the connection to SSAS database using MS OLAP    provider which you see in the list ( I think it is same as above)

4. In the destination specify the table where you want to dump this data

 

There are some issues with SSIS running MDX. If you open profiler you will see that it run the MDX 2 times. But if you look at the XMLA you can figure out the difference. Have a look. I am leaving it you to figure out why it runs 2 times and if you don’t find out then let me know.

I would also advise you to set validation of metadata = false

 

 NOTE : - In all cases you will have to write MDX. If you have issue with writing MDX you can use Excel and connect to the AS cube and select the dimension and measure you want and then look under the hood and get the MDX query

Here is how you can auto generate MDX query using Excel ( even other cube browsers do the same but I like excel )
http://sqlblog.com/blogs/marco_russo/archive/2007/01/18/display-the-mdx-query-of-an-excel-2007-pivottable.aspx

---

Ashwani Roy

My Blog

Please click the Mark as Answer button if a post solves your problem!

Answer 3

Hi

While I was trying to enteR A mdx COMMAND IN A SSIS  package, it shows me the following error:

Memory error: While attempting to store a string, a string was found that was larger than the page size selected. The operation cannot be completed.

please help me out.

what is my intension is, I want to extract the data from a cube and sTORE T IN eXCEL sHEET, BUT I Dont want to use a pivot table , is there any other way to do this?

Thanks

Answer 4

1. Created Linked Server and use OPENQUERY http://geekswithblogs.net/darrengosbell/archive/2006/04/26/76418.aspx

This is the method I use. It is pretty painless and just plain works.

Answer 5

Hi McLovin

When  I use this  , it is giving me the following error..

The OLE DB provider "MSOLAP.4" for linked server "linked_olap3" reported an error. Access denied.

So what could be the reason for  this?

your help is appreciated.

Thanks

Answer 6

Hi,

There is a simple solution. From Reporting Services you can export a MDX query, in the report, to Excel or a textfile. I am not an expert in Reporting Services or ASP.Net but since it is a web service you should be able to write some code to automate this.

HTH
Thomas Ivarsson

Answer 7

Hi McLovin

When  I use this  , it is giving me the following error..

The OLE DB provider "MSOLAP.4" for linked server "linked_olap3" reported an error. Access denied.

So what could be the reason for  this?

your help is appreciated.

Thanks

Did you manage to resolve it. If not then lee let us know

---

Ashwani Roy

My BI Blog

My Entity Framework Blog

Please click the Mark as Answer button if a post solves your problem!

Answer 8

Hi,

I was trying to extract data from an SSAS cube into a relational table, basically create a fact table out of the cube, where I'd have the dimension's natural keys and the measure values.

I tried using the Microsoft OLE DB Provider for Analysis Services 9.0 to connect to the cube, and using the SQL Command option I was able to query the dimension tables (select * from [$Date].[$Date]) and push the data into a relational table. However for the cube data, I was only able to query the measure group and the cube dimension values separately. Apparently the 'tables' in which the measure group data and the cube dimension data is different, and I'm not able to find a relationship between them. Also for the cube dimension data I see one table for each cube-dimension combination. Am I taking the right approach?

I also tried using Linked Server to my OLAP database, and then use MDX to get data, but the problem remained that I wasnt able to write a MDX that would just give me rows of data with all the dimension keys and measures flattened out in a table.

Is there any other approach to get data from the cubes into a relational table?

Thanks

Hi Bally,

Here is the way to do that ,

create a data flow in your ssis pacakge and in the data source take Ado.NET as your source and in the connection connect to analysis services data and in sql command write the mdx query which you want and pull the data out of cube with realtionship between your fact table and dimension tables ,

All the data comes out in NTEXT so add a data conversion task in between and convert to (DT_WSTR) and add a data viewer and check the results.

Mark as helpful if it is.

---

Sri.Tummala