Thomas.I Microsoft BI & Analytics

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

Posts Tagged ‘Excel 2013’

BIG MAC Index with Power Map and Power View

Posted by thomasivarssonmalmo on May 27, 2014

The Big Mac Index is an invention by the news magaziine “The Economist” to measure if a countrys currency is over or under valued. One way to do this is to use the prize of the Big Mac in USD as a comparative product since it is the same all over the world. Then take the price of the Big Mac in all local currencies and calculate that local price into the USD price and compare it to the USD-price. You can read more here.

So what I have done is that I have imported the data from The Economist, in several Excel spreadsheets and used Power Query to change data types and did some minor corrections. Only a few countries have continous data from 2000 to 2014 and a lot of the world countries are not part of the index.

Power Map

Above is the state in early 2014. Since US is the comparative country nothing will show up there. Over valued countries and currency are entirely dark blue. Undervalued countries and currencies have white edges. According to the map Norway, Swiitzerland, Sweden and Venezuela have the most overvalued currencies.

Here is a recording of the tour  where you can see the development over 14 years.

 

We can also have a look at Norway in Power View.

Power View

As I can say, from several stays in Norway it is a really expensive country and  beatyful. Norway stays at the top during most years.

Posted in Power Map, Power View | 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: http://sv.wikipedia.org/wiki/Lista_%C3%B6ver_fasta_fornminnen_i_Trelleborgs_kommun

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.

 

LoadToDataModel

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 »

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 »

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 »