7 May 2014

Excel 2013 and Business Intelligence

#14  Excel 2013 and Business Intelligence

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:

www.amby.net

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:

http://en.wikipedia.org/wiki/Business_intelligence

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

 Excel: Skating_XL_PP_Query10_XL13C_v2.xlsx

In this part I´ll use the same database as in the previous post, see:

http://worktimesheet2014.blogspot.com/2014/04/excel-and-relational-databases.html

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


With the Skating-database (3 tables: Race, Skater, Team) imported in the datamodel in PowerPivot, I now can use DAX-formulas (Data Analysis Expressions), which are like ‘normal’ Excel-formulas, except that they work on (rows and columns of) tables (not on cells). For more details about DAX, see e.g.:

http://office.microsoft.com/en-us/excel-help/data-analysis-expressions-dax-in-power-pivot-HA102836919.aspx

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)

fig.5 DAX-formula


fig.6 DAX-formula



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

Excel: Skating_PowerviewMap_v3.xlsx

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:

http://en.wikipedia.org/wiki/2014_Winter_Olympics_medal_table

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.:

http://www.sqljason.com/2012/07/creating-maps-in-excel-2013-using-power.html

fig.12: PowerView with Bing-Map

fig.13: PowerView with Bing-Map



* PART 3

Excel: WebDB_v2.xlsx

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:

http://en.wikipedia.org/wiki/UEFA_European_Football_Championship

see fig.14
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



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:

http://worktimesheet2014.blogspot.com.es/2014/05/excel-2013-powermap-and-world-cup-soccer.html

Downloads


at

1 comment: