Save time analyzing volumes of data using a structured method to extract, model and create insights from your data
Key Features
Master in using Excel's Data Model and Power Pivot to connect and analyze multiple sources of data
Create key performance indicators for decision making using DAX and Cube Functions
Apply the knowledge to build an interactive dashboard that delivers key insights to your users
Book Description
Microsoft Excel BI solutions have gradually evolved over the years to give users flexibility and more control in analyzing large volumes of data from multiple sources without stepping out of Excel. From the introduction of Pivot Tables, the x-velocity engine, the Data Model, Power Query, and Power Pivot, users of Microsoft Excel are now equipped to get, transform, model, aggregate, and visualize data using the world's most popular spreadsheet
Data modeling with Microsoft Excel book offers a practical way to de-mystify the use and application of these tools using real-world examples and simple illustrations. The book will introduce to the world of Data Modeling in Excel, definitions, and best practices in data structuring (that is, normalized and denormalized data). It will then take a guided journey to make you understand the useful features of the Data Model and Power Pivot. You will learn the types of schemas, (snowflake and star), and create relationships within multiple tables. You will also understand how to create powerful and flexible measures using DAX and cube functions
By the end this book you will be able to apply the knowledge in a real-world case study and build an interactive dashboard for decision making
What you will learn
Implement the concept of Data Modelling within and beyond Excel
Get, transform, model, aggregate and visualize data with Power Query
Understand the best practices with data structuring in MS Excel
Build powerful measures using DAX from the Data Model
Generate flexible calculations using Cube functions
Design engaging Dashboards for your users
Who This Book Is For
This book is for Data Analysts looking for hands-on and effective methods to manage and analyze large volumes of data within Microsoft Excel using Power Pivot. If you are new or already conversant with Excel's Data Analytics tools, this book will give you further insights on how you can apply Excel's Power Pivot, Data Model, DAX Measures, and Cube Functions to save you time on routine data management tasks. This book can also be referred by students who are warming up to get into Data Science, the book offers a broad foundation for you to build on and transition into other tools like Power BI
Table of Contents
Getting Started with Data Modeling - Overview and Importance
Data Structuring for Data Models
Preparing your Data for the Data Model
Data Modeling with Power Pivot
Creating DAX Calculations from your Data Model
Creating Cube Functions from your Data Model
Communicating insights from your Data Model using Dashboards
Visualization Elements for your Dashboard
Choosing the right Design Themes
Publication and Deployment