다음을 통해 공유

Datazen Load On Demand Using Analysis Services and MDX

  • 아티클

Introduction

Kudos to the SQL Team with the great walkthrough of getting started with SSAS and MDX.  If you have not checked out that tutorial please read it here first.  This article assumes you have Datazen running with Adventure Works just like in their tutorial. Now that you have gotten some data into Datazen with SSAS, you want to do a little more.  For example: select a category and have it show the values for the children while also selecting a time range.  This article will show you how to do advanced Load on Demand functionality such as this using the Control Panel, Datazen Publisher and MDX.

Part 1 - Creating Data Views

We have already said we want to be able to select the categories and use a time navigator to select a time range.  This will mean we need 3 Data Views: 1 Data View for Category Selection List, 1 Data View for the Time Navigator which will be filtered by the Selection List and 1 Data View to display the results of the selections.
We will assume you have successfully created your Adventure Works Data Connection as in the Getting Started Tutorial.  From there we will click New Data View.


select [Measures].[Internet Sales Amount]  on  0,
[Product].[Product Categories].children DIMENSION PROPERTIES UNIQUE_NAME, MEMBER_CAPTION on  1
from [Adventure Works]

Here we have setup our query to return us the Children of the Product Categories along with their Unique Name and Member Caption.  Unique Name will be the Keys, and Member Caption will be the labels.  Once you click next you have the ability to update the names.

Now once we next through and finish creating this data view, we can create our next one for the time navigator.


WITH

 MEMBER [Measures].[Total Sales] as  ([Measures].[Internet Sales Amount] + [Measures].[Reseller Sales Amount])
 
SELECT
  {
  [Measures].[Total Sales], 
  [Measures].[Internet Sales Amount],
  [Measures].[Reseller Sales Amount]
  }
  ON 0,
NON EMPTY
[Date].[Date].children
ON 1
FROM [Adventure Works]
WHERE STRTOMEMBER({{ ISEMPTY(@category, '[Product].[Category].[All Products]') }})

Here we have selected the Sales Values for all the Dates in the Cube.  We have added to the where clause a string to member and inside there, we have a Datazen ISEMPTY function to check the value of the category.  When it is empty, which is the default ALL value, we will return all products or else we will filter by the selected category.  Next we setup the Parameter with a blank default value - click add, then click Done.


Once we click next, we'll see the next screen showing us the columns, and we will update them to have nice display names and set the data type for Date to DateTime, to be recognized as a DateTime within Datazen Publisher.  Once complete, we can click next and finish.

The final data view we will create is the time chart load on demand dataview, this will provide all the filtered results for the dashboard.

WITH
 MEMBER [Measures].[Total Sales] as  ([Measures].[Internet Sales Amount] + [Measures].[Reseller Sales Amount])    
 MEMBER [CATEGORYNAME] as  [Product].[Product Categories].CurrentMember.Name
SELECT
  {
  [CATEGORYNAME],
  [Measures].[Total Sales], 
  [Measures].[Internet Sales Amount],
  [Measures].[Reseller Sales Amount]
  }
  ON 0,
NON EMPTY
{
    Filter({ STRTOMEMBER({{ ISEMPTY(@category, '[Product].[Product Categories]') }}).Children * 
    STRTOSET("[Date].[Date].&[" + format({{ @startTime }}, "yyyyMMdd") + "] : [Date].[Date].&["  + format({{ @endTime }}, "yyyyMMdd") +"].Lag(1)") } , ([Measures].[Internet Sales Amount] + [Measures].[Reseller Sales Amount]) > 0)
} on  1
FROM [Adventure Works]

This query has a couple of members defined, one is the CATEGORYNAME, which we want to get the Current Member name.  The reason we use this is because we want to ensure that the column coming in is static when we change the category level between top level categories and sub categories.

In the rows section we have a filter here which we want to filter only the values for dates as well as the category, where the sales are greater than 0.  We need to use a STRTOMEMBER method here to check the category.  If it is empty we want the children of all products, if we have selected an item it will get us the children (sub categories) of the selected category.  For the time range we use a STRTOSET to build a set based on the startTime and endTime coming from the time navigator.  We use a format string of yyyyMMdd as this is what the members are using and we add a Lag(1) on the end of the last member to get the member before endTime (TimeNav usually includes the next day as it goes midnight to midnight).

Like the other data views, we will clean up the names and change the Data Type of Date to DateTime.  We will not include the MEMBER_CAPTION column as we are already getting that with CATEGORYNAME.  We can next through and Finish.  Now that we are done the data views we can create the dashboard.

Part 2 - Creating The Dashboard

Once we've logged into our Datazen server we can create our Dashboard.  We will start by triggering the menu and then clicking create dashboard.  We will start from an empty dashboard.

On the design surface we will need : 1 x Time Navigator, 1 x Selection List, 1 x Number, 1 x Totals Chart, 1 x Time Chart.

Settings for the Totals Chart:
* In Layout View - Set Data Structure to ByRows
* In Data View - Select the Time Chart LOD table, Set Category Column to CATEGORYNAME, Set Main Series to Total Sales.

Settings for the Time Chart:
* In Layout View - Set Data Structure to ByRows
* In Data View - Select the Time Chart LOD table, Set Value Field to Total Sales, Set Series Name Field to CATEGORYNAME

Settings for the Time Navigator:
* In Layout View - Set Time Levels - Add Days
* In Data View - Select the Time Navigator table, set Series for Background Chart to Total Sales

Settings for the Selection List:
* In Data View  - Select Category List table, set Keys as Unique Name, set Labels as Label

Settings for Number:
* In Data View - Select Time Chart LOD table, set Main Value as Total Sales

Once these are set, we need to update the load on demand data views to use the parameters.  You will know they have parameters because of the green P next to the name. 

Once you click the charm, you can then click Param button to set the parameters.


We will update the parameters for the Time Chart to use the selected item from the Selection List as well as the Selected Start time and Selected End Time from the Time Navigator.  Once this is done, we will update the parameters for the Time Navigator data view to use the selected item from the category list as well.

Now that we have finished all the setup, we can update the titles of the controls and then run preview.

Once you are happy with your dashboard, you can publish it and it will be available to all devices.

Here is an equivalent HMTL5 representation.