Calculated Measures är en kraftfull funktion i OLAP-kuber som används för att utöka kubfunktionalitet genom att kombinera vanliga measures och dimensioner med varandra. En calculated measure definieras i själva kuben medans en query scoped member å andra sidan definieras i en query. Ibland kan en query scoped member vara enda utvägen beroende på användarrättigheter eller p.g.a. prestandaskäl. Läs mer
Etikettarkiv: SSRS
Calculated measures and query scoped members in SSRS
Calculated Measures are a powerful feature in OLAP cubes. They are used to extend cube functionality trough combining actual measures and dimensions with each other. A calculated measure is defined in the cube whereas a query scoped member is defined in an MDX-query. Sometimes a query scoped member might be the only option due to user rights or performance reasons.
Let’s say we want to define a query-scoped member that allows us to subtract the previous period’s sales from the current period to find out whether sales increased or decreased. First open SQL Server Management Studio then connect to SSAS, open the Adventure Works database and create the following query:
with member [Measures].[Sales Growth] as
([Measures].[Reseller Sales Amount]) -
(ParallelPeriod([Date].[Calendar].[Month],1,[Date].[Calendar].CurrentMember),[Measures].[Reseller Sales Amount])
select {[Measures].[Reseller Sales Amount],[Measures].[Sales Growth]} on columns,
non empty [Date].[Calendar].[Month].Members on rows
from [Adventure Works]
To define Sales Growth we make use of the ParallelPeriod function and the CurrentMember property. Let’s dissect the ParallelPeriod function call:
The first parameter is the Month level of the [Date].[Calendar] hierarchy. It defines the period:
(ParallelPeriod([Date].[Calendar].[Month],1,[Date].[Calendar].CurrentMember),[Measures].[Reseller Sales Amount])
The second parameter defines the period lag. By passing 1 as an argument we get the previous month:
(ParallelPeriod([Date].[Calendar].[Month],1,[Date].[Calendar].CurrentMember),[Measures].[Reseller Sales Amount])
The third argument defines the hierarchy which we will use to slice the value. The CurrentMember property tells the execution engine to look for the current member of the other axis:
(ParallelPeriod([Date].[Calendar].[Month],1,[Date].[Calendar].CurrentMember),[Measures].[Reseller Sales Amount])
Note that we can use both the [Date]. [Calendar].[Month] and [Date]. [Calendar].[Date] levels of the [Date].[Calendar] hierarchy to slice the data along the rows-axis. I.e.: [Date].[Calendar].[Date].Members or [Date].[Calendar].[Month].Members
The above was all done in SQL Server Management Studio, to accomplish the same in an SSRS report:
- Fire up BIDS, and start a Report Wizard project that we connect to the SSAS Adventure Works database. Click next.
- Open the query designer, drag the [Date].[Calendar].[Month] dimension and [Measures].[Reseller Sales Amount]) to the results pane (the big one).
- In the lower left pane of the query designer, right click and select “Add New Calculated Member”.
- In the Calculated Member Builder window paste the following part of the query we designed in Management Studio:([Measures].[Reseller Sales Amount]) -
(ParallelPeriod([Date].[Calendar].[Month],1,[Date].[Calendar].CurrentMember),[Measures].[Reseller Sales Amount]) - Type [Sales Growth] in the uppermost textbox.
- Click ok and then simply drag the calculated measure from the lower left pane to the center pane. You should now see both [Measures].[Reseller Sales Amount] and[Measures].[Sales Growth] data.
You can now go on and create an SSRS report using the query scoped member.
Välkommen till BI-bloggen!
Vi som skriver här jobbar på Kentor Business Solutions och skriver om sådant som vi arbetar med dagligen här – nämligen Business Intelligence. De som skriver här brinner för Business Intelligence och vi hoppas att ni hittar matnyttig information och inspiration i våra inlägg!
-
Vilka är vi?
-
