I am new to SSAS. I have the following scenario and not sure how to address it
I have a Fact Table as below and fed to my SSAS Cube, i need to create a few complex Calculated metric based on the available metric from my Fact Table (eg of simple calculated Metric is Price = Revenue/Sales Volume.)
When it comes to Cube, users want to see metric as rows (as we have 80 over metrics) and not as calculated columns, how can i achieve that. My final output should be similar to below fact based on the slicers the users pick up. Since the Price metric is non aggreagatable an cannot be rolled up i have issues.
Appreciate if anyone could throw light on this or provide any links to any samples
Metric, Company, Commodity all has Hierarchy to it
Company Commodity Product Counterparty Metric MeasureValue A C1 P1 SalesVolume 1000 A C1 P1 CP1 Revenue 5000 A C1 P1 CP2 Revenue 10000
Thanks for your question.
If i understand you correctly, "Price" metric should be aggragatable dimension. To get the desired results,you can just do a crossjoin with all these dimension.
See my sample MDX query in Adventure Works below:
select [Measures].[Order Count] on 0, [Product].[Category].&* [Product].[Subcategory].[Subcategory]* [Product].[Size].[Size]* [Product].[Class].[Class]* [Product].[Weight].[Weight]* [Product].[Size Range].[Size Range] on 1 from [Adventure Works]
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com
Thanks for your quick response.
Not sure i have convened the requirement correctly. My user can load the data into DWH on any level based on specific metric, ie they can load data at product level or counterparty level or combination of this, so when it comes to cubes, it has to rollup the data according to measure. This kind of functionality - grouping Measure and creating calculated measure based on existing measure is available in Cognos,power play cubes, i am looking for similar functionality.
i.e. data will be loaded into cubes by individual measures as dimensions and i need to created calculated measures based on the fixed measures, but slicers(other dimensions than measure like product, company, counterparty) will be chosen by the users while consuming the cubes and the calculated measure should appear as one more record bases on the slicers
My Current Cube will give me data like
Sales - Export - Product1 - Couterparty1 - 5000
Sales - Domestic - Product2 - Couterparty1 - 2000
Revenue - Export - Product1- <Null for counterparty> - 10000
I want to create a Price metric = Revenue/Sales and when the qry the cube the result should be
Price - Export - Product1 - Counterparty1 - Null ( as no revenue is there) (when counterparty dim is selected)
Price - Export - Product1- 2 (10000/5000) (when counterparty is not Selected)
First of all, in MDX, the columns and rows axis do not play any special row like they do in relational databases. Whatever you can put on the rows, you can as well put on the columns and vice versa.
The basic structure of an MDX statement (slightly simplified) is
SELECT <set1> ON COLUMNS, <set2> ON ROWS FROM <cube>
And by just switching these sets, you can switch the result structure:
SELECT <set2> ON COLUMNS, <set1> ON ROWS FROM <cube>
There is no requirement that measures must be on columns (except that some client tools like SQL Server Reporting Services require that, but that is a restriction of that tool, not of the cube or MDX).
Having said that, now let's look at the price calculation. I would implement that as a calculated measure in the calculation script of the cube, somehow similar to
CREATE MEMBER CURRENTCUBE.[Measures].[Price] AS [Measures].[Revenue] / [Measures].[Sales Volume];
CREATE MEMBER CURRENTCUBE.[Measures].[Price] AS IIf([Measures].[Sales Volume] = 0, NULL, [Measures].[Revenue] / [Measures].[Sales Volume]);
The first expression is technically completely valid, but it would display +INF or -INF (plus/minus infinity) should the Sales Volume be 0 or null, a result which is to some extent technically correct, but not what users expect to see normally, and hence the IIf expression prevents that.Frank
- Proposed as answer by FrankPl Tuesday, March 21, 2017 7:31 PM
I did mange to create the calculated Measures for Revenue, Sales , Price . We have few complexities over here. Eventually we will be having 80 over measures, some are Aggregated measures ( Eg: Inventory: Inventory - Port Inventory- Finished Goods and so i have a hierarchy in measures). Users feels that its hard for them to locate each measure when they pivot it using excel or power bi also we have 3 scenarios for measure (Actual, Budget , outlook) and their kpi which they want to see side by side so they prefer to view in row format
ie my Pivot from cube should be like
Rows - Required Dimensions , Measure Hierachy (Sales (Export, Domestic, Retail), Revenue (Export, Domestic, Retail), Price (Export, Domestic, Retail), Inventory(Port, Goods)... Cost etc)
Columns - Period hierarchy and Scenarios
Values - One single Field that is Measure value
Under normal scenario i can get this, my only issue is when there is calculated measure. I tried even rollup of data and filter in datasource level, but complexities few are Aggregated as Average and few are Sum , few are calculation based on few other metrics
I would suggest to put Export, Domestic, Retail into a separate attribute. This would then reduce the number of measures to one third, but would require the users to add that attribute to the rows (depending on the tool you use).
Things that are calculated after aggregation are normally best implemented as calculated measures in the calculation script of the cube. Things that need to be calculated before aggregations are normally best put into the data source view.
I roughly understand that the SSAS doesn't allow to group the measure and dimension on rows which is what i was looking for using excel, but we managed to achieve the functionality using Tableau, probably we cannot expose the cubes directly to user, we need to look for reporting tool as well.
Measures can be put on rows in MDX, but not some measures on rows and some on columns in the same query. And calculated measures are just measures with regard to this rule.
However, calculated members need not be measures. In fact, you can build calculated members on every attribute or hierarchy.
There are some restrictions that some client tools have, like Reporting Services requiring that measures and only measures are on the columns.