Reg SSAS Cube

[Anonymous]

Hi All,

We created a cube in SSAS (MS BI). How can i connect this cube with QV? Pls explain me the process fully.

Thnx & Rgds,

Nandha

[Gysbert_Wassenaar]

See here. If you need more information ask brunomarques.

[Anonymous]

Wasswnaar,

Are you refering to any link? If so I don't see any link in this. Can you pls sed me the link?

Rgds,

Nandha

[christian77]

1st Connector, the usual ones.

2nd openrowset is a config to do in your analysis services SQL. It is a must.

3rd Query like it follows.

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Data Source=blah.blah.blah.blah;Use Procedure for Prepare=1;
Auto Translate=True;Packet Size=4096;Workstation ID=blahblah;Use Encryption for Data=False;
Tag with column collation when possible=False] (XPassword is XVGHHbFNULYYWZVMSTdKGZNP);

Pacientes:LOAD
     "[Measures].[#Pacientes]"                                            as "Paciente",
     "[Pacientes].[Estado Civil].[Estado Civil].[MEMBER_CAPTION]"         as "Estado Civil";

SQL SELECT * FROMopenrowset('MSOLAP','DATA SOURCE=localhost;Initial Catalog=blahblah',
'SELECT
           { [Measures].[# Pacientes]} ON COLUMNS,
           { [Pacientes].[Estado Civil].Members} ON ROWS
FROM [blahblah222]')

good luck. it is not that hard. read about MDX step by step to start doing queries. this is a very simple one.

[#5]

I will explain bellow the way to do it (because you can do it), but I have to ask you first if you really need to extract data from a cube. This question is because you usually (99% of times) have the same data in the cube and in the DW behind the cube, and if you use cubes to extract data into QlikView, you are using an unnecessary piece that will complicate future maintenance, that is very rigid and that usually hasn't all the detailed data.

People want to use their cubes because they have spent so many weeks (or months) and effort building them, that they think that work have to count for other projects. But usually you can have a QlikView application from the same DW with the same information (or more) just in some hours or days.

• There are two main methods to get data from SSAS (SQL Server Analysis Services) cubes:
  • OpenRowset: You really connect to SQL Server and use the OpenRowset sentence to make SQL Server launch the MDX query to SSAS, and then getting back the results to QlikView in a Rowset object. This method allows full MDX syntax, with calculated members.  The MDX query has to be written in the QlikView SQL sentence manually.
  • SSAS OLEDB connection: It is a MDX query directly launched to SSAS, but it allows only plain MDX, without calculated members. The MDX query has to be written in the QlikView SQL sentence manually, but it is easier than with OpenRowset.
• QlikView has not any MDX wizard, you have to write the MDX sentence in the SQL part of the connection.

But be careful when working with SSAS cubes:

• SSAS extractions are very slow, SSAS is an OLAP engine designed to aggregate many data and accumulate into some values. Not to return large datasets, like the relational database engine.
• If you query many “theoretical” cells from a SSAS cube, it is extremely slow and you can get the famous error “The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples.”. Or get a “Out of memory” error. This can happen with large cubes with large dimensions, where the possible theoretical combinations (the product of all members) are larger.
• Usually the best approach is using the database (or DW) for transaction detail extractions, and the cube for complex calculations or special calculations on aggregated data (usually can be replicated into QlikView, but this way there is only one place to maintain this calculations).

But you can also think about this other points:

• Usual MDX query is not complicated, it is similar to SQL, mainly for simple queries.
• You can build dynamically any MDX query before querying to SSAS. i.e. I have done a SSAS extraction looping some dimensions to extract data for every dimension value, like doing slicing. Depending the combinations of MDX you need, you could use parameters in the QlikView script.

JG

[brunomarques]

Added a quick manual to the download page of the tool. Hope it helps to clarify some questions on how to use it.

[Anonymous]

Hi Marques,

I have mappd the cube server with my local Server Management Studio. But i am not clear how i can link that to my MDX query generator.

When i type the servfer path in server information and click Connect button i am getting and error "InvalidArgument=Value of '0' is not valid for 'index' Parameter Name: index"

Can you pls help me here?

Rgds,

Nandha

[Anonymous]

Bruno,

Its working fine now. I faced that problem as the services are not running properly. Now its fixed and working fine and i got the query generated as below

"SELECT

{[Measures].[USD],

[Measures].[USD - Fct Budget],

[Measures].[USD - Fct Forecast]

}

DIMENSION

PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS , NON EMPTY

CrossJoin

(

{[Dim Account Main3].[Account Key].

Children},

{[Dim Activity Main].[Activity Key].

Children},

{[DIM COST CENTRE MAIN].[Cost Centre Key].

Children})

ON

ROWS

FROM

[IB Test] ;"

Pls let me know where do i want to use this query and how will i get the fields into QV?

Thnx & Rgds,

Nandha

[#9]

Hi Bruno,

Can you please send me the link of this manual from the download page. I am not able to locate it.

[#10]

Hi Bruno,

Can you please send me the link of this manual from the download page. I am not able to locate it.