August 7, 2014 at 10:43 pm
Dear all
I'm new to SSAS and MDX and need your help on my current project. What I need to do is to insert the MDX query result to sql table.
Here's the scenario:
Let's suppose the data is from [AdventureWorksCube] the Measure is [Net sales], I need to pull out the net sales data for certain prodcuts and use it to calculate the rebate amount by multiplying cetain rebate percentage.
Here's a sample MDX query under Management studio:
select {[Measures].[Net Sales]} on 0,
{[Account].[Bill to account]} on 1
from [AdventureWorksCube]
where ([Product].[productCategory].[Product Code], [Date].[Month Year].[Aug-14], [Scenario].[Scenario1], [Factory].[FactoryID])
The output of the query will be like what's in below:
BillTo Net sales
345353 $79
536261 $829
489202 $9303
573982 $8933
I need to insert the query result along with all the members in the where clause to a SQL table in below.
The fact is that one BillTo might contain sales amount for multiple product code
BillTo Netsales ProductCode MonthYear Scenario FactoryID
345353 $79 1 aug-14 1 01
536261 $523 2 aug-14 1 02
536261 $306 3 aug-14 1 01
489202 $5303 4 aug-14 1 02
489202 $2000 5 aug-14 1 01
489202 $2000 6 aug-14 1 02
573982 $2220 7 aug-14 1 01
573982 $4000 8 aug-14 1 01
573982 $2713 9 aug-14 1 02
Question #1: how to populate the above table to include the query result and members in where clause
Question #2: how to use parameters to make the query dynamic? The above table needs to be loaded monthly with every month's transaction with different productcode
and different
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply