November 17, 2011 at 11:36 am
Currently I am working on a project where I have to calculate a measure that is based off two facts. The client sells packages which are a group of services. I have to calculate the consumption of the package within a date range, based on when the package was sold. Using TSQL, I can calculate the value using two dates, but I cannot figure out how to do this using SSAS/MDX.
An example of my TSQL query is below:
SELECT sum(t.Quantity*t.Price) FROM dbo.PackageDetails pd
INNER JOIN dbo.Transactions t ON t.ipackageid = pd.iid AND t.Store = pd.Store
INNER JOIN dbo.PackageSales p ON pd.iparentpkgid = p.iid AND pd.Store = p.Store
WHERE p.TransactionDateKey >= 20111001 AND p.TransactionDateKey <= 20111031
AND t.TransactionDateKey >= 20111001 AND t.TransactionDateKey <= 20111031
ORDER BY p.TransactionDateKey
The PackageSales table provides information regarding the date the package was sold.
The Transaction table provides information regarding the date a service was performed.
The PackageDetails table links the PackageSales table to the Transaction table when the service performed was part of a package.
In the attached zip file, PKGConsumption.zip, are the following files:
Create_Tables.sql which will create the tables.
TransactionsData.sql will insert transaction data into the transaction table.
PackageDetailsData.sql will insert package detail data into the packagedetails table.
PackageSalesData.sql will insert package sales data into the packagesales table.
There is an SSAS project, PKGConsumption, that I was using for development to obtain the calculation.
When the example query above is executed it returns a value of 872.45000000, which is the correct value I need to obtain.
I have tried using MDX to obtain the calculation, using the following MDX query:
select filter({[Measures].[Series Use]}, [Dates].[Month Name].&[2011]&[10]) on columns,
[Locations].[Store Name].Members on rows
from [EWC DW]
where [Dates].[Month Name].&[2011]&[10];
the same value as executing the below query against the DW
SELECT sum(t.Quantity*t.Price) FROM dbo.PackageDetails pd
INNER JOIN dbo.Transactions t ON t.ipackageid = pd.iid AND t.Store = pd.Store
INNER JOIN dbo.PackageSales p ON pd.iparentpkgid = p.iid AND pd.Store = p.Store
WHERE t.TransactionDateKey >= 20111001 AND t.TransactionDateKey <= 20111031
The value obtained using the above queries produces a value of 4931.04000000.
November 18, 2011 at 12:01 am
The difference between the SQL and the MDX is that in your first query, you join also against PackageSales. However, you don't need this table to get your result. The only thing it does is filtering out rows.
Since you do not include this link in the cube (or in the second SQL query), this filtering does not take place.
You should rethink your design a bit. The Package Sales and Transactions measure group have the same granularity, so maybe they can be merged into one single measure group. Furthermore, the link between the fact tables Transactions and Package Sales and the PackageDetails dimension is with a composite key. Analysis Services has some troubles with it, as the dimension is not used in the Dimension Usage tab. (I couldn't get the composite key relationship to work there). Consider replacing this with a single key relationship, using surrogate keys.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply