Browsed by
Category: SSAS

SSRS: Using date pickers to filter SSAS cube data

SSRS: Using date pickers to filter SSAS cube data

When you have a report that pulls data from an SSAS cube, you have most likely used a date range filter on which time series you would like to display. It is not so straightforward though, to create date-parameters in SSRS and in your MDX query you refer to these queries. Because MDX needs to interpret the date values in a completely different syntax, we have to format the data. This post shows you how to accomplish this. This post does not describe how you should format the MDX to make this work, I assume that you have your dataset ready to go to use in your report.

The first step is to create two dimensions for the date range. The types of these parameters is “Date/Time”. There are no specific other settings for these parameters, as these are the date pickers that the end user can use to select a date range. So when you start the report, you will only have these parameters:

 

The next thing to do is create parameters that contain the formatted values that the MDX query can read. To accomplish this, create two more parameters called “FromDate” and “ToDate”, of type “Text”, and set the visibility on “Internal” (this causes the parameter to be hidden, but the values will change when the selection of other parameters change). On the “Available Values” tab, select “None”. On the “Default Values”, the following expression should be used:

=”[Order Date].[Date].&[” & Format(Parameters!FromDate.Value, “yyyy-MM-dd”) & “T00:00:00]”

The second parameter should contain this expression as the default value:

=”[Order Date].[Date].&[” & Format(Parameters!ToDate.Value, “yyyy-MM-dd”) & “T00:00:00]”

When you run the report now, you will see that the selected dates will be applied to your dataset. If not, you can always set the last two parameters to “Visible” and run the report. That way you will see how these parameters are formatted. Most of the errors that I got is that the descriptions of the cube dimensions were not completely correct.

Various SQL date formats to use with SSIS, SSAS and/or SSRS

Various SQL date formats to use with SSIS, SSAS and/or SSRS

While designing data warehouses, SSIS packages, etc., like myself you’ve probably ran into the issue of having to format different date and time values that ends up in a long string of SQL code or expressions in SSIS. The purpose of this post is to collect some links to various pages where these kind of date and time formatting can be found.

Getting only date part of a datetime field

First of all, the function that I used the most is when I’m trying to get only the date part of a datetime field type. For example, when I’m looking at a sales order fact table, an order could have been placed at August 15th 2011 at 9:15am. This date would be stored as 2011-08-15 09:15:00:000. The issue here, is that it’s difficult to design your SSAS cube and use this field as a link to your time dimension table, because the time dimension table usually holds the dates only. Therefore, the following function could be used:

CAST(
FLOOR( CAST( GETDATE() AS FLOAT ) )
AS DATETIME
)

(thanks to Ben Nadel)

When you’re using SQL Server 2008 and above, you can also use the following function that is a lot easier to use. Since the date and time fields were introduced in SQL Server 2008, this won’t work in 2005 and earlier versions:

DATE(datefield)

Date formats

Some simple date formats can be found here. These are just some standard SQL date formats but these could help you tremendously to avoid having to concatenate several strings together.

Calculations: First and last day of the month

You might have ran into the issue of calculating dates in relation to a certain input date. This page shows you how to get the first or last day of the month in relation to a certain date.

Calculations: First and last day of the year

Just as calculating the first and last day of the month, you can see here how you can calculate the first and last day of the year.

Standard functions

Other than the ones above, if you are looking for a calculation in terms of number of days, weeks, months, years, etc., the standard DATEADD and DATEDIFF functions in SQL would do the trick.

Report on your reports

Report on your reports

When you deploy your SSRS reports, you might want to know about how they perform. Or maybe your reports perform allright, but there might be improvements on how the end users can be presented with the reports even faster. Also, you might have deployed a bunch of reports that nobody reads but they’re just sitting there… To get insight into these things, the ReportServer database has a few tables that contain log records of your reports.

First of all, by default the records are kept for 60 days. Older log entries are deleted. Since I always want to have a little bigger time frame of log data, I have changed the setting of this. You can find this setting in the table ConfigurationInfo in the ReportServer database. For example, I’ve used the following query to change the settings so the log entries are kept for a whole year:

UPDATE ConfigurationInfo
SET Value = ‘365’
WHERE Name = ‘ExecutionLogDaysKept’

Right, so if you also want to have log data for a year to use this functionality, please come back in 10 months… just kidding, the 60 days by default should usually generate enough data that you can test out the functionality described in this article.

As of SQL Server 2008, the log data is stored in the table dbo.ExecutionLogStorage (in earlier versions, the table is called ExecutionLog). For some reason Microsoft does not recommend to query against tables directly, therefore there are three views created to use for querying the log. By default this view is called ExecutionLog, in SQL Server 2008 the view ExecutionLog2 was introduced, and in 2008 R2 the view ExecutionLog3 was introduced. The only difference between ExecutionLog2 and ExecutionLog3 is two column names. ReportPath is called ItemPath and ReportAction is called ItemAction.

On to the next step… getting an idea how your end users view the reports. Well, this is a very straightforward thing, if you log table has grown proportionally, it means that your work has paid off. There are several columns in this table/view that gives you an idea what the users have been browsing for and the type of actions that have been performed.

To measure the performance of your reports, there are five columns that are important to take a look at. These columns are:

  • TimeDataRetrieval: This value reflects the number of milliseconds spent interacting with data sources and data extensions. This includes all data sets for the report itself and datasets for possible subreports. This value includes the time that is spent opening connections to the data source and the time spent reading the data from the database. This retrieval executes in parallel (in some cases this is not possible), therefore the number that is displayed here is not the sum of the retrieval of the datasets, but the dataset that took the longest to retrieve. In those cases where the datasets are retrieved in sequence, this number is the sum of the retrieval of all data sets.
  • TimeProcessing: This value (also in milliseconds) is the time spent on processing the data in the reporting engine. This phase also includes the processing time of the tablixes, for example sorting, filtering, grouping, etc. 
  • TimeRendering: This value (I’m sure it’s clear now in which measurement unit…) reflects the time spent on rendering the reports and applying the pagination to the reports.
  • ByteCount: This is the number of bytes that are generated for the output of this report request.
  • RowCount: The number of rows that were retrieved from the data source to generate this report.

I’ll conclude with some tips how to prevent reports from taking a long time to load based on the values in the above five columns:

  • When TimeDataRetrieval has a high value, try to modify your query to the data source. Narrow it down to the data that you need. Try to avoid SQL statements with “SELECT * “, where all the columns of a table are retrieved.
  • When TimeProcessing is high, try to “prepare” the data differently that is used in your report. It’s sometimes easier to use an ORDER BY in your query that eliminates the need of sorting in your tablixes. Also, try to use as many filters in your SQL query to reduce the data that is processed.
  • When TimeRendering is high, try to rearrange items on your report. For example, one list with one chart that is repeated 10 times is better than having 10 different charts each filtered on a different column value.
  • When ByteCount and/or RowCount is high, try to reduce the amount of data that you use for your report. When you have a tablix that shows the top 10 best performing sales agents, it’s better to have a TOP 10 condition in your SQL query than pulling the whole set and filter on your tablix.
Avoid SSRS to change your customized MDX datasets

Avoid SSRS to change your customized MDX datasets

I’ve experienced many times that when I changed the main dataset in SSRS (that reads from an SSAS cube), that the datasets of the parameters are changed again automatically. This has usually been a very annoying thing because I change the MDX in the parameter datasets very frequently.
To avoid this automatic change, you can add SuppressAutoUpdate=true in the code of your report that suppresses the change of this MDX. Right click the report and choose View Code. Find the dataset, and in the relevant node pertaining to your dataset, modify it. Then Save.

<Query>

<DataSourceName>DataSource1DataSourceName>

<CommandText> …

<rd:SuppressAutoUpdate>truerd:SuppressAutoUpdate>

<rd:Hidden>falserd:Hidden>

Query>

Source:
Error message explained: The ‘Role’ with ‘ID’ = ‘Role 1’ doesn’t exist in the collection.

Error message explained: The ‘Role’ with ‘ID’ = ‘Role 1’ doesn’t exist in the collection.

It could happen that you move around your cube files and with the deployment, you run into some errors. A common error that I’ve encountered again is the following.

The ‘Role’ with ‘ID’ = ‘Role 1’ doesn’t exist in the collection.
This happens mostly when you have copied a .cube and .partitions file to another project and added this to the new SSAS project/solution.
The underlying meaning of this error is that in your cube file, you have a role defined in your old project that has set specific permissions on this cube. When this role does not exist under the same name and the same permissions set in the new project, your deployment will fail with the above error.
I’ve always deleted the permissions on the cube and re-created them again with the roles that I’ve used in the new project. You can do this by opening up the cube file in Notepad or another text editor, and look for the text CubePermissions. Everything starting at this term (including the tag of CubePermissions as well), remove that node from the file.
Apart from possible other errors that you might stumble upon, your copied cube should now be able to be deployed.
Dimensional model in SCD2 (part 2)

Dimensional model in SCD2 (part 2)

Continued from part 1:

In addition to my previous post, I thought it would be helpful to display screenshots of how I designed my dimension.

This is the attributes tab:
… and this is how the attribute relationships look like:
With these attributes, I would like to be able to do analyses like the number of resources per gender, per country, per cost center, per language, etc…
Dimensional model in SCD2 (part 1)

Dimensional model in SCD2 (part 1)

This post is dedicated to an issue I’ve been dealing with for a while now and until so far I haven’t found a solid solution yet. Since I’ve been explaining and asking this to several experts over and over again, I’ll dedicate a blog post to it.

I’m a big fan of using Kimball’s Slowly Changing Dimensions, especially since it keeps track of historical data and it doesn’t explode your database. But… mapping this data into an SSAS dimension is still an effort, and let me explain my challenge by using some example data:

First of all, my dimension table looks like this:

Let’s take John’s example. John started the company on January 1st 2009 in the sales department in Delft. Halfway through the year they realized that he is needed in the sales team in Amsterdam, therefore John is transferred to the Amsterdam office on June 1st. Since John is staying in Amsterdam longer than expected, he is assigned another manager in February 2010.

In my fact table, the sales activities of John look like this:

In wording, he won a deal of 3000 euros when he was in the Delft office, a deal of 1000 euros after he moved to Amsterdam and another deal of 6000 euros after he was assigned another manager.

With this data, what I would like to achieve first of all is an analysis of the sales activities per location with a drilldown of sales reps on that location. In other words, it should show that the Delft location has a 7000 euro turnover (record 1 and 2) and that the Amsterdam location also has 7000 (record 3 and 4). With the drilldown, I should be able to see that it was John who was responsible for these sales.

Into SSAS where my challenge already starts with the creation of the dimension. These are the steps that I take:

1. Creation of the dimension: I create the dimension according to my dimension table, and the dimension is created as usual. No problems here.

2. Adding a hierarchy: I add a hierarchy according to the following structure: Location –> Business Key –> Surrogate Key.

3. Attribute relationships: By creating the hierarchy in the previous step, I also have to set the attribute relationships accordingly.

4. Deploy and process the dimension.

Processing the dimension generates the error that a duplicate key attribute has been found on the field “BusinessKey”. I understand the error, because if the location is a parent of the business key in the hierarchy, it goes wrong because there are more than 1 locations for one business key.

Then I continue with a few settings:

5. I set the key value for the field “BusinessKey” to the fields “BusinessKey” (as it originally was) and the field “SurrogateKey”.

6. Deploy and process the dimension.

The processing goes well, but when I browse the dimension, the hierarchy is displayed as follows:

  • Delft
    • Erik Monchen
      • 1
    • Erik Monchen
      • 2
    • Erik Monchen
      • 3
    • John Smith
      • 4
  • Amsterdam
    • John Smith
      • 5
    • John Smith
      • 6

I also understand why this is the behaviour because the key value of the business key is set to the business key and the surrogate key, but this is the only way how I can use the dimension without having errors…

In the end, I would like to achieve a structure like this:

  • Delft
    • Erik Monchen
      • 1
      • 2
      • 3
    • John Smith
      • 4
  • Amsterdam
    • John Smith
      • 5
      • 6

This way, it’s easy to link the fact table to the surrogate key value and the sales totals are added up correctly per location. Also, when I browse the cube where I’ve linked this dimension, it will display the location first, then when I drill down, it displays the names over and over again because that’s the next level in the hierarchy. If there is a way to change this view, I’d be open to use that as well though and not change anything to the dimension! The whole point of this exercise is to avoid having a list of double names when I browse the cube…

Since the method of Slowly Changing Dimensions is widely used, I was wondering how other people have accomplished this. I’m sure I’m not the only one out there facing this challenge!

Thanks in advance for your feedback!

Welcome to my blog!

Welcome to my blog!

Welcome to my blog! The purpose of this blog is to publish information that I have been collected over the past years to make life easier in the world of Microsoft’s BI platform. It has occurred to me many times that I asked myself the question “Now how did I do this-and-this again?”. After some extensive searches on Google I usually found an answer, but I thought to myself why not collect this information and share it with the rest of the world? The posts will be a mixture of information in the area of Integration Services (SSIS), Analysis Services (SSAS) and Reporting Services (SSRS).

Now that release 243 of Exact Synergy is available, I hope that a lot of people are going to use the integration of Reporting Services. Therefore I will be posting tips and tricks about the integration of Exact Synergy and Reporting Services as well.
Other than that, I’ll be posting stuff that is indirectly related to the MS BI platform, for example on the topic of SQL.
Happy reading!
Erik