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