Microsoft Excel 2013 · Building Data Models with PowerPivot by Ferrari, Alberto -- Read -- Imperial Library of Trantor

Index

Microsoft Excel 2013: Building Data Models with PowerPivot A Note Regarding Supplemental Files Introduction

Who this book is for Assumptions about you Organization of this book Conventions About the companion content Acknowledgments Support and feedback

Errata We Want to Hear from You Stay in Touch

1. Introduction to PowerPivot

Using a PivotTable on an Excel table Using PowerPivot in Microsoft Office 2013

Adding information to the Excel table Creating a data model with many tables Understanding relationships

Understanding the data model

Querying the data model

The PowerPivot add-In Using OLAP tools and converting to formulas Understanding PowerPivot for Excel 2013 Creating a Power View report

2. Using the unique features of PowerPivot

Loading data from external sources

Creating a PowerPivot PivotTable

Using the DAX language

Creating a calculated column Creating a calculated field Computing complex aggregations like Distinct Count

Refreshing the PowerPivot data model

3. Introducing DAX

Understanding DAX calculations

DAX syntax DAX data types DAX operators DAX values

Understanding calculated columns and fields

Calculated columns Calculated fields

Choosing between calculated columns and measures

Handling errors in DAX expressions

Conversion errors Arithmetical operations

Empty or missing values

Intercepting errors

Formatting DAX code Common DAX functions

Aggregate functions Logical functions Information functions Mathematical functions Text functions Conversion functions Date and time functions Relational functions

Using basic DAX functions

4. Understanding data models

Understanding the basics of data modeling

Producing a report without a data model Building a data model

More about relationships Understanding normalization and denormalization Denormalizing within SQL queries

The PowerPivot query designer When to denormalize tables

Understanding over-denormalization Understanding OLTP and data marts

Querying the OLTP database Data marts, facts, and dimensions Star schemas Which database is the best to query?

Using advanced relationships

5. Publishing to SharePoint

SharePoint 2013 and PowerPivot integration Licensing and setup Publishing a workbook to SharePoint Using the PowerPivot Gallery Connecting Excel to a SharePoint Excel data model Creating a Power View report Managing the PowerPivot data refresh

6. Loading data

Understanding data sources Loading from a database

Loading from a list of tables Loading relationships Selecting related tables Loading from a SQL query Loading from views

Opening existing connections Loading from Access Loading from SQL Server Analysis Services

Using the MDX editor Handling of keys in the OLAP cube Loading from a tabular database

Loading from SharePoint Using linked tables Loading from Excel files Loading from text files Loading from the Clipboard Loading from a report Loading from a data feed Loading from Windows Azure Marketplace

Suggest related data

Refreshing connections

7. Understanding evaluation contexts

Introduction to evaluation contexts

Understanding the row context

Testing your evaluation context understanding

Using SUM in a calculated column Using fields in a calculated field

Creating a row context with iterators Understanding FILTER, ALL, and context interactions Working with many tables

Row contexts and relationships Filter context and relationships Introducing VALUES Introducing ISFILTERED and ISCROSSFILTERED

Evaluation contexts recap Creating a parameter table

8. Understanding CALCULATE

Why is CALCULATE needed? CALCULATE examples

Filtering a single column Filtering with complex conditions

Using CALCULATE inside a row context Understanding circular dependencies CALCULATE rules Understanding ALLSELECTED

9. Using hierarchies

Understanding hierarchies

When to build hierarchies Building hierarchies Creating hierarchies on multiple tables Performing calculations using hierarchies

Using parent/child hierarchies

10. Using Power View

What is Power View? Power View basics

Using the Filters pane Decorating your report

Understanding table, matrix, and cards

Using the matrix visualization Using the card visualization Using a table as a slicer

Using charts

Using the line chart Using the pie chart Using the scatter chart

Using maps Understanding drill-down Using tiles Understanding multipliers Using Power View effectively

11. Shaping the reports

Key Performance Indicators (KPIs) Creating data models for Power View Understanding Power View metadata

Using Summarize By Using the default field set Using the Table Behavior dialog box

Defining sets Creating dynamic sets with MDX Using perspectives Understanding drill-through

12. Performing date calculations in DAX

Building a calendar table

Working with multiple calendar tables Calculating working days Computing the difference in working days

Aggregating and comparing over time

Year-to-Date (YTD), Quarter-to-Date (QTD), and Month-to-Date (MTD) Time intelligence with CALCULATE Computing periods from the prior year (PY) Computing the moving annual total Using other aggregation functions Computing difference over a previous year

Closing balance over time

Semiadditive measures OPENINGBALANCE and CLOSINGBALANCE functions Updating balances by using transactions

Computing moving averages

13. Using advanced DAX

Banding Ranking Using many-to-many relationships Computing new and returning customers Understanding KEEPFILTERS Implementing basket analysis Understanding the power of calculated columns: ABC analysis Handling currency conversion

14. Using DAX as a query language

Understanding EVALUATE Creating an Excel table with EVALUATE Using common functions in queries

Using FILTER Using CALCULATETABLE

Using ADDCOLUMNS

Using VALUES with ADDCOLUMNS

Using SUMMARIZE

Using the ROLLUP option

Linking back a DAX query Computing ABC analysis with a linked-back table Using CROSSJOIN Using GENERATE Querying with DAX Studio

15. Automating operations using VBA

Enabling the DEVELOPER tab of the ribbon Updating a linked-back DAX query through VBA Using the Model object Importing data into the data model using VBA Understanding data connections

16. Comparing Excel and SQL Server Analysis Services

Understanding the different versions of the engine Feature matrix Securing your data Programmability and flexibility Translations Database size Number of databases PowerPivot as a prototyping system

A. About the Authors Index About the Authors Copyright

← Prev
Back
Next →

← Prev
Back
Next →