I'm using SQL server 2016.
I am new to MDX. Is it possible to convert a MDX statement to SQL? Is there a converter?
The below is my MDX - how do I convert this to SQL?:
WITH MEMBER _x AS
IIF( [Measures].[Inherit from Material Group] <> 0,
[Measures].[Splitting Percent at Material Group m],
[Measures].[Splitting Percent at SKU m] )
SELECT _X ON 0, NON EMPTY (
[Product].[Sales Org SKU].[Sales Org SKU],
[MARKET].[Sales Org Ship to Party].[Sales Org Ship to Party]
) ON 1 FROM TEST
You need to understand that you can have an equivilant of MDX query in SQL but , the SQL version will run on the dimension Model/ Star schema that feeds your SSAS solution.
In the below translation I have supposed the following about your dimensional model/ star schema
Based on this take a look at the following query
select [Product].[Sales Org SKU],
[MARKET].[Sales Org Ship to Party],
case when sum([Inherit from Material Group])<>0
then sum([Splitting Percent at Material Group m])
else sum([Splitting Percent at SKU m]) end as _x
from
yourfactTable f
inner join
[Product] p
on f.productkey=p.productkey
inner join
[MARKET] m
on f.marketkey=m.marketkey
group by
[Product].[Sales Org SKU],[MARKET].[Sales Org Ship to Party]