Thomas.I Microsoft BI & Analytics

A Site Dedicated to General BI Issues and MS BI Issues: Architecture and Technology

Multiselecting Dates in Excel 2013 on top of SSAS/BISM Multidimensional

Posted by thomasivarssonmalmo on March 3, 2013

It is not uncommon to compare BI clients on a very detailed feature level in discussions with customers but this blog post is about a question in the Analysis services forum that I posted an answer to. I have written a longer blog post on the SolidQ blog about the most important new features in Excel 2013 for multidimensional analysis and this post add a feature that was not on my mind while I wrote this:

http://blogs.solidq.com/Business-Intelligence-SQL-Server-pa-svenska/Post.aspx?ID=15&title=Excel+2013+and+the+new+Multidimensional+Improvements

You can work with sets of dates in Excel 2013 if you use the GUI approach. First you can use the old Pivot Table report filter approach like below where you have a filter above the Pivot Table.

Add Dates as Report Filter

If you would like to use a date interval you activate “Select Multiple Items” in the left corner and then you are left to click-click and click for all the relevamt dates.

Report Filter Select Multiple Items

This might involv a lot of clicking especially if you would like intervals of 20-30 dates.

With the Excel slicers you have a much more efficient way of marking ranges of dates. I assume that you know how to add slicers for a natural hierarchy in Excel. Below I have selected the first 5 days in July 2005 by clicking the first date in the interval and then using shift ´click on the last date in the interval.

Use shift in slicers to mark date intervals

In Excel 2013 there is a third approach with Time Lines. That option is placed next to Insert Slicer in the Ribbon under PivotTable Tools.

Insert TimeLine Ribbon

You are presented to the option to select the date hierarchy.

DateHierarchyChoicesTimeLine

I have swedish settings and År means Year. You can select levels in the upper right corner to select years and then the months of interest for quick navigation. I am on my way to the first five days in July in the picture below.

TimeLineSelection2005

When I have navigated to the date level of July 2005 I simply drag the cursor over the dates that I am interested in and the Internet Order Quantity sums up in the same way as with the slicer approach.

DragTheCursorOnTheTimelIne

It is always important to know about these less apparatent features in clients that will make the analytic daily work easier for our customers.

Advertisements

Posted in Excel 2013, OLAP klients | Tagged: | 4 Comments »

2012 in review

Posted by thomasivarssonmalmo on January 1, 2013

The WordPress.com stats helper monkeys prepared a 2012 annual report for this blog.

Here’s an excerpt:

4,329 films were submitted to the 2012 Cannes Film Festival. This blog had 39,000 views in 2012. If each view were a film, this blog would power 9 Film Festivals

Click here to see the complete report.

Posted in No Category | Leave a Comment »

ISOWeeks in SSAS Revisited

Posted by thomasivarssonmalmo on November 24, 2012

I wrote a blog post a few years ago about IsoWeeks in SSAS and now it is time to do an update. Since SQL Server 2008 and later we have a TSQL Datepart() argument that will remove the need to build TSQL User Defined Functions to add this information into a date dimension table. You will see this in the code samples but have a look at the datepart() TSQL function i Books On Line for more information.

ISOweeks seems to be the fashion here in the Scandinavian countries. The main idea is that a year can contain 52 or 53 weeks depending on if the new year starts on Thursday or later. All ISOWeeks have seven days and the main problem for SSAS or BISM Multidimensional is that with ISO weeks they cross over calendar years and this means a many to many relation between ths IsoWeek and the calendar year if you try to add a calendar year as a hierarchy level above IsoWeek. The question is if you should add a year level above the IsoWeek or not. If you decide to add a year level that must be a business defined IsoYear level and not a calendar year. In this blog post we assume that such a level is needed and the business rule is that week 52 and 53 should always belong to the previous year and week 1 to the new year. As an outcome of this descision is that ISOYears and calendar years will not have sum up in the same way. Totals over years will be the same but not the yearly distributions.

Enough said and now it is time to transfer this IsoWeek requirement into code and a working solution. My code is a theoretical example and has no real world background except for the date dimension. It is simplified to check that the numbers are summed correctly and includes one order for each day that is part of the date dimension.

I will start with the code for a date dimension below.  I am using a CTE(TSQL Common Table Expression) with  a start date(2009-01-01) and an end  date to the last date of the current year ( where   Year(DateValue) < = Year(GetDate()) ) . You can also see the datepart-function with the ISOWeek argument. I am doing a select Into TSQL statement to create a new table from the CTE. The last column(1 as IsoYearWeek) was added to create an integer column that we will use in a second step. The column created with the TSQL case statement is the business rule mentioned earlier to create the IsoYear classification. I thanks my colleague Eva Eriksson for her kind help.

 

DateDimensionCode

 

In a second step I use this TSQL statement to update the IsoYearIsoWeek column:

Update dbo.TestDateDim
Set IsoYearIsoWek = IsoYear * 100 + IsoWeekInt

The result will look like this in Management Studio.

DateDimensionTable

 

Finally it is time to create the dummy fact table with one order for each date in the date dimension. This is also done with a CTE part of SQL Server since the 2005 version.

 

FactTable

It is time to build the date dimension and the cube in the SQL Server 2012 Data Tools previously named BIDS. I assume that you now how to do this except for the settings I have added all attributes to the date dimension. In the wizard you should set the type properties for the different date attribute members that will be part in MDX Time calculations. Some of the attribute types are kept as regular because they are not important for this example.

AttributeTypesDateDim

I have created these hierarchie below in the date dimension.

 

DateHierarchies

I have also created the attribute relations for the hierarchies like this.

 

Attribute relations

Finally it is time to add the fact table. In the cube structure tab it look like this.

 

FactTable2

And in the dimension usage tab it look like this.

Fact Table

 

Process the cube and start Excel to have a look at the cube. I am using Excel 2013. I have created two pivot tables from the same connection. The pivot table to the left is the IsoWeek hierarchy and the one two the right is the calendar  hierarchy. We have the same totals but the distributions differs. If you expand the IsoWeek hierarchy for IsoYear 2012 all weeks should have 7 as the sum of orderQty, like you can see in the second picture.

 

TheTwoHierrachies

 

IsoWeekExcel

I a future post I will cover a  many to many requirement in a date dimension with weeks.

Posted in Analysis Services SSAS | Tagged: , | Leave a Comment »

Excel 2010 and Excel 2013 Slicer Settings

Posted by thomasivarssonmalmo on November 20, 2012

Here is an interesting new functionality in Excel 2013 regarding slicers with BISM Multidimensional. One of my colleagues asked for this functionality in Excel 2010 when he built reports with slicers for publishing in SharePoint 2010 Excel Services. The slicers should not show members without data in the slicer and this is obviously not the fact with Excel 2010 as you can see in the picture below. There is not such choice in the slicer settings(right click on the slicer)

OldExcel2010SlicerSettings

In Excel 2013 this was changed to the bettter and I did not figure this out until today when i remembered the colleagues wish.

 

Excel 2013SlicerSettings

There is a new setting added that I have underlined, Hide items with no data. This setting works as you can see if you compare the slicers in Excel 2013 to the ones in Excel 2010(first picture). The members without data, lightblue, are gone.

Posted in Excel 2013 | Tagged: , | Leave a Comment »

Estimated File Size in BISM Multidimensional(SQL Server 2012)

Posted by thomasivarssonmalmo on September 28, 2012

One property that has changed in the latest release of SSAS, now called BISM Multidimensional, is the Estimated Size of the cube database in Management Studio. This property no longer tells the whole story about the cube database size. 

Start Management Studio and connect to your SSAS service. Expand the databases folder and righ click –>properties on a cube database of your choice.

MyCubeDatabases

I will right click on the Adventure Works DW 2008R2 database and select properties.

MMS Estimate

In the Estimated Size property above you can see 20.66 MB. This might look good since the source database file size is 141 MB. That implies a size down to around 14 percent of the original database size.

The problem is that this estimate is not correct for the total cube database size like you can see in the next picture.

FileFolderSize

The cube database resides in C:\Program Files\Microsoft SQL Server\MSAS10_50.MSSQLSERVER\OLAP\Data on my hard drive. There is one folder for each cube database.

It is Swedish(storlek på disk = size on disk) and says that the cube database is 89.5 MB on my hard drive. The size is now 63 percent of the original database file size. This is actually the true story.

Be careful with the estimated file size in Management Studio in SQL Server 2012.

Edit: Update(2012-11-11). After the kind assistance of Akshai Mirchandini, in the MS team I have got some clarifications that was not clear to me when I wrote the post.

  • This estimate was changed already in SQL 2008 R2
  • The reason for this change was the time to open MMS and get an update of the total file size was too long
  • It is an estimate of the compressed total file size.

Posted in No Category | Leave a Comment »

Power View in Excel 2013 Part Two: Maps

Posted by thomasivarssonmalmo on July 22, 2012

This is my second blog post about the functionality in Power View for Excel 2013 that will show you have easy it is to create map visualizations in Excel 2013.

I start with the basic model with relational data from the Adventure Works database with country, calendar year and internet order quantity. What you can see here is a basic tables when you drag the attributes and the measure to the default box in the down right corner.

The Basic Model

Now we will change this boring table into something more visually useful. Make sure that the table is marked and press the map button in the ribbon.

The Map Button

After you have pressed the map button you will see something like this on the canvas.

 

The first map

 

But we can extend this even more. Here is the design tool to the right where I have added CalendarYear to vertical multiplies.

 

The map design tool

The result is this map.

The final report

The purpose of this blog post is to show you how easy it is to integrate maps into your reporting and change a boring table into something more useful with a few clicks.

Posted in No Category | 1 Comment »

Power View in Excel 2013 Part One: Pie Charts

Posted by thomasivarssonmalmo on July 20, 2012

The integration of Power View into Excel 2013 is the biggest news for the BI-functionality in Excel. Chris Webb has already blogged about the map functionality in Power View  Excel sheets. I will describe a minor improved functionality that is new and useful. Pie charts was not part of the SQL Server 2012 release of Power View in SharePoint 2010 mode.

I have built a model in Excel 2013 from relational data in the Adventure Works database hosted in SQL Server 2012. It is a PowerPivot model that automatically gets generated for you when you import data from several tables into Excel 2013.

The data model

I have a few dimension tables and one fact table above. The entire Power View sheet in Excel 2013 looks like this with my pre designed dashboard.

The entire Power View Sheet in Excel 2013

In the world of Power View all report objects that you create are automatically related so if you click on one bar in one chart the other charts will be filtered. With Excels classic Pivot Tables you will have to do all that join work on your own with slicers. Another thing to realize is that Power View is not a tool with a lot of settings that gets you detailed control of how the graphics work. If you need that detailed control and many settings you should stay with Excel Pivot Tables.

In the upper right corner you have the pie chart but there is more. If I click on the United States bar in the down right corner the pie chart will look like this.

Power View Dashboard in Excel 2013 Interactivity

The area in the pie charts will show the United States product categories order quantity during different years related to the total order quantity.

Nice pie charts!

Posted in Excel and BI | 1 Comment »

Excel 2013 and BISM Multidimensional

Posted by thomasivarssonmalmo on July 19, 2012

This is the first review of the new Excel 2013 BI features and I will start with the news regarding Excel 2013 as a client for SSAS cubes. In the SQL Server 2012 release SSAS has changed name to BISM Multidimensional. With this setup you cannot, yet, use the Power View report sheet that is built in to this new release of Excel. Let us hope that an update will arrive later that integrates Excel 2013 Power View reports with BISM Multidimensional.

Here is the list of new Excel 2013 features for BISM Multidimensional:

  • Support for both local calculated members and calculated measures.
  • The Quick Explore tool for changing the Pivot Table and Chart more quickly
  • The Time Line slicer tool
  • You can build Pivot Charts without an annoying Pivot Table

All this new functionality are extensions to Excel Pivot tables and Pivot Charts that you can see as the work horse for doing analysis on multidimensional cubes. I have worked with dashboard projects that relied heavily on Excel 2010 and Performance Point Services in SharePoint 2010 that was appreciated by the customers. If you know how to use slicers and charts in Excel 2010 it is very easy to build report eye candy with SSAS as the data store.

Calculated Members and Measures

In Excel 2010 we got support for local named sets that could combine members from different dimensions. For ProClarity users that was not good enough because many of them also used the calculated measures and members functionality. With Excel 2013 we finally get a client that can build all local objects that ProClarity can.

The editor for calculated measures is here under PivotTable Tools in the ribbon.

Olap tools button

Below you see the calculated measures editor where you set the name of your clculation, the displayfolder for the calculation and the MDX expression. I have made a very simple calculation, Internet Order Quantity x -1

Calculated Measure Editor2

When you have saved this expression it will appear in the metadata pane to the right in the Excel dimension tool.

Pivot Table Calculation

Calculated members are MDX expressions for dimension members and are also supported in Excel 2013. Here is a simple summary of two states in Australia:

SUM({[Customer].[Customer Geography].[State-Province].&[TAS]&[AU],[Customer].[Customer Geography].[State-Province].&[VIC]&[AU]})

In the picture below you can see that I have created this calculated member under the Customer.Country parent attribute.

Tasmania and Victoria

When I drag the Customer.Country attribute to rows in the Pivot Table this new member will appear.

Tasmania and Victoria PT

The Time Line Tool

This is a new kind of slicer where you can mark continous values like years without have to use the control button to select several years in a slicer. It also have a more slim look and feel that will make you dashboards look better in Preformance Point Services.

TimeLine

This new tool is next to the Insert Slicer button in the Ribbon as you can see above. I have made an ugly Pivot Table with this tool in the picture below.

The TimeLine Slicer in Action

 

Pivot charts without Pivot tables

In the previous versions of Excel you will always get a Pivot Table even if you only want a Pivot Chart and that means that you will have to drag the table to an area that will not show in a dashboard.

Insert Chart

Select the insert tab on the Ribbon and Pivot Chart. If you click that button you can now select Pivot Chart only or borth chart and table.

 

Quick Explore

When you click in a Pivot Table on members or cells with measures you will see a small box appearing that will launch Quick Explore.

Quick Explore

What you can do with this tool is to more quickly change dimension and members or create supporting charts. I expect that this is useful for touch screen scenarios as well.

If you double click on a hierarchy member Excel 2013 will now go down on that member and expand the next level. If you double click on Canada the Canada members on the next level will show up.

I will probably update this post as I play around more with the multidimensional support in Excel 2013. Bubble charts and Scatter charts are two desirable graphs that are not supported in Pivot Tables/Charts in Excel 2013 but they are included in the Power View part of Excel 2013. If we get multidimensional support in Excel 2013 Power View that limitation is gone.

Posted in Excel and BI | Tagged: , | 1 Comment »

The Data Warehouse Value Proposition

Posted by thomasivarssonmalmo on February 18, 2012

This is a slide that I am working on tryng to present what the customer pays for compared to running canned reports directly on top of source systems. I do try to avoid details from a customer perspective

 

image

 

This is what I have in my mind trying to explain what I am doing and what the customer is paying for.

Posted in No Category | 3 Comments »

My Quick Guide to SSAS Query Tuning Part One

Posted by thomasivarssonmalmo on January 28, 2012

The intention of this blog post is to write a practical introduction to SSAS Query tuning with the SQL Server Profiler tool. I think that SQL Server profiler can be very usefu but it also have its issues with delivering too much information and in a very unclear way. I hope that this post can be helpful and show how Profiler interacts with the SSAS engine.

The first step is to build a modified Adventure Works cube that only use basic measures from the fact table, no MDX scripts(except for the default calculate() and with no measure expressions. With measure expressions you will get more events, in Profiler for a single query, than you need to start learning this tool. I am using SSAS 2008 R2 SP1 and the Adventure Works DW database and cube project available on www.codeplex.com.

I have two measure groups in thiis model.

TheMeasureGroups

The following dimensions are used. They have no attribute relations yet, only flat connections with the dimension key in each dimension. You can use as many as you like. I have included more dimensions than I need for this blog post but I will refer back to this model in later posts.

TheDimensions

Finally, these are the relations between the dimension and measure groups. It is exactly as in the standard Adventure Works model but without the measure expressions.

The relations

There are no partitions in the measure groups and no aggregations in the measure groups. We will add that in a later post and see the impact of that. I will improve this model step-by-step.

I assume that starting SQL Server Profiler is not an issue here. I have a saved template that I reuse for query tuning. The focus in this blog post are some of the following events.

The Profiler Trace Events

Now it is time to start the fun. I assume that you have the SSAS service up and running and that you have started SQL Server Management Studio and connected to SSAS. Otherwise you will se a lot of file load events that I have described in two previous blog posts. Start the profiler just before running each query and after each query stop it and clear the events. What you will see here, at best in this blog post, is storage engine events and nothing in the formula engine since this model do not include MDX scripts yet.

I will start wih this query:

Select {} on 0
From [Adventure Works DW2008R2 Performance];

Here is a subset of the events that you will see in Profiler.

FirstQueryNull

There are no Query Subcube events so no activity has been going on in the storage engine. The query was sent to the server and a result was returned(Serialize Results Current).Resource usage shows zero on all counters. Thanks to Chris Webb research about these counters here

The second query will use a basic measure group measure on the columns axis and nothing more.

Select {[Measures].[Order Quantity]} on 0
from [Adventure Works DW2008R2 Performance]

.

Query2aPartitions

The is one reading from the measure group and partition containing the [Order Quantity] measure.

A new event occurs that was not shown in the first query. The event subclass numbers 2 and 22(query subcube and query subcube verbose means that data was not retrived from the storage engine cache.

Query 2 Query Subcube Verbose

If you look at the Query Subcube Verbose event in the more detailed window that appears if you mark that event you will see this.

Query 2 Subcube Verbose

We did only send a query that requested a measure from the cube but no dimensions, like you can see above. On each dimension you have a line of zeroes meaning that no specific dimension member was asked for in the query. Profiler only shows the dimensions related to the measure in the measure group. The line of zeroes is a mathematical model called vectors and that is how SSAS currently shows its internals.

This is not the main point here but the resource usage counters are the following for this query explained from Chris Webbs blog post above.

Query 2 Resource Usage

The reads tells you about the number of disk read operations for the query and the reads in kb tells you about the physical amount of data that was retrieved.

The number of rows scanned are the number of rows decoded/filtered/aggregated by the storage engine.The number of rows returned are the number of resulting rows from the same query. I will not go into detail about these counters.

The next query will retrieve the same measure as before but add a new measure from the second measure group.

Select {[Measures].[Order Quantity],[Measures].[Order Quantity – Fact Reseller Sales]} on 0
from [Adventure Works DW2008R2 Performance];

For the same measure that we asked for in the pervious query we will get this event in Profiler.

Fråga3Cache

The data above is retrived from the storage engine measure group cache. In the query subcube and subcube verbose you will see both event subclass 1 and 21 showing that the data was requested from the measure group cache.

The second measure goes through the same process as the measure in the previous query.

Query 3 second Measure

The data is not cached since it has never been retrieved before.

Both Query subcube verbose events still show the same zero for all dimensions. This is the query subcube verbose event for the second measure.

Query 3 second measure query subcube verbose

If you like you can run the third query a second time to confirm that both measures will be retrieved from the storage engine cache.

Finally it is time to see what happens in profiler when you run queries for dimension members. I will only use a date dimension to show this.

This is the first query.

Select {[Order Date].[Calendar Year].&[2010]} on 0
From [Adventure Works DW2008R2 Performance];

This is the clear message from the SSAs engine that you have requested a dimension member in the Query Subcube event.

Query Subcube query 4 query subcube

Can you see the 1 appearing at the end?

Query subcube verbose is a little bit more generous.

Query4 verbose

There is a number 2 on the order date dimension. This is the internal SSAS DataId of that member.

Finally if you run this query with many dimension members:

Select {[Order Date].[Calendar Year].&[2005],[Order Date].[Calendar Year].&[2006],[Order Date].[Calendar Year].&[2007]} on 0
From [Adventure Works DW2008R2 Performance];

Query 4 Many dimension members

Can you see the star * on dimension 4? This means that all members was requested from that attribute. 

If you run this query with two dimension members:

Select {[Order Date].[Calendar Year].&[2005],[Order Date].[Calendar Year].&[2006]} on 0
From [Adventure Works DW2008R2 Performance];

Query4 two members

Here you will see the plus sign + . This means that more than one member was requested.

I hope that this first part of the query tuning can explain the storage engine retrieval of data in Profiler and how it describes the retrieval of dimension members.

Posted in Analysis Services SSAS, No Category | Tagged: | Leave a Comment »