Hey All,

Is it possible to execute a SQL passthrough MDX query to a Tabular model? I am using WF7703 and using a MFD built off the model works, but I'd like to use custom MDX for a particular situation.

Is this possible? I assume not based on this thread, but just double checking...

Related focal point post


Thanks!

This message has been edited. Last edited by: <Emily McAllister>,

The Adapter doc doesn't say anything about passthru for SQL Server Analysis Services.

You can using OPENQUERY if this is SQL Server and SSAS.

Very old example:

ENGINE SQLMSS SET DEFAULT_CONNECTION IRDB
SQL SQLMSS
SELECT
CAST("[Account].[Level 03].[MEMBER_CAPTION]" AS varchar(50)) as ACCOUNT,
CAST("[Measures].[Amount]" AS float) as AMOUNT,
CAST("[Measures].[AmountSMLY]" AS float) as AMOUNTSMLY,
CAST("[Measures].[FiscalAmount]" AS float) as FISCALBALANCE,
CAST("[Measures].[FiscalAmountSMLY]" AS float) as FISCALBALANCESMLY,
CAST("[Measures].[COGS]" AS float) as COGS,
CAST("[Measures].[FiscalCOGS]" AS float) as FISCALCOGS

FROM openquery(OLAP_FINANCE,
'SELECT { [Measures].[Amount], [Measures].[AmountSMLY],
[Measures].[FiscalAmount], [Measures].[FiscalAmountSMLY], [Measures].[COGS], [Measures].[FiscalCOGS] } ON COLUMNS ,
{ [Account].[Profit and Loss].Children } ON ROWS
FROM [Finance]
WHERE ( [Dataset].[Actual],  [Time].[&LDATE1.EVAL] , [Site].&|[&SITE.EVAL]) 
');

TABLE ON TABLE HOLD AS MYOUT
END