NB: This post has 3 Excel-files (embedded in 1 zip), see ‘Downloads’ at bottom of page. To be able to open these files, you must have Excel 2013 with the Power-BI add-ins installed.
Recently I followed a very interesting course about Business intelligence (BI) with MS SQL Server 2012, Analysis Services 2012 and MS Excel 2013. Because this blog is about Excel, I´ll tell in this post something about Excel 2013 and it's BI-features.
The teacher of this course, who has a blog about BI, see:
said: “Microsoft wouldn’t be Microsoft without Excel”, meaning that Excel is probably Microsoft's most used product. And with Excel 2013, and it´s 'Power-BI' add-ins like PowerPivot, PowerQuery and PowerView, Microsoft wants to provide 'self-service business intelligence' (so for business-users), to increase even more the use of Excel.
To start, a definition of BI, from:
Business intelligence (BI) is a set of theories, methodologies, architectures, and technologies that transform raw data into meaningful and useful information for business purposes.
An example of BI with Excel is e.g. a spreadsheet which has all the sales-data of a big international company and then by applying pivot-tables and pitvot-charts, this data can be summarized and presented in a way the general salesmanager can see easily how the business is going, e.g. the top 10 of most sold products, or the sales per country.
* PART 1
In this part I´ll use the same database as in the previous post, see:
So first I link my Excel to the Excel-file with the Skating-database (C:\Temp\Skating_DB_XL.xlsx, see 'Downloads' in my previous post), by creating the connection, see fig.1.
fig.1: Connection (relational) database
Then I add the linked-tables in PowerPivot, into a 'tabular model', which is the data-source I'll use from now on to create pivot-tables etc, see fig.2-4.
fig.2: Connection PowerPivot
fig.3 PowerPivot datamodel
fig.4 PowerPivot datamodel: relations
2 Examples of DAX-formulas:
*1: Column: Category_MW (Man/Women) = LEFT(T_Race[Race];1), see fig.5. Result: M/W.
*2: Total distinct races = DISTINCT_Distance:=DISTINCTCOUNT([Distance]), see fig.6. Result: 6.
NB: for this total, we only look at distance, so disregarding gender, so M500 = W500 (race: men/women over distance 500 meter)
Leaving the PowerPivot-window, you can see in Excel 3 pivot-tables:
*1: Number of skaters per distance (race), with slicer (filter): Gender (Men/Women), see fig.7
*2: Number of skaters per distance (race) X Gender (Men/Women), see fig.8
*3: Medal-total (ranking 1,2,3: gold/silver/bronze) per skater and Gender (Men/Women), see fig.9.
fig.7: Pivot-table 1
fig.8: Pivot-table 2
fig.9: Pivot-table 3
Besides the pivot-tables, the Excel also has a report in PowerView, see sheet-1 and fig.10 for an example. This report has several elements:
* on the top-right, all tables and fields (including the calculated-fields like ‘distinct Distance’) of the database (from the PowerPivot-datamodel, see above)
* the tables in the report (‘Ranking per Skatername’ and ‘Team/Skater-name’ and ‘distinct Distance’)
* filters (e.g. ‘Gender’ and bar with 1 value per race ‘M1000, M10.000 etc’) which work on the tables, so they make the report ‘interactive’
In fig.10 you can see that:
- in race M1000 (Men 1000 meter) (see slicer), there were 4 (Dutch) skaters participating, and Groothuis won this race (table 1).
- because filter Gender = ’M' (Men), table 2 (Team/Skater-name) shows only the men (not women) in the teams, and the number of races (distinct distance) for men is 5.
fig.10 Report in PowerView
* PART 2
In this Excel you can see how in Excel 2013 with PowerView you can visualize geospatial data in a map.
I created a table in in Excel with the top 10 countries of the medal-ranking from Wintergames 2014, which I copied from:
and I added an extra column, 'Continent', and this table I imported in a 'tabular model' in PowerPivot, see fig.11.
fig.11: datamodel in PowerPivot
After that I enhanced this model by specifying the correct Data Category (in menu Advanced) for the 'Continent' and 'NOC' (Country) fields. And then when you return to Excel and want to create a report for this data in PowerView, you see that these geographic-columns are marked with a Globe-icon. The maps used by PowerView are those from MS Bing Maps, so you should be 'online' when you want to create a report with maps. The final result you can see in fig.12-13, which show in this case the number of golden medals won per continent and country, with a dot. So you can see easily (by the size of the dot) that in Europe the countries in the North did better then in the South or that a small country like the Netherlands did better then a big country like France.
For more info about the use of Excel and Maps, see e.g.:
fig.12: PowerView with Bing-Map
fig.13: PowerView with Bing-Map
* PART 3
With Excel 2013 and add-in PowerQuery, you have a lot of possibilities to connect to external data and the Excel in this part shows how you can import a table from the Web, in this case I used the 'Overall top goalscorers' table from:
fig.14 Website with topscorers-table to import in Excel
and for the result, see fig.15-16.
fig.15 topscorers-table import by PowerQuery
fig.16 topscorers-table import by PowerQuery
This post just showed some aspects of Excel 2013 and BI, for more, see e.g. :
Note 11-5-2014: Today I created a new post about Excel 2013 and PowerMap (another add-in of the Power-BI suite), with an example for 2014 FIFA World Cup Soccer, see: