Ancient Monument Spotting with Excel 2013 Data Explorer and Power View
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.
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.
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.
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.
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.
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.
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.
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.
Below you also see 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.
Multiselecting Dates in Excel 2013 on top of SSAS/BISM Multidimensional
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.
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.
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.
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.
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.
2012 in review
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
ISOWeeks in SSAS Revisited
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.
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.
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.
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.
I have created these hierarchie below in the date dimension.
I have also created the attribute relations for the hierarchies like this.
Finally it is time to add the fact table. In the cube structure tab it look like this.
And in the dimension usage tab it look like this.
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.
I a future post I will cover a many to many requirement in a date dimension with weeks.
Excel 2010 and Excel 2013 Slicer Settings
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.
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.
Estimated File Size in BISM Multidimensional(SQL Server 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.
I will right click on the Adventure Works DW 2008R2 database and select properties.
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.
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.
Power View in Excel 2013 Part Two: Maps
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.
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.
After you have pressed the map button you will see something like this on the canvas.
But we can extend this even more. Here is the design tool to the right where I have added CalendarYear to vertical multiplies.
The result is this map.
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.
Power View in Excel 2013 Part One: Pie Charts
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.
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.
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.
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!
Excel 2013 and BISM Multidimensional
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.
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
When you have saved this expression it will appear in the metadata pane to the right in the Excel dimension tool.
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.
When I drag the Customer.Country attribute to rows in the Pivot Table this new member will appear.
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.
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.
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.
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.
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.
The Data Warehouse Value Proposition
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
This is what I have in my mind trying to explain what I am doing and what the customer is paying for.
