12 Replies Latest reply: Feb 7, 2013 9:05 AM by Shauli Bergman RSS

    Reg SSAS Cube

    Nandha Kumar

      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,


        • Re: Reg SSAS Cube
          Gysbert Wassenaar

          See here. If you need more information ask brunomarques.

            • Re: Reg SSAS Cube
              Nandha Kumar



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




                • Re: Reg SSAS Cube
                  Christian Conejero

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

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



                  SQL SELECT * FROMopenrowset('MSOLAP','DATA SOURCE=localhost;Initial Catalog=blahblah',
                             { [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

                  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
                      Nandha Kumar

                      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?





                        • Re: Reg SSAS Cube
                          Nandha Kumar



                          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







                          [Measures].[USD - Fct Budget],

                          [Measures].[USD - Fct Forecast]






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


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


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





                          [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,


                        • Re: Reg SSAS Cube

                          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

                            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
                          Juan Gerardo Cabeza Luque

                          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



                            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"