Thomas.I Microsoft BI & Analytics

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

Archive for the ‘Excel 2013’ Category

My BI Year 2013

Posted by thomasivarssonmalmo on January 1, 2014

I had too little time to blog as much as I wanted. The one about SSAS evaluation nodes was the one that took me a lot of time to write and it should have a second part with more practical stuff but there was simply no time for that in 2013.

So what happened then? I can mention the word certification.  I took both the 70-463, implementing a data warehouse with SQL Server 2012, and the 70-461, querying MS SQL Server 2012 and I am currently preparing for the 70-462, administering MS SQL Server 2012 databases. The two last ones, from my point of view as a BI consultant, are a waste of time. I am very happy to have passed the 70-463, because it is very useful in my daily work. Parts of the TSQL certification are useful, like 30-40 percent but I would like to see a more BI-focused database engine part with admin and TSQL parts in one exam. Right know both exams are too developer and DBA focused. The goal is to get the MCSA title, since it is mandatory in the company that I work for. It will not make me better as a BI-consultant since I never do cluster installations, Always On installation or set up replication of the database engine. Most of my customers either has outsourced maintenance of hardware and software or would never let me play with their IT-infrastructure. I have colleagues that have these skills and do this work daily.

The parts of BI that have have spent most of my time on, the last ten years, like Reporting Services and BISM MD/Tabular come in the second certification step, to be a “MS Certified Solutions Expert.

Now to the other parts. The year was mostly spent on the ETL or ELT part of BI. I have been involved in name standard document for BI like clening up that the same term applies to different objects or different terms apply to the same object. I was also invloved in discussions with customers about ETL or ELT strategies.

I attended a 5 day long course on the new PDW V2 and that hardware and software solution really can compete on the high data volume scenarios of BI. Do not think about it as only a part of the BIG Data scenarios because it can integrate well with SQL Server environments without the BIG data part. My personal opinion is why you should invest in more than 4 socket CPU system because on the declining return on adding more CPU power. This also questions the Fast Track part of MSBI offerings since PDW V2 gives much better value for money.

I also worked with a POC for a customer that included SQL Server 2012, BISM Tabular, SharePoint 2013 and Excel 2013. The fun part was to load a 340 million record fact table in to  a dedicated tabular server and see that it worked very well and that compression worked to shrink data to 15 percent of the original size.

I met a very smart customer that maintain their own business catalog with product and customer definitions outside of their ERP system. When they upgrade their ERP system they use this business catalog with cleaned product- and customer keys. That is what I call true master data management.

Slowly the interst for more self service is riising here in Sweden.  Since Power Pivot requires quite capable hardware like 64 bit laptops with 8 GB RAM and SSD and Office 2013 that will be an investment for most organizations that requires approval since most laptops are still 32 bit with 2-4 GB RAM. On top of that a SharePoint infrastructure is also needed.  Still the idea that IT provides datasets to users that then use Excel 2013 with Power Pivot and perhaps Power Query is attractive since it will reduce pressure on IT to help with data mash up.

Posted in BI Architecture, BISM TAB & MD, Excel 2013 | Tagged: | 1 Comment »

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 »

Ancient Monument Spotting with Excel 2013 Data Explorer and Power View

Posted by thomasivarssonmalmo on April 14, 2013

While I am not working with BI or learning new technical stuff I read about archeology in Scandinavia and especially about what can be found in the region of Sweden where I live. One idea of mine is to be able to compare differnt types of acheological findings from different time periods and see if they appear on the same geographical spot or not. Can Excel 2013 help me with this? Yes it is possible with the help of Excel Data Explorer, currently in beta and PowerPivot and Power View.

First I need to show you the geographical spot so that you can relate to the area. I have highlighted one type of ancient monumnets in the Bing map that also shows the final result after this exercise. It is from Trelleborg municipality in south of Sweden since I have found a really good dataset from that part with a list of ancient monuments(stone age, bronze age and iron age). Most of these monuments are graves and burial mounds.


So where are we

The dataset can be found here on Wikipedia:

Below you can see how this Wiki-page looks like. Name is the unique registered number for each ancient monument. “Lämningstyp” is the classification like rune stones, burial mounds. “Historisk Indelning” is the actual geographic spot of the monument. “Läge” is the latitude and longitude of the monument and the key to map this information to the Bing Map in Power View.

The Data source

I started with launching Excel 2013 with the Data Explorer preview and selected from web as the data source. I simply added the url above and Data Explorer started the import. As you can see I got several datasets for different sub areas in Trelleborg. Here is an issue I have not solved yet and that is if I am able to import all data sets with one query by setting a parameter. With my approach I am able to show other steps in the cleaning and consolidation process with Data Explorer.


First Query

The cleaning process is straight forward. I have right clicked the columns I do not need and selected hide. I have also changed the data type of the Id-nr, a not important column, to text with the same righ click and quick menu. Finally I have added an Index column not for any specific reason that being able to run a count aggregation on that column. The final query result looks like this with the steps shown to the right.

cleaning steps

I have created one query for each different sub area within Trelleborg. With all queries defined I have used the Append button to add the sub areas to the same result set.


The appending process

This Append process is not the best way to run a union over all queries. It is possible to do this much quicker by changing the append query slightly. You simply add the query names to the function like you can see below.


Extend the query


With this work done it is time to load the data into PowerPivot. In Excel 2013 this is done transparently and since I have only one single table there is no question about joins.



After that I started Power View in Excel 2013(Insert)  the data model will appear. I select maps as the graphical tool in Power View. The selections for my map are the following. I need a measure in the size to see anything. I simply run a count on Lämningstyp(=classification of monument), and in Location I put the Latitude and Longitude data field. I added lämningstyp a second time for the color of the circles in Power View.


Selections for the map

The result look like this with two “Lämningstyp” selected in a filter. Gravfält is a burial field and Hög is a burial mound, usually from the bronze age. All sub areas are not loaded into the model so there are more monumnets than you see in the map.

Resulting Map

Below you also see the filter section.

The filter section


So what is the point of doing this? The is an available database together with maps of all ancient monuments in Sweden but without the flexibility of the Excel 2013 solution I have shown here. The fact that I can add colors to different groups or classification of monuments in Power View is very useful. It is possible to see different clusters of classifications on the same geographical spot or near by. I can also quickly select and unselect categories of monuments.

Posted in Excel 2013, Power View, PowerPivot and DAX | 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 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)


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 »