Excel 2013 PowerPivot in Action Let us see how Excel and Self-Service BI can boost your reports Alberto Ferrari Senior Consultant SQLBI.COM

Global Sponsor:

Who’s Speaking? BI Expert and Consultant Founder of www.sqlbi.com    

Problem Solving Complex Project Assistance DataWarehouse Assesments and Development Courses, Trainings and Workshops

Book Writer Microsoft Business Intelligence Partner SSAS Maestro – MVP – MCP

a brand of

Agenda Want to learn DAX?  Sorry… one hour is not enough  Really, believe me!

Instead, we’ll see what benefits DAX brings to the BI world Seeing DAX in Action! Excel 2013 new features for Self-Service BI:    

PowerPivot for Excel 2013 The DAX Language Power View Reports PowerPivot for SharePoint Integration

The Scenario AdventureWorks  Fictious company  We sell bikes all around the world

We already have a data warehouse    

Now we need to perform analysis on data Build a lot of reports Share them with the colleagues Not surprisingly, we use Excel 2013

The Data Warehouse Dim Date

Dim Geography

Dim Customers

Sales

Dim Categories

Dim Products

Our data warehouse is a simple and standard one, after all we will build everything in an hour…

The project in good old style Start a Business Intelligence Project    

Find a good BI professional Learn something about data modeling Create the solution Design dimensions and measure groups

We are not going to demo this  Just think it means weeks of work  And tons of money…  Along with some rigidity in further development

The Self-Service BI way Self-Service BI means    

Load data in Excel Build a data model by yourself Focus on the result Write your own DAX formulas

Advantages  Optimal time-to-market  No need to explain business rules to BI professionals

Requirements  At least Excel 2010 and PowerPivot; 2013 is better  Some data modeling and DAX skills

Let’s Prototype in Excel

I Need to Count the Customers… You want to count the customers? DimCustomer is a dimension!!!    

If only you told me that before… Well, don’t worry, it can be done I will create a new measure group And setup a fact-relationship with customers

Not a big issue, just a couple of hours

Let’s do it with Excel

I Need Just Another Count… You want to count the buying customers? That’s a DISTINCT COUNT over the fact table!  Well, yes, it can be done  It’s not easy, although…    

I will create a new DISTINCT measure group Then I will partition the fact table Maybe review the clustered index of the fact table… Otherwise it will be slow...

You thought it was easy? It is not…

Let’s do it with Excel

Oh, well… while you are here… Now you want to count the NEW customers??? What do you mean by that?  Customers who never bought anything  And are buying something now

Not easy at all…    

Need to review the data model Add snapshot fact tables, computed during ETL Write some complex MDX And fine-tune performance

Let’s discover the power of DAX

I Want to Perform Price-Range Analysis… Analysis by Price Range?  Do you mean Banding?  Freely configurable?

That need to be carefully planned  Need to compute the range during ETL  Update the DWH structure  Ops… it’s the fact table, that means planning the work

Updating the banding table  Need to write some code to update the table  That means building a user-interface

Let’s do it with DAX

ABC Analysis Clustering technique Helps detecting your core business Classification based on Sales / Revenues Class Percentage A (few products) 70% B (some products) 20% C (many products) 10% GRAND TOTAL 100%

ABC Analysis Usage Typically, you find Class A: few products, your core business Class B: some products Class C: a lot of products, less important ones

ABC Analysis with Excel

Let us mix DAX and Excel

Time to play with Power View Searching for insights in data Is there a correlation between  Product Cost  Sales Amount  Gross Margin

I would like to look inside my data in a graphical way At the end, I will produce the report

Power View in Excel 2013

I want a map chart!!! I’d like to    

build a report with a map Pinpoint all the sales as coloured circles Not very useful, but looks damn good! I have seen this on a friend’s reporting system!

Wow… that map looks cool! I need latitude and longitude    

Maybe a geography column in the DWH? Where do I start learning about it? And how do I find latitude of Rome? Not speaking about longitude…

Time to google for a solution    

I need a charting component Write some code to find coordinates And finally mix everything together Not an easy task, indeed…

Let’s do it with Power View

Nice for Self-Service, can I share this? I need to share the report with colleagues I want them to  Look at the report every day  Improve it by adding more intelligence  Use it as the basis for new analysis

They need to access from outside of the company This is what «share» means, after all

Time to share with SharePoint

Self-Service BI and the DAX language Simple Relational Data Model  Tables  Relationships

DAX for Calculated Columns and Fields  Complex calculations stored in the data model

Incredible Speed  No need to author custom data models  Simply leverage the tremendous speed of DAX

Simple Development  Excel is the only tool you need  And some DAX and modeling skills, of course

What Next? Learn DAX  Seriously, learn it!  It is not easy, it takes time, but it is worth it!  Yes… here I have a conflict of interests 

PowerPivot, DAX and Self-Service BI are not  A solution to all the problems  The only way to the future  The only available solution

But the modeling is  Faster, Simpler, Cheaper  In the hands of users (ok… of Power Users)

Questions?

Global Sponsor:

Thank You for Attending

Global Sponsor: