topic Re: Direct discovery syntax to read a SSAS cube in QlikView

Direct discovery syntax to read a SSAS cube

Mon, 25 May 2015 14:29:13 GMT

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

Joaquin_Lazaro — Mon, 25 May 2015 14:37:26 GMT

Hi pmscorca :

Read this

SQL Server Analysis Services (SSAS) Cubes and QlikView

Connecting Qlikview to SSAS cube

Hope it helps you

Joaquín

Re: Direct discovery syntax to read a SSAS cube

Mon, 25 May 2015 15:00:12 GMT

Hi Joaquin,

I've already read these posts, but as I wrote, I need to read a SSAS cube by the Direct Discovery features. I don't want to import data from a SSAS cube (multidimensional or tabular). Is this the right place to post a such question?

Re: Direct discovery syntax to read a SSAS cube

JonnyPoole — Mon, 25 May 2015 17:30:31 GMT

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 ?

Direct Discovery Technical Addendum_11.2_SR11_and_Sense.pdf

Re: Direct discovery syntax to read a SSAS cube

Mon, 25 May 2015 19:58:26 GMT

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

JonnyPoole — Tue, 26 May 2015 02:56:01 GMT

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

Tue, 26 May 2015 07:22:17 GMT

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

MichaelRobertshaw — Wed, 24 Jun 2015 11:57:42 GMT

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.

Re: Direct discovery syntax to read a SSAS cube

Wed, 01 Jul 2015 16:58:15 GMT

Hi Michael,

I need to query the Tabular model from QlikView and not to import the cube data. I need to use QV as Excel that queries dinamically a Tabular model by a pivot table.

Thanks

Re: Direct discovery syntax to read a SSAS cube

MichaelRobertshaw — Wed, 01 Jul 2015 19:06:54 GMT

‌Can you create a View in SQLServer that connects to your cube, and allows SQL to be used as a query language by connecting Qlik to the Cube via MSSQL as a sort of Proxy? A suggestion only as I've not tried using DD through such a connection. See https://support.microsoft.com/en-us/kb/218592

Qlik products are a possible replacement for cubes. Qlik products do not generate MDX. DirectDiscovery is a SQL client ONLY and is not designed to interact with any cube datasources.

You want to use DD with a cube. I want to be taller and drive a lamborghini. None of these desires are likely to happen.

Re: Direct discovery syntax to read a SSAS cube

Wed, 01 Jul 2015 20:51:06 GMT

Hi Michael, thanks for your reply.

Only for a second, suppose it is possible to create a SQL view on a SSAS Tabular model. Only for a second.

Are you sure that the behaviour of QV is the same as Excel? When a filter/slicer of a pivot table is selected, a query against the SSAS Tabular model is triggered; in other terms, it must be possible to query dinamically the Tabular model connected to QV solution.

Are you sure that I don't need to import any tables and to create any model/relationships inside QV?

Many thanks