BI Solutions Using SSAS Tabular Model Succinctly

174 Pages·2014·10.629 MB·English

by  Parikshit Savjani

By
Parikshit Savjani
Foreword by Daniel Jebaraj

2
Copyright © 2014 by Syncfusion Inc.
2501 Aerial Center Parkway
Suite 200
Morrisville, NC 27560
USA
All rights reserved.

I
mportant licensing information. Please read.
This book is available for free download from www.syncfusion.com on completion of a registration form.
If you obtained this book from any other source, please register and download a free copy from
www.syncfusion.com.
This book is licensed for reading only if obtained from www.syncfusion.com.
This book is licensed strictly for personal or educational use.
Redistribution in any form is prohibited.
The authors and copyright holders provide absolutely no warranty for any information provided.
The authors and copyright holders shall not be liable for any claim, damages, or any other liability arising
from, out of, or in connection with the information in this book.
Please do not use this book if the listed terms are unacceptable.
Use shall constitute acceptance of the terms listed.
SYNCFUSION, SUCCINCTLY, DELIVER INNOVATION WITH EASE, ESSENTIAL, and .NET ESSENTIALS are the
registered trademarks of Syncfusion, Inc.

Technical Reviewer: Ramanathan Iyer
Copy Editor: Courtney Wright
Acquisitions Coordinator: Hillary Bowling, marketing coordinator, Syncfusion, Inc.
Proofreader: Graham High, content producer, Syncfusion, Inc.

3
Table of Contents
The Story behind the Succinctly Series of Books .................................................................................. 7
About the Author ......................................................................................................................................... 9
Dedication .................................................................................................................................................. 10
Chapter 1 Introduction to the Microsoft BI Stack ................................................................................. 11
What is business intelligence? ............................................................................................................... 11
Understanding the Microsoft business intelligence stack ...................................................................... 14
What’s new in the SQL Server 2012 business intelligence stack? ........................................................ 16
Choosing the right Microsoft analytics tool ............................................................................................ 18
Multidimensional approach versus tabular approach ............................................................................ 19
Choosing the right Microsoft BI reporting tool........................................................................................ 22
Developing an MSBI solution ................................................................................................................. 24
Summary ................................................................................................................................................ 25
Chapter 2 Developing a Data Model with a SSAS Tabular Instance ................................................... 26
Scenario ................................................................................................................................................. 26
Getting started with an Analysis Services tabular project ...................................................................... 27
Import data to the tabular model ............................................................................................................ 30
Modifying or deleting an imported table ................................................................................................. 40
Modifying or deleting a column in the table ........................................................................................... 40
Defining relationships ............................................................................................................................. 44
Defining hierarchies ............................................................................................................................... 47
Defining calculated columns .................................................................................................................. 49
Defining calculated measures ................................................................................................................ 50
Defining KPIs ......................................................................................................................................... 52
Filtering the data model ......................................................................................................................... 55

4
Sorting the data model ........................................................................................................................... 57
Summary ................................................................................................................................................ 60
Chapter 3 Learning DAX .......................................................................................................................... 61
DAX syntax ............................................................................................................................................ 61
DAX operators ....................................................................................................................................... 62
DAX data types ...................................................................................................................................... 63
Evaluation context .................................................................................................................................. 64
DAX functions ........................................................................................................................................ 66
Aggregation functions .......................................................................................................................... 67
Date and time functions ....................................................................................................................... 69
Filter functions ...................................................................................................................................... 71
Information functions ........................................................................................................................... 75
Logical functions .................................................................................................................................. 81
Mathematical functions ........................................................................................................................ 82
Statistical functions .............................................................................................................................. 84
Text functions ....................................................................................................................................... 87
Time intelligence functions .................................................................................................................. 89
DAX as a query language ...................................................................................................................... 96
Summary .............................................................................................................................................. 103
Chapter 4 Preparing the Data Model for Reporting and Deployment ............................................... 104
Hiding undesired columns and tables from the data model ................................................................. 104
Setting the Default Field Set and Table Behavior properties ............................................................... 106
Setting the Data Category property for columns ................................................................................. 109
Setting the Format property for measures ........................................................................................... 110
Setting the Summarize property for columns....................................................................................... 111
Adding descriptions for columns, tables, and measures ..................................................................... 112
Defining perspectives ........................................................................................................................... 114

5
Defining roles and security................................................................................................................... 117
Dynamic security .................................................................................................................................. 123
Defining partitions ................................................................................................................................ 124
Deploying the data model .................................................................................................................... 128
Post-deployment tasks ......................................................................................................................... 133
Summary .............................................................................................................................................. 134
Chapter 5 Exploring the Data Model with Power View ....................................................................... 135
Creating a connection to the data model in Excel 2013 ...................................................................... 135
Power View visualization ..................................................................................................................... 139
Visualizing data with tables in Power View ........................................................................................ 139
Visualizing data using matrix in Power View ..................................................................................... 142
Visualizing data with cards in Power View ........................................................................................ 145
Visualizing data using charts in Power View ..................................................................................... 148
Visualizing data using maps in Power View reports .......................................................................... 159
Filtering and slicing in Power View reports .......................................................................................... 161
Interactive filtering and highlighting with chart visualization .............................................................. 161
Filters ................................................................................................................................................. 163
Slicers ................................................................................................................................................ 166
Designing a dashboard in Power View ................................................................................................ 168
Summary .............................................................................................................................................. 174

6
The Story behind the Succinctly Series
of Books
Daniel Jebaraj, Vice President
Syncfusion, Inc.
S
taying on the cutting edge
As many of you may know, Syncfusion is a provider of software components for the
Microsoft platform. This puts us in the exciting but challenging position of always
being on the cutting edge.
Whenever platforms or tools are shipping out of Microsoft, which seems to be about
every other week these days, we have to educate ourselves, quickly.
Information is plentiful but harder to digest
In reality, this translates into a lot of book orders, blog searches, and Twitter scans.
While more information is becoming available on the Internet and more and more books are
being published, even on topics that are relatively new, one aspect that continues to inhibit us is
the inability to find concise technology overview books.
We are usually faced with two options: read several 500+ page books or scour the web for
relevant blog posts and other articles. Just as everyone else who has a job to do and customers
to serve, we find this quite frustrating.
The Succinctly series
This frustration translated into a deep desire to produce a series of concise technical books that
would be targeted at developers working on the Microsoft platform.
We firmly believe, given the background knowledge such developers have, that most topics can
be translated into books that are between 50 and 100 pages.
This is exactly what we resolved to accomplish with the Succinctly series. Isn’t everything
wonderful born out of a deep desire to change things for the better?
The best authors, the best content
Each author was carefully chosen from a pool of talented experts who shared our vision. The
book you now hold in your hands, and the others available in this series, are a result of the
authors’ tireless work. You will find original content that is guaranteed to get you up and running
in about the time it takes to drink a few cups of coffee.

7
Free forever
Syncfusion will be working to produce books on several topics. The books will always be free.
Any updates we publish will also be free.
Free? What is the catch?
There is no catch here. Syncfusion has a vested interest in this effort.
As a component vendor, our unique claim has always been that we offer deeper and broader
frameworks than anyone else on the market. Developer education greatly helps us market and
sell against competing vendors who promise to “enable AJAX support with one click,” or “turn
the moon to cheese!”
Let us know what you think
If you have any topics of interest, thoughts, or feedback, please feel free to send them to us at
[email protected].
We sincerely hope you enjoy reading this book and that it helps you better understand the topic
of study. Thank you for reading.

Please follow us on Twitter and “Like” us on Facebook to help us spread the
word about the Succinctly series!

8
About the Author

Parikshit Savjani is a Microsoft Certified Solution Expert and Microsoft Certified Trainer working
as a Premier Field Engineer with Microsoft specializing in SQL Server and business intelligence
(SSAS, SSIS, and SSRS). His role involves consulting, educating, mentoring, and supporting
the premier customers of Microsoft. He has more than six years of experience with Microsoft,
during which he has authored and developed a number of intellectual properties (IPs) in the
SQL and business intelligence space.
While supporting and consulting for premier customers of Microsoft, he has gained experience
in working in varied complex environments, understanding common customer bottlenecks, and
how to overcome them.
In this book he intends to educate BI professionals (architects, developers, and business users)
on how to best utilize the new tabular model to design a tabular model cube and use it for data
exploration and analytics using powerful visualization in Power View.
He contributes to the community as well by blogging at sqlserverfaq.net and MSDN Blogs, and
by delivering community sessions in SQL Virtual PASS and SQL Server Days in India.

9
Dedication
I would like to dedicate this book to my wife for her unconditional love and support to inspire me
to write this book. This book is also dedicated to my parents for their blessings. They have
worked hard to make me capable enough to be what I am today. I would also like to take this
opportunity to thank Microsoft for teaching me strong values and helping me realize my true
potential. Last but not least, I would like to thank Syncfusion for giving me this opportunity to
write a book to share my expertise on the product.

10