10 Replies Latest reply: Jul 1, 2015 4:51 PM by Pasquale Massimo Scorca RSS

    Direct discovery syntax to read a SSAS cube

      Hi,

      I need to know which is the correct Direct discovery syntax in order to read a Microsoft SSAS cube, in multidimensional or tabular mode. In particular, to read a SSAS Tabular model, I've accomplished many attempts but unsuccessfully.

      Any helps to me, please? Many thanks

        • Re: Direct discovery syntax to read a SSAS cube
          Jonathan Poole

          Hi there

           

          Direct Discovery is SQL compliant data sources only (not MDX for example).  Below is a good reference. I copy/pasted the relevant text.

           

          Do you have a driver / technique to expose the cube as a SQL source to Qlik ?

           

          Capture.PNG

           

          Direct Discovery Technical Addendum_11.2_SR11_and_Sense.pdf

            • Re: Direct discovery syntax to read a SSAS cube

              Hi Jonathan,

              I'm using the Microsoft OLE DB Provider for Analysis Service 11.0 to connect QV to the SSAS Tabular model: this data provider seems compatible with the Direct Discovery feature. I can query this model using MDX or DAX Language, but when I use the DIRECT QUERY statement I've a script error. I'm interesting to know the right syntax to apply to Direct statement: I've accomplish several tests and now I'd like to find a solution, if possible. Do you know how to use the Direct Discovery feature? Many thanks

                • Re: Direct discovery syntax to read a SSAS cube
                  Jonathan Poole

                  That driver will work to send mdx queries to MSAS to load in memory tables.

                   

                  But a direct discovery query must be written in SQL, which is not the same as mdx precluding the ability to do direct discovery.

                   

                  You would be able to combine in memory tables loaded via mdx from MSAS with a direct query table or view from a Sql source such as Sql server . As long as the direct discovery tables are sourced via sql you can combine with anything else sourced from anywhere else.

                    • Re: Direct discovery syntax to read a SSAS cube

                      Hi Jonathan,

                      the QV documentation talks about OLEDB data sources compatibility, but it seems that the Direct Discovery doesn't function with the Microsoft OLE DB Provider for Analysis Services: the documentation would be more accurate and this incompatibility isn't a good thing.

                      My goal is to retrieve the SSAS measures without recreating them in QV saving further development effort; in this way, I could use QV with a client tool to navigate among dimensions and measures. The QV philosophy remains too closed itself.

                      However, using the above OLEDB provider I can use this syntax in the traditional manner: SQL SELECT CODE FROM [Model].[$AGENCY];

                      but I've a script error when I try to use the DIRECT QUERY statement.

                      Many thanks

                        • Re: Direct discovery syntax to read a SSAS cube
                          Michael Robertshaw

                          Direct Discovery causes Qlik visualisation objects to generate SQL statements to the underlying datasource. MDX is similar to SQL but is not the same.

                           

                          As an example, If you have a simple Sales application with Customer, Product and Calendar dimensions and you select 2041 and 2015 in the Calendar, and Iberia in the Customer Region, then the Green/White/Grey of the Products listbox has to be determined by querying the fact table for what Product Keys were sold in that region and period. A stacked Bar Chart of Product Sales over Time is displayed from the result of a SQL statement such as:

                           

                          SELECT Calendar, Product, // Dimensions

                               sum(Sales) // aggregate Expression
                          FROM SalesFactTable
                          GROUP BY Calendar, Product // all Dimensons

                          WHERE CalendarYear IN (2014, 2015) AND CustomerRegion IN ('Iberia');

                           

                          and the IN clauses could be a much larger list of associated keys if these fields are not actually on the Fact table. MDX uses a completely different syntax for applying filters, or selecting aggregation levels and Direct Discovery does not know to generate that syntax for a non-SQL datasource. There are tunable properties in the script for affecting the way that portions of the generated SQL are constructed.

                           

                          Qlik products supply a much more flexible form of OnLine Analytical Processing than cubes. Logical Cubes (Star Schemas in databases) are now much more scalable than Physical Cubes that require legacy interfaces such as MDX and XML/A and bound the user to hierarchical data navigation.

                           

                          You should be able to use DAX to query the Dimensions and Fact table from your cubes and materialise these into a Qlik document without having to remodel the data, but beware of non-additive measures in cube datasources.