I’ve just finished a series of four articles for SQL Server Pro Magazine, along with sample projects and hands-on exercises. The series will take you through SSAS Tabular model design from start to finish, using the Adventure Works sample data in SQL Server 2012 or 2014. Here are links to all four articles followed by an excerpt from each.
Part 1 – Getting Started with SSAS Tabular
Part 2 – Easy DAX – Getting Started with Data Analysis Expressions
Part 3 – Tabular Model Administration
Part 4 – Deep Dive DAX – Solving Complex Business Problems with Data Analysis Expressions
Download the sample projects here: Part 1, Part 2, Part 3, Part 4
Starting Your Modeling Career with Analysis Services Tabular Models Part 1
Four-part series
This is the first of a four-part series about getting started with Tabular model design using SQL Server Analysis Services in SQL Server 2012 and 2014. You will learn the basics from start to finish and build a complete solution. A sample project is provided for for each stage of the solution so you can follow-along with each article.
Article 1 excerpt:
Getting Started with SSAS Tabular
Microsoft’s introduction of the xVelocity in-memory analytics engine in 2010 is a significant game-changer for business intelligence and data management. The availability of this impressive technology and its future application in new product features will change the way you store and consume data, and it’s at the heart of SSAS Tabular. In this first in a series of four articles about designing and using Tabular models, you will experience how to create a model and analyze data. In the articles that follow, we will add more sophisticated features to deliver production-scale solutions.
Why Choose Tabular?
On a server equipped with sufficient memory and CPU horsepower, Tabular SSAS can be very fast. xVelocity, also known as VertiPaq, is the engine behind Tabular and Power Pivot. In simple terms, it applies some foundational data principles elegantly to achieve high performance. If you work with SQL Server databases, you know that table lookups are much faster when the columns used in a query are indexed. Tabular take this principle to the next level. The xVelocity column store essentially creates a separate index for every column and then compresses the data, eliminating redundant and sparse values. Unlike other compression methods, the storage for each column is unaffected by adjacent columns. For numeric, date and short text values; this approach can drastically reduce the size of the stored data. All of the compressed data is loaded into memory so queries incur no disk IO cost and return results quickly.
In the sixteen year history of multidimensional Analysis Services, many features and capabilities were added to the platform to address some specific business needs and “newer” is not always “better”. SSAS Tabular is not designed to be a comprehensive replacement for multidimensional Analysis Services. It is, however, a streamlined platform for analytic reporting and data exploration. Tabular is a ground-up re-engineered platform with a lot of solid business value today and even more promise for the future. Think if it as a Formula One race car without cup holders and seat warmers; just designed to go really fast. A number of publications and resources are at your disposal to compare both options and determine which best meets your requirements. I encourage you to take Tabular for a test drive.
Create a Tabular Project
The Developer, Business Intelligence and Enterprise editions of SQL Server 2012 and 2014 support SSAS Tabular projects. SQL Server Data Tools for BI installs with SQL Server 2012. For SQL Server 2014, install any edition of Visual Studio 2012 or 2013 and then install the SSDT-BI add-in. You should also have Excel 2010 or 2013 installed. To follow-along, you’ll need the AdventureWorksDW2012 or AdventureWorksDW2014 sample database. Links to these downloads and more background information are included in the Resources at the end of this article.
…
Article 2 excerpt:
Easy DAX: Getting Started with Data Analysis Expressions
The DAX language really serves three different purposes. Calculated columns perform row-by-row operations that are processed when data is loaded and the calculated results are persisted into the Tabular model. Measures are calculated at query time on a filtered set of rows. DAX is also a query language and can return an entire result set for reporting.
Continuing our Tabular modeling journey, we will use the project created in the first article, “Getting Started with SSAS Tabular.”
This article will give you a good start with DAX measures, and then we’ll resume and go deeper in the fourth article: “Deep Dive DAX – Solving Complex Business Problems with Data Analysis Expressions.”
Simple Measures
At its heart, DAX is a very simple expression language that, much like Excel, uses functions to perform operations on values and sets of data. The most fundamental functions are simple and easy to use, and then they get more complex and powerful. The first group of DAX functions you’ll learn are aggregates that roll up a range of values into a single result. These functions include SUM, AVERAGE, COUNT, DISTINCTCOUNT, MIN and MAX.
…
Article 3 excerpt:
Part 3 – Tabular Model Administration & Security
It’s common knowledge that SSAS Tabular and Power Pivot use the same in-memory engine and support the same essential features. Tabular supports a number of features which take it from desktop and small group scale to a truly enterprise class business solution. We explore two important enterprise features in the following exercise.
The first thing to understand is that a Tabular solution sits on the SQL Server Analysis Services foundation, which is a 14-year-old mature technology. When a project is deployed, an SSAS database is created. In SQL Server Management Studio (SSMS), you may notice the lack of objects that may be familiar to SSAS multidimensional users; like cubes and dimensions. The database simply contains tables (some of which may be “measure group” type tables and others serve as dimensions.)
To follow-along, use the solution you completed in Part 2 or download the Article 2 Completed Project.
Managing the Workspace Database
When opening the Model.BIM file in the SSDT model designer, a user-specific workspace database is automatically deployed to the server specified in the model properties. As a general rule, I don’t recommend the workspace database be on a production server. It should be on an instance where you have administrative rights.
Just a word about model stability, recovery and maintenance…
…
Article 4 excerpt:
Part 4 – Deep Dive DAX
The heart and soul of Tabular models is the DAX language. In part 2 of this series you learned to use some of the basic DAX functions like SUM and CALCULATE. You also learned about row and filter context and how functions like ALL() and ALLEXCEPT() can be used to override natural row context to change the scope of a calculation. In simple terms, when you use aggregate functions like SUM(), AVERAGE(), MIN() or MAX() with a numeric measure value, the calculation is applied to all the rows in the context of current filters and groups. This functionality is much like the behavior of a T-SQL query using a SUM() function and GROUP BY clause but that’s about where the similarities end.
The power of DAX goes well beyond emulating simple grouping and filtering logic. It can be used to apply advanced business logic to perform complex calculations that would be difficult to duplicate with the same performance in relational tools. For this final exercise, I’m going to queue some important topics you are likely to encounter, I’ll describe a reporting scenario and the calculations needed to address requirements and then I’ll step you through the exercise using the project we completed in the previous article.
User Interaction and Data Browsing
Analytical reporting is pretty simple in concept. Typically every measure is going to be grouped and/or filtered by something so when designing calculations, measures and KPIs; we must anticipate different ways users might interact with a model. The easiest way to think about this is in the context of an Excel Pivot Table. Connected to the model, a user could place any of the table attributes on rows or columns. They could place any measure or KPI in the intersecting data cells to perform calculations grouped by the rows or column attribute values; and they can apply filters and slicers.
Conditional Calculations
Some calculations only make sense when performed in a certain context. For example, if financial budgets are calculated for the end of each fiscal year and actual expenses are recorded for each month period, the logic for reporting on a daily, monthly or annual basis will be different and in some cases, may just not make any sense.
…
Read the entire series starting with Part 1 – Getting Started with SSAS Tabular
I have a situation (Define MEASURE MeasuresTable[Department] = ” & IIF(Parameters!Department_KEY.Value > 0, “LOOKUPVALUE(Department[DepartmentCode],Department[KEY], ” & Parameters!Department_KEY.Value & ” )”, “””All”””)) Here (ALL) returns all the departments. But I want to block one department from All at the same time we need to show this department by selecting individual department(in the sense of selecting by particular record). would you please provide any solution for this if you can..? It would be great help to me.