Load data from Analysis Services using MDX Federator or OLEDB Connection

Samuele Marchetto

Hi everyone,

 

I have to load data from a cube in Microsoft Analysis Services. I tried with an ODBC Datareader, connecting via OLEDB to analysis services session and typing a MDX query in manual box of the datareader, but every time an error occured (usaually a syntax error or other type). Has anyone done that? Is there someone that had tried MDX Federator and can give some documentation about it?

 

Thanks!

Robert Barkaway

It sounds like you're trying to pull in data from SSAS into an existing Board database, if so then MDX Federator won't be the solution, that allows for building a Board database over SSAS but doesn't allow you to combine SSAS data with an existing Board database. If you just want to pull in a new dataset from SSAS you have a couple of options:

 

Option 1 would be to create an SSRS report to extract the dataset you wish to load, you can then extract that report to CSV (either manually or as a scheduled process in SQL Agent) and load it into Board as with any other flatfile. 

 

Option 2 would take a little more setting up but can be very useful, it's possible to create a linked server which points at an SSAS instance, you can then create a view in a SQL database to sit over the top of that linked server and return the required dataset as with any other view. You will need to do some data-cleansing as querying SSAS in this way leaves in square brackets around entity values if I remember correctly, but once you have your dataset returning as you want it you then have a simple job to set up a normal SQL datareader in Board to import the data. 

Björn Reuber

Hi

 

there is an third option:

Configure an MS OLE DB Provider for Analysis Services (due to the fact that in most cases MSAS only allow SSO user you need to change the windows account running the enigne). Then configure a  new connection in BOARD (RDBMS type  OLEDB for OLAP), using your OleDB Connection.

Afterwards board should create an MDX statement instead of an SQL statement.

 

regards

Björn

Samuele Marchetto

Hi and thank you for your answer. I did exactly what you suggest, I can connect to SSAS and view the list of dimensions and measures, I can map the field with usual datareader drag&drop wizard, but every time I browse the data with the MDX statement, a syntax error occure. I tried to write manually the MDX statement, but i didn't find a way to do it work.

Trying to browse the data of a dimension, I type a statement like that:

 

SELECT * FROM [Dimension].[Dimension Name]

 

and it work. But I didn't find a working statement to load data from a cube. I tried somethig like:

 

SELECT { [Measure].[Sells] } ON COLUMN, { [Dimension].[Dimension Name] } ON ROW

FROM [Mycubename]

 

Do you have an example of a correct syntax of the statement?

 

Thank you

Björn Reuber

Hello,

 

can you please share a screenshot of the query and the syntax error? Cause I also get it some time (but right now I doesnt have an AS Server to test)

 

regards

Björn

Samuele Marchetto

Unfortunately I don't have an easy access to SSAS and I don't have a test server too. I'll try to connect again asap and let you know. Thanks

Samuele Marchetto

I have the screenshot of the error. 

The first (Autoconnect) is the statement created by Board and it doesn't work.

 

 

The second image show the error after few changes in manual mode (is the error showed every time the syntax seems to be correct)

 

 

Error in English is "Object reference not set to an instance of an object".

 

Thanks.

Björn Reuber

Hi,

 

it seems that the query doesnt use the Field "Tempo].Giorno and so for board one column is missing.

Can you please try an cube with 3 Entities instead or try following query:

Select [Tempo].[Giorno].Members On 0, [Articolo].[Articolo].Members On 1, {[Measures].[ValoreNetto]} On 2 FROM Vendite

Regards

Björn

Previous Member

I usually make a linked server in MS-SQL, write a stored procedure which to extract the data from the SSAS Cube and store it into a SQL table. Once in SQL it is easy to import it into Board.

Samuele Marchetto

Tried your statement, but doesn't work. 

The follow statement works (obtain sales value by year):

 

SELECT  

    { [Measures].[ValoreNetto],   

        [Measures].[Quantita] } ON COLUMNS,  

    { [Anno].[Anno].[Anno] } ON ROWS  

FROM [Vendite]

 

but when I use an Article ([Articolo[.[Articolo]) or Customer dimension like that:

 

SELECT  

    { [Measures].[ValoreNetto],   

        [Measures].[Quantita] } ON COLUMNS,  

    { [Articolo].[Articolo] } ON ROWS  

FROM [Vendite]

 

it doesn't work (always "Object reference not set to an instance of an object error" is show). Could it be a wrong dimension? I try [Articolo].[Articolo].[Articolo] on row and obtain the same error.

 

 

Unknown

I Agree with Robert: import everything in SQL srv and manage data from there.

There are many things not fully working around MDX topics.