by Paul te Braak, from Packt Publishing
on Amazon
Good technology books usually come in one of two forms. Some of the books on my shelves go deep technically, contain useful tidbits of code but read like the phonebook. Just a few are interesting and insightful. This book is a rare gem that does both. Paul te Braak is well-known in the Business Intelligence community for his expertise and contributions and he delivers a unique guide that starts with the basics and proceeds to cover all of the essentials with depth and practical examples to solve some challenging business problems.
You might expect a book that introduces fundamental concepts to gloss-over advanced topics and avoid complex applications. This book covers the essentials of data modeling and analysis with Excel and Power Pivot in language that is plain and easy to understand but it doesn’t stop with the basics. It covers practical and useful applications without insulting the reader’s intelligence. As an experienced data modeler, I found several useful techniques and new methods to use the tools and language. Paul’s coverage of practical techniques spans the spectrum of business applications and product features. This is a rare book that is not only a good tutorial with many hands-on examples that can be repeated by the reader but it’s also a great reference of useful techniques and code samples.
Highlights include:
- The integration of Excel features such as ranges, tables, pivot tables and pivot charts with the additional features of Power Pivot and Power View that extend and enhance these capabilities
- Examples and instructions are directed at Excel 2010 users and the author compares some the different features in Excel 2010 and Excel 2013.
- Fundamentals of the DAX calculation language
- Importing data as text, different date formats and implied data type columns
- Beyond the basics, a schema.ini file is used to define column data types
- Importing data from a database, working with database tables, views and queries, managing connections and challenges encountered running the table import wizard multiple times
- Data feeds using OData and using a Reporting Services report to provide a data feed
- Decisions a designer makes to enable the user’s experience when browsing a model. This includes sorting values, navigating hierarchies that enable drill-down interaction.
- DAX “X” functions (SUMX, MINX, etc.) to perform row-level aggregation
- Working with parent-child hierarchies using specialized DAX path functions.
- Advanced browsing features, adjusting pivot table options to optimize the user experience
- Building and using KPIs and using alternate table relationships
- Time calculations and date functions. This chapter covers running totals and totals to date
- Date part aggregate functions (MTD, YTD, etc.),
- Essential data math and comparisons
- LastYear and PriorPeriod functions, TotalYTD
- Manufacturing calendar, working with “445” dates
- Creating a dynamic relative time measure, using a shell dimension table
- Using DatesBetween to show the average value for the past 10 days
- Apply advanced modeling technique to bin, sort and rank values for reporting
- Expand concepts introduced in chapter 3, using the DAX “X” functions to perform row iteration in advanced financial applications
- Defining and working with many-to-many relationships. This is often no trivial task to completely understand many-to-many relationship requirements and to apply a working solution that provides the intended results
- Addressing inventory and stock-keeping challenges
- Conditional aggregation at different levels
- Budgeting and forecasting vs actuals
- Programming Excel to enhance the users experience
- Excel VBA event programming to respond to slicers
- Using cube functions
- Interacting with charts and slicers
- Building solutions for the enterprise
- Using the SSDS Tabular designer
- Migrating Power Pivot models to Tabular server solutions
- managing connections, implementing impersonation, managing security
- Using roles and perspectives
- Generating and using XMLA script
- Defining and implementing role-based, dynamic row filtering
- Performing currency conversion
- Managing and optimizing a Tabular solution
- Deployment scenarios
- Using SSDT to deploy and process models
- Using the SSAS Deployment Wizard
- Generating and using deployment scripts
- Creating and managing partitions
- Scheduling and executing processing tasks
- Utilizing DirectQuery for real-time data results
- Using Profiler to troubleshoot and optimizing a model
- Querying a model using DAX
- comparison of similar and different concepts in multidimensional and Tabular semantic models
- Query with MDX
- Query with DAX
- DAX tools and debugging techniques
- Using DAX query techniques to simulate SQL query operations
- Column aliases, joins, filters, deriving tables
- Samples and top ranked results
- Using Power View to present results and visualize data
- Essential design features
- creating a table report
- using a matrix to pivot results
- time and data filters
- advanced filters
- creating charts
- bar charts
- stacked charts
- cluster chart
- using tiles to navigate sectioned results
- using images
- managing tables with default field sets
- table behavior and cards
- data categories and visual behaviors
