Thomas.I Microsoft BI & Analytics

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

Archive for the ‘OLAP klients’ Category

Alternative to drill through in Excel 2013-Quick Explore

Posted by thomasivarssonmalmo on November 24, 2013

I sometimes get feedback on features from customers that I did not recognized when testing the feature on your own. Quick Explore in Excel 2013 is such a feature.

When we build BI solutions and start to deliver data in a tool customers usually would like to validate the data against referenced transactions in the source system. BISM Tabular and BISM Multidimensional both have a feature for this called drill-through but you have limited control over what will be presented.

I start with a Pivot Table in Excel 2013 that use a multidimensional cube but it will work the same in Tabular. You probably recognize the Adventure Works multdimensional  cube.


I the next step I will highlight the CY 2005 and United Kingdom cell that tells that the order quantity was 96. You will see a small magnifier glass appear to the right of the cell. That is Quck Explore.


Qlick on that icon and a small window will open up. I have choosen to go down drill to Internet Sales Order Details –> Sales Order Line to get the reference numbers.



The result might seem disappointing first since we will se all years of order quantity for United Kiingdom not only 2005.


UK for all years

This issue can be quickly fixed by moving years from columns in the report to the report filter box.



Finally we have a clean list will the sales order numbers for United Kingdom and the year 2005. It is a clean list without any other redundant information.



In a Tabular or Multidimensional model you will need to creat a 1-1 dimension with the fact table refered to as a degenerate dimension and in large fact table scenarios this can be a performance problem.

Happy Quick Explore!

Posted in BISM TAB & MD, Excel 2013, OLAP klients | Tagged: | Leave a Comment »

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:

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.


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.


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.


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

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

Excel 2010 on SSAS 2008 R2 Named Set Creation

Posted by thomasivarssonmalmo on June 3, 2010

I have started with some tests of new Excel features as a client for SSAS 2008 R2. I am comparing with what was supported and not supported in ProClarity 6.3.
This blog post is about creating named sets in Excel 2010 with SSAS 2008 R2 as the server version. In Excel 2007 you had the built in feature to build groups for dimensions members but the result was a long response time before the groups appeared. In Excel 2010 it is a different story. You can create named sets by a graphical click of members or you can write custom MDX for the sets. This has been possible in ProClarity since several releases but in Excel 2010 this is new.
You start with connecting to SSAS 2008 R2 and create a Pivot Table in Excel 2010. I assume that you know about that step.
With the Pivot Table available and active you click the Field, Items and Sets button in the Ribbon. It is part of the Pivot Table tools in Excel 2010. Click on the Manage Set button. I have selected the two last years from the date dimension in the Adventure Works 2008 R2 cube project. I selected the Create Set Based on Column Items but that option is also available directly when you press the Field, Item and Sets button. Notice that you have option to Create Set using MDX option when you select the Manage Set option under this button.
 The graphical UI looks like this:
Delete the members that you do not need. You can also create a display folder under the dimension that you are building this set on.
The result look like this after I have removed 2005 and 2006 in Excel 2010.
Let us continue with something that is a new feature and big news in Excel 2010 that did not work at all in ProClarity 6.3
Is it possible to save named sets that are created from two different dimensions?
Here comes the create set using MDX option.
 I have crossjoined the members and the product.category members above. This set will appear under its own parent Sets in the Pivot Table Field List. The result is seen in the next picture.
 I have not continued with the issue why the totals disappears but that is a subject for another blog entry. I still think that this new feature will be helpful for Excel 2010 users.

Posted in OLAP klients | Leave a Comment »

Leaf level queries in Gemini and SSAS 2008

Posted by thomasivarssonmalmo on September 18, 2009

SSAS cubes are good at answering queries that are designed for retrieving aggregated information. Sometimes end users also would like to use SSAS for queries that will flatten out the cube to the leaf level in dimensions and the fact table.  Response time in these scenarios are often slow because SSAS have no aggregations and will have to create the resultset dynamically, from leaf level data.
Let us have a look at what Gemini can do in the leaf level scenario. I have imported data from the Adventure Works 2008 DW source database. It is the customer table, the product tables and the fact internet sales table. Gemini take care of the relations so after the import is finished you can start building queries in the Excel pivot tables. I am using Excel 2010 with Gemini and SQL Server 2008 sp1.
I will start with building the leaf level query with the Adventure Works demo cube on SSAS 2008. I will put the leaf level customer together with the leaf level product on rows and calendar year on columns. The measure is internet sales amount.
Response time is around 1 minute on my laptop, Intel Dual Core 1,66 GHz and 4 GB RAM. Not bad but can be a liitle to much for stressed end users. And if you change the order of customer and product in the rows box you will have to wait again. Remember that these types of queries is not what SSAS is designed for.
Finally I will try the same in Gemini with the data I have imported before.
I have created a column in the customer table, FullName with this DAX syntax:  =[LastName] & ", " & [FirstName]. I also use English product name and SalesAmount.
On the same laptop response time is 10-15 seconds.
Conclusion: Gemini takes care of the scenario with end users creating leaf level queries that can make SSAS slow. Remember that SSAS is designed for answering top level or aggregated queries or if your queries goes from the top down to a special part of the cube.
Even if you not not load millions of records into Gemini it will still outperform running leaf level queries in Pivot tables on top of an Excel spreadsheet connected to SSAS, without Gemini..

Posted in OLAP klients | Leave a Comment »

First impression of Excel 2010

Posted by thomasivarssonmalmo on July 14, 2009

Office 2010 is here as a technical preview and I have installed it today as an upgrade of Office 2007.  As a BI consultant my primary interest is in Excel 2010 as a SSAS client and it seems as Excel 2010 works fine with SSAS 2008, at least on my laptop.
My first impression is that the user interface has not change that much since Excel 2007, when you query SSAS cubes. It will take some time to find all the new stuff  but I have found one important new feature that is not part of Excel 2007. Let us start with the user interface in Excel 2010. I have not installed the Gemini add in yet.
If I right click in the Pivot table a new option will appear that is heavily requested in the SSAS discussion group, and more important, by end users.  You will have a new feature called show values as with a lot of options(more than ten). I will show you one of the options, show values as % of grand total in the second picture.
 If I add another dimension you will get the correct percent of total of the nested dimension.
If you check the available options under that quick menu it is possible to see that Excel 2010 will reduce the number of complex MDX calculations for ratios that would otherwise to have developed by a consultant in SSRS or SSAS.
Edit: Chris Webb has a great review here.

Posted in OLAP klients | Leave a Comment »

Searching the web for information about the next MS SSAS browser for SSAS(2008?)

Posted by thomasivarssonmalmo on December 18, 2008

When MS bought ProClarity in early 2006 I never expected that this analytical software would be sent into the deep freezer for 3-4 years. Obviously I have been spoilt with the ProClarity development team that added good innovative capabilities in one year or two years release cycles.
The only new innovative end user tool I have seen from MS is the data mining add ins for Excel 2007.
Performance Point Monitoring and Analytics 2007, added some capabilities, like balanced scorecards, to the PPS Dashboard Designer, but no other innovative visualizations tools were added in that release.
What will happen in the near future? Since I am a MVP since october this year I can only refer to information already published on the web. What will a search about Office 14, the next Office release, or Performance Point(next version) reveal. Not much but some information can point to a direction.
Since ProClarity(that have seased to exist) and Performance Point are parts of the next Office 14 release you can follow what directions this next Office release is taking.
If I do a summary from blogs that i trust they say that Office 14 will both be a full webb release and a Windows release.
According to Paul Thurrot the Live version of the next Office release is delayed. That is one of the web versions. The web version is the only real indicator of the next release of Office I have found this far.
Other blogs connects the next Office with the release of Windows 7, that is the next O/S after Vista.
The time table, at present, for the next version of Performance Point seems to be the end of 2009 or early 2010.
And in 2010 the next version of SQL Server is supposed to be released.

Posted in OLAP klients | 2 Comments »

MDX and response time in ProClarity and Excel 2007

Posted by thomasivarssonmalmo on December 12, 2008

I have heared some talk, from other consultants here in Sweden, about the MDX generated by Excel 2007, when querying SSAS 2005 cubes, is slow in some scenarios.  The worst performance killers, from what I have been told are arbitraty shape queries and calculated members.
In this first blog post about client performance I will compare the MDX generated by ProClarity 6.3 and Excel 2007 (sp1) in two other scenarios.
It will help with finding a baseline of each clients performance under one standard scenario and one that is more extreme.
The first is a simple query with the customers and products dimensions nested together on higher levels in the hierarchy in the Adventure Works cube. 
The second scenario is the "query from hell" when customer and products are nested on leaf levels. I will take the MDX generated by the clients and paste it into MDX studio and run each query on a cold and warm cache. What we will miss is the time for each client to render the result sets graphically in grids. We will only measure the performance of the MDX generated in each client.
The first version of ProClarity was released at the same time as OLAP Services, the first SSAS version that shipped with SQL Server 7 in 1999. Excel 2007 was released one year after SSAS 2005, at the end of 2006.  
For Excel 2007 I have used the Pivot table add in that you can download from Codeplex. The hardware is a one year old laptop with Windows XP sp2 and 4 GB RAM.
This is the first query in ProClarity.
This is the MDX generated by ProClarity.
SELECT { [Date].[Calendar].[All Periods].CHILDREN } ON COLUMNS ,
{ { { [Customer].[Customer Geography].[All Customers].CHILDREN } * { [Product].[Product Categories].[All Products].CHILDREN } } } ON ROWS 
FROM [Adventure Works]
WHERE ( [Measures].[Internet Sales Amount] )
On a cold cache in MDX I have got the following counters.
Time              : 3 min 25 sec 334 ms
Calc covers       : 182114
Cells calculated  : 198643
Sonar subcubes    : 3664
SE queries        : 182110
Cache hits        : 185750
Cache misses      : 18
Cache inserts     : 11
Cache lookups     : 185768
Memory Usage KB   : 65408
On a warm cache I have got these counters.
Time              : 109 ms
Calc covers       : 3
Cells calculated  : 96
Sonar subcubes    : 1
SE queries        : 1
Cache hits        : 1
Cache misses      : 0
Cache inserts     : 0
Cache lookups     : 1
Memory Usage KB   : 0
In Excel 2007 the same tablee looks like this.
The MDX generated by Excel 2007 looks like this.
SELECT NON EMPTY Hierarchize({DrilldownLevel({[Date].[Calendar].[All Periods]})}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS ,
NON EMPTY CrossJoin(Hierarchize({DrilldownLevel({[Customer].[Customer Geography].[All Customers]})}),
Hierarchize({DrilldownLevel({[Product].[Product Categories].[All Products]})})) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS 
FROM [Adventure Works] WHERE ([Measures].[Internet Sales Amount])
In MDX studio I have got these counters for the Excel 2007 MDX on a cold cache.
Time              : 3 min 26 sec 540 ms
Calc covers       : 182159
Cells calculated  : 198687
Sonar subcubes    : 3675
SE queries        : 182117
Cache hits        : 185757
Cache misses      : 18
Cache inserts     : 11
Cache lookups     : 185775
Memory Usage KB   : 63936
And for the warm cache I have got these counters.
Time              : 124 ms
Calc covers       : 48
Cells calculated  : 140
Sonar subcubes    : 8
SE queries        : 8
Cache hits        : 8
Cache misses      : 0
Cache inserts     : 0
Cache lookups     : 8
Memory Usage KB   : 0
When we compare ProClarity and Excel 2007 we can hardly see any differences in performance. Some counters are a little bit higher in Excel 2007 but this can depend on that I have the grand totals active in Excel but not in ProClarity.
Now to the second scenario, combining the leaf levels in the customer and product dimensions.
The result look like this in ProClarity.
The MDX generated by ProClarity looks like this.
SELECT { [Date].[Calendar].[Calendar Year].&[2003] } ON COLUMNS ,
NON EMPTY { { { DESCENDANTS( [Customer].[Customer Geography].[All Customers], [Customer].[Customer Geography].[Customer] ) } *
 { DESCENDANTS( [Product].[Product Categories].[All Products], [Product].[Product Categories].[Product] ) } } } ON ROWS 
FROM [Adventure Works]
WHERE ( [Measures].[Internet Sales Amount] )
The counters for the cold cache
Time              : 3 min 28 sec 296 ms
Calc covers       : 182114
Cells calculated  : 222671
Sonar subcubes    : 3665
SE queries        : 182110
Cache hits        : 185750
Cache misses      : 18
Cache inserts     : 11
Cache lookups     : 185768
Memory Usage KB   : 53696
And the counters for the warm cache.
Time              : 1 sec 781 ms
Calc covers       : 3
Cells calculated  : 24124
Sonar subcubes    : 1
SE queries        : 1
Cache hits        : 1
Cache misses      : 0
Cache inserts     : 0
Cache lookups     : 1
Memory Usage KB   : 3328
The Excel 2007 generated for "the query from hell" scenario is not possible to show here because it is a very large list. A fragment of that code is possible to show.
SELECT NON EMPTY Hierarchize({DrilldownLevel({[Date].[Calendar].[All Periods]})}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS ,
NON EMPTY CrossJoin(Hierarchize(DrilldownMember({{DrilldownMember({{DrilldownMember({{DrilldownMember({{DrilldownLevel({[Customer].[Customer Geography].[All Customers]})}},
{[Customer].[Customer Geography].[Country].&[Australia]})}}, {[Customer].[Customer Geography].[State-Province].&[NSW]&[AU], 
FROM (SELECT ({[Date].[Calendar].[Calendar Year].&[2003]}) ON COLUMNS  FROM [Adventure Works]    –This is the subcube statement at the end
Performance counters on a cold cache are these.
Time              : 3 min 27 sec 375 ms
Calc covers       : 182399
Cells calculated  : 224175
Sonar subcubes    : 3739
SE queries        : 182158
Cache hits        : 185842
Cache misses      : 18
Cache inserts     : 11
Cache lookups     : 185860
Memory Usage KB   : 62656
And on a warm cache they are these.
Time              : 3 sec 93 ms
Calc covers       : 288
Cells calculated  : 25628
Sonar subcubes    : 52
SE queries        : 48
Cache hits        : 92
Cache misses      : 0
Cache inserts     : 0
Cache lookups     : 92
Memory Usage KB   : 9216
On a warm cache the response time is three times higher for Excel 2007  than for ProClarity, but we are talking about a few seconds. On a cold cache responsetime are the same for both clients, like the number of cells calculated and SE queries.
Summary: For these two query scenarios the performance of the MDX generated by the two clients are almost identical, except for the difference for the cold cache scenario for the "query from hell". It i more or less what you should expect. 
The next step is to test how the two clients handle calculated measures and calculated members.

Posted in OLAP klients | 2 Comments »

What’s to learn more about during the next two weeks

Posted by thomasivarssonmalmo on May 23, 2007

I will be the presenter of MS-BI news for some customers in the beginning of june. Our plans is to talk about and demo features in:
  • Excel 2007
  • Excel 2007 Services(part of Sharepoint 2007 Server)
  • Excel 2007 data mining
  • Performance Point Analytics
  • Performance Point Planning
  • Report Center in Sharepoint 2007 Server
  • Integration of a Strategy map with a balanced scorecard in Performance Point.

Quite a lot of servers and client features to have an opinion about. Up until know I have spent most time on Excel 2007 as an SSAS2005 client and most of the features my clients will ask for are already there. My present ProClarity customers will see some new good features not available in ProClarity like having many SSAS2005 tables in the same spreadsheet.  A lot of others will miss a lot of better features that are not a part of Excel 2007(better dimension tool, the performance map and the decomposition tree).

Most of the time however is spent making all those server applications:

  • SQL Server 2005
  • SSAS2005
  • SSRS2005
  • SSIS2005
  • Sharepoint 2007 Server
  • Performance Point Server

Working on my laptop. This far I have realized that the SSRS2005 Sharepoint 2007 integration(You run SSRS2005 inside of Sharepoint 2007 and not in report manager/report server) have a problem with Sharepoint(that will not work). Right now Sharepoint 2007 server works together with Performance Point CTP 2 on my laptop and it seems like a miracle.

I think I spend to much time on installation issues than on learning more about something useful like MDX.

Chris Webb have published some interesting posts on trend analysis with MDX( and he have used some statistics like ‘Linear regression’. Great information.  I will try his examples.

Trends can also be expressed in alternative ways and I have an example of what a client of mine requested. Not linear regression but accumulated rolling 12 months on each month. In a chart you will present each month as a bar but the rolling accumulated 12 months as a line. This will require two y-axis scales in a chart but that can be solved in ProClarit and Excel 2007, but not in SSRS2005(only one y-axis in a graph if you do not buy the Dundas add ins).

I will write a post(my first MDX) about this soon and another one on finding the last time member with data in a cube(simple business problem). I have also written down most of the MDX I have used during my career in a classification system. It looks good on a piece of paper but how can you publish that on the webb. I thinking about writing som posts called "MDX for everyone".

Let bee careful out there in the BI-world!

Posted in OLAP klients | Leave a Comment »