The Analysts Perspective Advanced BI with Power Pivot

The Analyst's Perspective: Advanced BI with Power. Pivot DAX, Share. Point Dashboards, and SQL Data Mining Rafal Lukawiecki Strategic Consultant, Project Botticelli Ltd rafal@projectbotticelli. com 1 1

Objectives Introduce more advanced BI analytics from Microsoft Discuss using Share. Point 2010 as a BI Dashboard environment This seminar is based on a number of sources including a few dozen of Microsoft-owned presentations, used with permission. Thank you to Chris Dial, Tara Seppa, Aydin Gencler, Ivan Kosyakov, Bryan Bredehoeft, Marin Bezic, and Donald Farmer with his entire team for all the support. The information herein is for informational purposes only and represents the opinions and views of Project Botticelli and/or Rafal Lukawiecki. The material presented is not certain and may vary based on several factors. Microsoft makes no warranties, express, implied or statutory, as to the information in this presentation. Portions © 2010 Project Botticelli Ltd & entire material © 2010 Microsoft Corp. Some slides contain quotations from copyrighted materials by other authors, as individually attributed or as already covered by Microsoft Copyright ownerships. All rights reserved. Microsoft, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U. S. and/or other countries. The information herein is for informational purposes only and represents the current view of Project Botticelli Ltd as of the date of this presentation. Because Project Botticelli & Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft and Project Botticelli cannot guarantee the accuracy of any information provided after the date of this presentation. Project Botticelli makes no warranties, express, implied or statutory, as to the information in this presentation. E&OE. 2 2

Power. Pivot DAX 3

Data Analysis Expressions (DAX) Simple Excel-style formulas Define new fields in the Pivot. Table field list Enable Excel users to perform powerful data analysis using the skills they already have Has elements of MDX but does not replace MDX 4 4

Data Analysis Expressions (DAX) No notion of addressing individual cells or ranges DAX functions refer to columns in the data Sample DAX expression Means: = [First Name] &“ ”& [Last Name] String concatenation just like Excel SUM function takes a column name =SUM(Sales[Amount]) instead of a range of cells new RELATED function follows =RELATED (Product[Cost]) relationship between tables 5 5

DAX Aggregation Functions DAX implements aggregation functions from Excel including SUM, AVERAGE, MIN, MAX, COUNT, but instead of taking multiple arguments (a list of ranges, ) they take a reference to a column DAX also adds some new aggregation functions which aggregate any expression over the rows of a table 6 SUMX (Table, Expression) AVERAGEX (Table, Expression) COUNTAX (Table, Expression) MINX (Table, Expression) MAXX (Table, Expression) 6

More than 80 Excel Functions in DAX Date and Time DATEVALUE DAY EDATE EOMONTH HOUR MINUTE MONTH NOW SECOND TIMEVALUE TODAY WEEKNUM YEARFRAC 7 Information ISBLANK ISERROR ISLOGICAL ISNONTEXT ISNUMBER ISTEXT Logical AND IF IFERROR NOT OR FALSE TRUE Math and Trig Statistical ABS AVERAGE CEILING, ISO. CEILING AVERAGEA EXP COUNT FACT COUNTA FLOOR COUNTBLANK INT MAX LN MAXA LOG MIN LOG 10 MINA MOD MROUND PI POWER QUOTIENT RANDBETWEEN Text CONCATENATE EXACT FIND FIXED LEFT LEN LOWER MID REPLACE REPT RIGHT SEARCH SUBSTITUTE TRIM UPPER VALUE ROUNDDOWN ROUNDUP SIGN SQRT SUMSQ TRUNC 7

Example: Functions over a Time Period Total. MTD (Expression, Date_Column [, Set. Filter]) Total. QTD (Expression, Date_Column [, Set. Filter]) Total. YTD (Expression, Date_Column [, Set. Filter] [, YE_Date]) Opening. Balance. Month (Expression, Date_Column [, Set. Filter]) Opening. Balance. Quarter (Expression, Date_Column [, Set. Filter]) Opening. Balance. Year (Expression, Date_Column [, Set. Filter] [, YE_Date]) Closing. Balance. Month (Expression, Date_Column [, Set. Filter]) Closing. Balance. Quarter (Expression, Date_Column [, Set. Filter]) Closing. Balance. Year (Expression, Date_Column [, Set. Filter] [, YE_Date]) 8 8

1. Simplicity of DAX to Relate and Analyse Data 9

Share. Point 2010 BI Dashboards: Performance. Point Services 10

PPS in Share. Point 2010 Performance. Point Services in Share. Point 2010 improve over Performance. Point Server 2007: Share. Point does all security, management, backup, respository of dashboard Decomposition Tree KPI Details Scorecard drilldown, dynamic hierarchies, calculated KPIs Dynamic, up-to-date filters for time intelligence Share. Point Dashboard Designer is smoother Better accessibility Analytic charts with value filtering and server-based conditional formatting 11 11

Monitoring with PPS Business users can build performance dashboards easily 12 12

Analytics with PPS Integration of KPIs and analytics Multidimensional slice and dice, drill-across, drill-to-detail, root-cause analysis, prediction and centralized business logic definitions No coding 13 13

Reporting and Consolidation in PPS Combine operational and financial data into one report No need to reconsolidate manually Dynamic and standard reports Consistent live reports published from Excel to Reporting Services and Share. Point 14 14

Dashboard Designer Details pane Workspace Browser Workspace 15 15

Developing a Dashboard Choose a dashboard layout Assign elements to a dashboard zone Add filters Preview the dashboard Deploy to Share. Point 16 16

1. Building a Dashboard, Scorecard, and a KPI Using Share. Point Server Performance. Point Services 17

Visualising BI with Microsoft Visio and Share. Point 2010 18

Two Trends that Lead to… The Messy Diagram 19

Data Visualization Fault Analysis Tree Color By Value Text Callouts Status Indicators Data Bars 20

Data Visualization Manufacturing Specialized Shapes 21

Strategy Maps 22 Visualize PPS Scorecard data in context 22

Data Mining with SQL Server 23

What does Data Mining Do? Explores Your Data 24 Finds Patterns Performs Predictions 24

Server Mining Architecture BIDS Excel Visio SSMS Excel/Visio/SSRS/Your App OLE DB/ADOMD/XMLA App Data Deploy Analysis Services Server Mining Model Data Mining Algorithm 26 Data Source 26

Data Mining Techniques 28 Algorithm Description Decision Trees Finds the odds of an outcome based on values in a training set Association Rules Identifies relationships between cases Clustering Classifies cases into distinctive groups based on any attribute sets Naïve Bayes Clearly shows the differences in a particular variable for various data elements Sequence Clustering Groups or clusters data based on a sequence of previous events Time Series Analyzes and forecasts time-based data combining the powerof ARTXP (developed by Microsoft Research) for short-term predictionswith ARIMA (in SQL 2008) for long-term accuracy. Neural Nets Seeks to uncover non-intuitive relationships in data Linear Regression Determines the relationship between columns in order to predict an outcome Logistic Regression Determines the relationship between columns in order to evaluate the probability that a column will contain a specific state 28

1. Association Rules for Market Basket 2. 34 Analysis Automatic recommendation engine using DMX queries

Summary Advanced self-service analysis requires a rich expression language: DAX Team, and organisational BI dashboards and scorecards are easy to build using Share. Point 2010 Data Mining enables advanced pattern (correlation) discovery in your data 35 35

© 2010 Microsoft Corporation & Project Botticelli Ltd. All rights reserved. The information herein is for informational purposes only and represents the opinions and views of Project Botticelli and/or Rafal Lukawiecki. The material presented is not certain and may vary based on several factors. Microsoft makes no warranties, express, implied or statutory, as to the information in this presentation. Portions © 2010 Project Botticelli Ltd & entire material © 2010 Microsoft Corp. Some slides contain quotations from copyrighted materials by other authors, as individually attributed or as already covered by Microsoft Copyright ownerships. All rights reserved. Microsoft, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U. S. and/or other countries. The information herein is for informational purposes only and represents the current view of Project Botticelli Ltd as of the date of this presentation. Because Project Botticelli & Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft and Project Botticelli cannot guarantee the accuracy of any information provided after the date of this presentation. Project Botticelli makes no warranties, express, implied or statutory, as to the information in this presentation. E&OE. 36 36