Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

10 Replies
JoaquinLazaro
Partner - Specialist II
Partner - Specialist II

Not applicable
Author

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?

JonnyPoole
Employee
Employee

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

Not applicable
Author

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

JonnyPoole
Employee
Employee

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.

Not applicable
Author

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

MichaelRobertshaw
Former Employee
Former Employee

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.

Not applicable
Author

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

MichaelRobertshaw
Former Employee
Former Employee

‌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.