topic Re: Reg SSAS Cube in QlikView

Reg SSAS Cube

Anonymous — Mon, 28 Jan 2013 15:14:41 GMT

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

Re: Reg SSAS Cube

Gysbert_Wassenaar — Mon, 28 Jan 2013 15:15:52 GMT

See here. If you need more information ask brunomarques.

Re: Reg SSAS Cube

Anonymous — Mon, 28 Jan 2013 15:17:48 GMT

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

Re: Reg SSAS Cube

christian77 — Mon, 28 Jan 2013 16:46:13 GMT

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.

Re: Reg SSAS Cube

Mon, 28 Jan 2013 18:28:06 GMT

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.

Re: Reg SSAS Cube

brunomarques — Mon, 28 Jan 2013 22:58:41 GMT

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

Re: Reg SSAS Cube

Anonymous — Wed, 30 Jan 2013 05:39:56 GMT

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

Re: Reg SSAS Cube

Anonymous — Wed, 30 Jan 2013 13:14:07 GMT

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})

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

Re: Reg SSAS Cube

Wed, 06 Feb 2013 14:33:42 GMT

Hi Bruno,

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

Re: Reg SSAS Cube

Wed, 06 Feb 2013 14:34:12 GMT

Hi Bruno,

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

Re: Reg SSAS Cube

brunomarques — Wed, 06 Feb 2013 14:47:10 GMT

https://dl.dropbox.com/u/50769050/Qlikview%20MDX%20Query%20Tool%20Manual.pdf

Re: Reg SSAS Cube

Anonymous — Wed, 06 Feb 2013 16:34:38 GMT

Bruno,

Thnaks a ton for this MDX generator. This is awesome tool who dosen't know MDX query.

I have a question here will this tool support the parent child hierarchy.

The problem i face in my project is we have parent child hierarchy and this hierarchy is related with the unique key numbers. So the MDX generator is not able to search the key and give me the query.

Can you help me here?

Rgds,

Nandha

Re: Reg SSAS Cube

Thu, 07 Feb 2013 14:05:00 GMT

Did Uou check O-delta™?

"O-delta™ by DataForce Ltd. is the most comprehensive Solution today for companies to dramatically cut down Time & Money of Connection, Migration & Integration of OLAP (mdx.) data cubes to any QlikView’s models, from months/ weeks to days/ hours !!! Http://www.dataforce-solutions.com The Benefits are: • Minimum need of skills/ knowledge of the current OLAP cubes’ structure • Leverages OLAP cubes development investments • Intuitive, integrated & consolidated view of all BI information as a front end • Mobile availability of old and new BI applications"

http://www.youtube.com/watch?feature=player_embedded&v=dlb6nf0w6TQ