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 post 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy