BISM – Period To Date Calcs

DAX has three periods to date functions which easily allow the creation of common the common measures (Month To Date, Quarter To Date and Year To Day). This post looks at period to date calculations and member sorting in the BISM tabular model.

Sorting

By default the tabular model sorts members by name. For example, clicking on the month field shows the alphabetical listing of months. Although these values are shown in the project model they are displayed in the same order when the project is deployed.The order of these members can be changed and sorted by an additional column by using the sort by column function (menu path: Column > Sort > Sort By Column).When this is done, we simply specify the sort column in the popup (below).

After sorting, members appear as expected in both the project design and deployed project.

Period to Date Calculations

Period to date can be easily created using the DAX functions TOTALYTD, TOTALMTD, TOTALQTD. Note that these functions are not dependent on sort order but on a related date field.

For example, a date field in the related DimTime table.Note that there is a primary relationship between FactResellerSales and DimTime and the inclusion of the TimeDate (which is a date data type) column in DimTime.

The general format of the function is TOTALPPP ( scalar_function , time_column) where the time_column is a primary related date field. To show YTD Values for SalesAmount we use the formula:

Sales YTD:=TOTALYTD(sum(FactResellerSales[SalesAmount]), DimTime[TimeDate])

Also note that measures (as they would be thought of in SSAS) must be added to the ‘Measure Grid’. If they are not, measures are not seen in the cube. The table FactResellerSales has columns hidden from client tools (so that no dimension members are created) and contains the measures [Sales] and [Sales YTD] (below).

Browsing the cube shows data (sorted) with YTD values as expected. Unlike SSAS, the tabular model will show the [YTD] measure defaulting to the last year when the (related) time table is not used.


MISC

The technet reference for these functions can be found at YTD , QTD , MTD .

Advertisements

BISM – Dimension Hierarchies

As a consultant who specialises in SSAS, I didn’t think that the semantic model could stand up to the UDM dimensional structure. After all, SSAS has hierarchies, sorting and the ability to assign attributes, values and names. None of these options were present in the release of power pivot and I expected the semantic model to be enterprise power-pivot. How wrong I was!

Hierarchies

Hierarchies are very easy to implement in denali. In this example, a dimension (if I can use that word) is created for adventure works product groups. As a refresher, the ProductSubCategories are related to ProductCategories which allow the RELATED function to return the product Category name to be shown in the ProductSubCategory table.

Once we have done this, the DimProductSubCategory can be hidden as it is no longer needed by right clicking on the table and selecting ‘Hide from Client Tools’. Once this is done, the DimProductCategory table is grey out

A hierarchy can then be created by selecting the table we want a hierarchy for and selecting ‘Create Hierarchy’ from the popup menu (right click on the DimProductSubcategory table).

If no fields were selected when the hierarchy was created, the hierarchy is created with no levels. Levels can be added now by dragging attributes into the hierarchy.

If fields were selected when the hierarchy was created, they will be included in the hierarchy and can be rearranged by dragging then to a different level.

To clean up the dimension we can also hide fields by highlighting the fields (control + to highlight multiple) and selecting ‘Hide from Client Tools’
Finally, the names for all objects (tables, attributes and hierarchies) can be changes by using the Rename option from the popup. (Right clik àrename).

The the dimension (again, should I be using that word ?) is finished, it looks as excpected in both the designer (below) and browsing the model in SSMS (right).

Perhaps, I’ve been using SSAS for too long but I find this functionality is very intuitive and easy to use. What can I say but well done!

DMX 102 – SSAS Time Series with Multiple Attributes

This post looks at extending the time series prediction so that multiple values can be predicted. In the last post (DMX 101) only a single value was predicted and while this can be useful, the approach has obvious draw backs because there is often an expectation that different classes have different patterns. Consider seasonal fashion. We expect that seasonal products will have a distinct life-cycle and to group products (from multiple seasons) would distort a specific seasons sales.

In this post, we predict two sequences within the same model. For brevity, both series follow the function y=mx+b;

    y1 = ½ X + 20 (and)

y2 = 2x

There are two ways of dealing with multiple predictors. Firstly, we can treat each as a function value of the same sequence. In this method, we identify a value for y1 and a corresponding value for y2. In this approach, the series key is simply the x (time) value. The second method considers that only one value is being predicted (y) which is a value derived from the series key and an additional identifier which specifies the function (as either y1 or y2).

Method 1 – Multiple Y values

In this situation, our predicting data is represented as functions of (the time) series key (see right). This is represented by the SQL below.
with xs(x) as
( select convert(money,1) union  all
select x+1 from xs where x<20
)
select x, x/2+20 as y1, 2*x as y2
from xs

The model is created and trained with the following commands.

— MODEL CREATION : create single sequence key with mulitple prediction values
create mining model ts_method1
(   x long key time
, y1 double continuous predict
, y2 double continuous predict
) USING MICROSOFT_TIME_SERIES

— MODEL TRAINING : train the model
INSERT INTO ts_method1(x, y1, y2)
OPENROWSET(‘SQLOLEDB.1’
, ‘Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=master;Data Source=.\SQL2008R2’
 , ‘        with xs(x) as (select convert(money,1) union all select x+1 from xs where x<20)
select x, x/2+20 as y1, 2*x as y2 from xs’
)

Note with this method, the predicted values (function) must be explicitly identified. That is, we can predict y1 or y2 but not y1+y2. For example, we can predict y1 with;

— select flattned results for y1
select flattened predict([y1],5)
from [ts_method1]

Method 2 – Single Y Value and a Composite Key

The second approach incorporates the function (or class type) into the model key. We can see that the data for the model shows a y_value that is dependent on both the x and y_function columns.

with xs(x) as (select convert(money,1)
union all select x+1 from xs where x<20)
select x ‘y1’ as y_function , x/2+20 as y_value
from xs
union all
select x
, ‘y2’ as y_function, 2*x as y2
from xs

The model can be created and trained with the following commands;

— MODEL CREATION : create complex sequence key with single prediction values
create mining model ts_method2
 x long key time
, y_function text key
, y_value double continuous predict
) USING MICROSOFT_TIME_SERIES

— MODEL TRAINING : Train the model
insert into ts_method2(x, y_function, y_value)
OPENROWSET (‘SQLOLEDB.1’
, ‘Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=master;Data Source=.\SQL2008R2’
 , ‘        with xs(x) as ( select convert(money,1) union all select x+1 from xs where x<20)
select x, ”y1” as y_function, x/2+20 as y_value from xs
union all select x, ”y2” as y_function, 2*x as y2 from xs
order by 1
)

Additionally, it is important to note that training Time Series model requires ordered data.

Prediction from a model which includes a composite key requires that the function (class) be specified in the query. The first query (as the results on the right show) does not show what class relates to what predictions. Note that two series of data are returned

— Q1 : Prediction without class specification
select flattened Predict([y_value], 5)
from [ts_method2]


This can be overcome by showing the class in the result set (in which case all results are still returned

— Q2 : Prediction showing class specification
select flattened [y_function], Predict([y_value], 5)
from [ts_method2]
or, by specifying the class that will be predicted.

— Q2 : Prediction specifying class
select flattened Predict([y_value], 5)
from [ts_method2]
where [y_function]=’y1′

The addition of class tags into time series models improves their useability by disseminating an aggregate sequence into components. This post has looked at the two methods for including class tags and the prediction implicatios of each.