SQLBI - Marco Russo : BISM

Workshop and Events in Europe next month #ssas #tabular #powerpivot

Marco Russo (SQLBI) — Thu, 21 Feb 2013 09:41:00 GMT

As I mentioned a couple of weeks ago, I and Alberto are starting to travel again, delivering SSAS Tabular Workshop and other free community events around Europe. This is the updated list of our next dates, including community events:

Online – SSAS Tabular Workshop on February 27-28, 2013 (17-24 CET)

This workshop is delivered online – you can attend wherever you are
Timeslot is late afternoon-evening for Europeans, more appropriate office time for US market

Aarhus, Denmark – SSAS Tabular Workshop on March 12-13, 2013 (9-17 CET)

Alberto Ferrari will be the teacher in these dates
You can still register here

Aarhus, Denmark – Excel 2013 PowerPivot in Action on March 12, 2013 (17:30-19:30 CET)

Free community event where Alberto will show the capabilities available in Excel 2013 using PowerPivot and Power View
More info here

Utrecht, The Netherlands – SSAS Tabular Workshop on March 25-26, 2013 (9-17 CET)

Marco Russo (me!) will be the teacher in these dates
You can register here (early bird expires on March 11, 2013)

Veenendaal, The Netherlands - Querying and Optimizing DAX on March 25, 2013 (18-21 CET)

Free community event where Marco Russo (yes, always me!) will show how to write and optimize queries in DAX
More info here and free registration here

Oslo, Norway – SSAS Tabular Workshop on April 8-9, 2013 (9-17 CET)

Alberto Ferrari will be the teacher in these dates
You can register here

Milan, Italy – PowerPivot Workshop Full on April 17-19, 2013 (9-17 CET)

Alberto will be the teacher in these dates
Be careful: this workshop will be delivered in Italian language – a good excuse to practice your Italian visiting Italy for work reasons!
You can register here

Other workshops will follow in May and June in Finland, UK, Belgium and Sweden – a complete list of Tabular Workshop here and PowerPivot Workshops here.

SQLBits XI in May 2013 – many reasons to attend! #sqlbits #dax #tabular

Marco Russo (SQLBI) — Wed, 20 Feb 2013 09:33:00 GMT

I and Alberto Ferrari will speak at SQLBits XI (Nottingham, UK – May 2-4, 2013) and there are many good reasons to join us there, especially if you are interested to DAX!

Here are our sessions:

Thursday, 02 May 2013 – Training Day - From 0 to DAX

The entire day is dedicated to learning DAX, starting from the syntax and going forward with more complex syntaxes for both expressions and queries in DAX.
Technical level: the goal is to be introductive, explaining the important concepts in DAX (such as filter context, row context, context transitions) and covering the usage of the most common DAX functions.
Target audience: Information Workers interested in exploiting the many interesting features of PowerPivot for Excel 2013 and BI developers who want to learn the DAX language.
In practice: if you started using DAX two years ago and created several projects in Tabular and/or PowerPivot with complex calculations, this is not the workshop for you (read about an Advanced DAX Workshop later in this post). But if you just started your first Tabular or PowerPivot projects and you are still wondering what CALCULATE is all about, then this is definitely the right training for you!

Friday, 03 May 2013 – 10:50am – Modern Data Warehousing Strategy

How to design a Data Warehouse in 2013? Should we change something considering the introduction of in-memory technologies such as xVelocity? Is Self-Service BI affecting the way we design and implement a Corporate Data Warehouse?
This is not a session about a specific feature of a product, but features and products have their effects on the data warehouse design. We’ll stop one hour trying to understand what changes in the big picture, affecting also our day-by-day job.

Friday, 03 May 2013 – 2:40pm – Inside xVelocity InMemory Engine (VertiPaq)

Do you use PowerPivot and/or Analysis Services Tabular? They use the same engine (xVelocity/VertiPaq) and you should know how it works.
This session will show how xVelocity/VertiPaq works, how it compresses and stores data and why it is so fast answering to your queries.
Why this is important? Because it is geeky! And because this knowledge will help you optimizing storage and DAX queries.

Saturday, 04 May 2013 – 10:50am – DAX Query Engine Internals

DAX is amazingly fast. But some query might be not fast enough.
Do you want to know how to optimize your DAX query? This session is for you.
Knowing the internals of xVelocity InMemory engine is just the first step (see Friday’s session). Then you need to apply this knowledge to DAX and understand why different DAX syntaxes for the same result might have different performance.
Understanding cross-filtering is a key to master DAX optimization. The more you know DAX, the more you will appreciate this session!

As I said before, the training day is an introductive course. If you already know DAX and want to improve your skills, there is a 3-day Advanced DAX Workshop I will teach in London on May 13-15, 2013. It could be also a good reason to visit London if you come from other countries!

Now that we are almost done with the PowerPivot for Excel 2013 book, we will write more articles – stay tuned!

xVelocity engines compared: VertiPaq vs ColumnStore #ssas #vertipaq #xvelocity #sql #tabular

Marco Russo (SQLBI) — Wed, 22 Aug 2012 11:56:00 GMT

During the last months I and Alberto worked in several projects using Analysis Services Tabular and we had to face real world issues, such as complex queries, large data volume, frequent data updates and so on. Sometime we faced the challenge of comparing Tabular performance with SQL Server. It seemed a non-sense, because even if the same core xVelocity technology is implemented in both products (SQL Server 2012 uses ColumnStore indexes, whereas Analysis Services 2012 uses VertiPaq), we initially assumed that the better optimization for the in-memory engine used by Analysis Services would have been always better than SQL Server.

However, we discovered several important things:

Processing time might be different and having data on SQL Server could make ColumnStore way faster for processing.
Partitioning in SQL Server might be much more effective for query performance than Analysis Services.
A single query can scale easily on more processor on SQL Server, whereas in Analysis Services the formula engine is single-threaded and could be a bottleneck for certain queries.
In case of a large workload with many concurrent users, storage engine cache in Analysis Services could be a big advantage over SQL Server, especially for scalability

As you can see, these considerations are not always obvious and you might be tempted to make other assumptions based on these information. Well, don’t do that. Before anything else, read the whitepaper VertiPaq vs ColumnStore Comparison written by Alberto Ferrari. Then, measure your workload. Finally, make some conclusion. But don’t make too many assumptions. You might be wrong, as we did at the beginning of this journey.

Finally in stock: Microsoft SQL Server 2012 Analysis Services: The BISM Tabular Model #ssas #tabular

Marco Russo (SQLBI) — Thu, 26 Jul 2012 10:32:00 GMT

It has been a long wait, but finally our book about Analysis Services Tabular is available!

Microsoft SQL Server 2012 Analysis Services: The BISM Tabular Model

Amazon is already shipping it in US and it should be available soon also in Europe. However, the Kindle edition is already available everywhere! If you like to order it on Amazon, here are the links for all the shops:

Amazon.com: hardcopy – kindle
Amazon.ca: hardcopy
Amazon.co.uk: hardcopy – kindle
Amazon.de: hardcopy – kindle
Amazon.es: hardcopy – kindle
Amazon.fr: hardcopy – kindle
Amazon.it: hardcopy – kindle

You can follow this link to take a look at the Table of Contents, at the Introduction and some content of other chapters. I suggest you to read the introduction and the "Who Should Read This Book” section before buying the book, so you will be sure you are getting the right one. Well, if you read this blog, you should be in the right target audience, but double check is not bad.

For your convenience, this is the list of the chapters:

Chapter 1 Introducing the Tabular Model
Chapter 2 Getting Started with the Tabular Model
Chapter 3 Loading Data Inside Tabular
Chapter 4 DAX Basics
Chapter 5 Understanding Evaluation Context
Chapter 6 Querying Tabular
Chapter 7 DAX Advanced
Chapter 8 Understanding Time Intelligence in DAX
Chapter 9 Understanding xVelocity and DirectQuery
Chapter 10 Building Hierarchies
Chapter 11 Data Modeling in Tabular
Chapter 12 Using Advanced Tabular Relationships
Chapter 13 The Tabular Presentation Layer
Chapter 14 Tabular and PowerPivot
Chapter 15 Security
Chapter 16 Interfacing with Tabular
Chapter 17 Tabular Deployment
Chapter 18 Optimizations and Monitoring
Appendix A DAX Functions Reference

We already received a good review but I hope to see many others – of course we all like the good ones, but we always look carefully at every comment so that we can improve the next one!

I’d like to give a big thank you to all the people who helped us reaching this goal. I know we set an ambitious target, trying to cover a brand new engine and development environment in a very deep way despite its infancy on the market. I think that this is just the first step in this new world, Tabular will evolve and we’ll gain more experience working with it on real word projects. But I hope that many BI developers will find it easier to start a new Tabular project with the guidance of this book. We have found that Tabular is good not only for “simple” models, but also for the complex ones, when Multidimensional faced some limitations in term of leaf-level calculation. I still don’t use Tabular for any new project, there are still many scenarios in which Multidimensional is a better choice, but don’t wait too much to start playing with DAX and Tabular. You might miss some real opportunities.

I hope you will enjoy the reading!

Watch @marcorus and @ferrarialberto sessions online #teched #msteched #tee2012

Marco Russo (SQLBI) — Sat, 07 Jul 2012 05:41:30 GMT

In June I participated to two TechEd editions (North America and Europe). I and Alberto delivered a Pre Conference and two sessions about Tabular. Both conferences provides recorded sessions freely available on Channel 9 so that you can compare which one has been delivered in the best way! If you have to choose between the two versions, consider that in North America we receive more questions during and after the session (still recording), increasing the interaction, whereas in Europe questions usually comes after the session finished (so no recording available). If you’re curious, watch both and let me know which version you prefer, especially for Multidimensional vs Tabular!

BISM: Multidimensional vs. Tabular (TechEd North America 2012)
BISM: Multidimensional vs. Tabular (TechEd Europe 2012)
Many-to-Many Relationships in BISM Tabular (TechEd North America 2012)
Many-to-Many Relationships in BISM Tabular (TechEd Europe 2012)

If you are interested to learn SSAS Tabular, don’t miss the next SSAS Tabular Workshop online on September 3-4, 2012. We are also planning dates for another roadshow in Europe this fall and I’m happy to announce we’ll have two dates in Germany, too. More updates in the coming weeks.

Optimize Many-to-Many with SUMMARIZE and Other Techniques

Marco Russo (SQLBI) — Fri, 01 Jun 2012 12:12:00 GMT

We are still in the early days of DAX and even if I have been using it since 2 years ago, there is still a lot to learn on that.

One of the topics that historically interests me (and many of the readers here, probably) is the many-to-many relationships between dimensions in a dimensional data model. When I and Alberto wrote the The Many to Many Revolution 2.0 we discovered the SUMMARIZE based pattern very late in the whitepaper writing. It is very important for performance optimization and it should be always used. In the last month, Gerhard Brueckl also presented an approach based on cross table filtering behavior that simplify the syntax involved, even if it’s harder to explain how it works internally.

I published a short article titled Optimize Many-to-Many Calculation in DAX with SUMMARIZE and Cross Table Filtering on SQLBI website just to provide a quick reference to the three patterns available. A further study is still required to compare performance between SUMMARIZE and Cross Table Filtering patterns. Up to now, I haven’t observed big differences between them, even if their execution plans might be not identical and this suggest me that depending on other conditions you might favor one over the other.

Order of Evaluation in CALCULATE and Clever Hierarchy Handling in #dax

Marco Russo (SQLBI) — Wed, 02 May 2012 04:36:00 GMT

After the first two editions of the SSAS Tabular Workshop, I and Alberto had some time to flush some of the pending activities. We published two articles:

Clever Hierarchy Handling in DAX: DAX is not a hierarchy-friendly language, especially if you have a MDX background. If you have to implement hierarchy-based calculation, MDX is still better. But if you need to do that in DAX (for example, because you need this in a DAX measure), then you have to read this article written by Alberto Ferrari that explains how to correctly calculate the classical “ratio to parent” pattern in several different scenarios.
Order of Evaluation in CALCULATE Parameters: a common mistake when you start working with DAX is understanding the evaluation order of CALCULATE parameters and what happens when you have nested CALCULATE statements. The topic can be very dense, so I tried to write a simple article that explain the basic concepts and based on the feedback I’ll receive, I’ll consider writing other articles on this subject. So feedback is welcome!

We have other ideas and material that I hope we’ll be able to write and publish in the upcoming weeks. In the meantime, there are plenty of other editions of the workshop this month, starting from the online one next week that is arranged for US time zone. Don’t miss it!

Last minute update #ssas #tabular community event in Amsterdam on April 16

Marco Russo (SQLBI) — Fri, 13 Apr 2012 23:45:00 GMT

We finally have the registration website for the community event about Tabular in Analysis Services in Amsterdam on April 16 evening that I introduced in my previous post - don't miss it, the registration are open (and limited!) at:

http://passnl-april2012.eventbrite.com/

This is really a "last-minute" event, but we're pretty confident the room will be full anyway! See you there!

#SSAS #Tabular Workshop and Community Events in Netherlands and Denmark

Marco Russo (SQLBI) — Thu, 12 Apr 2012 11:04:00 GMT

Next week I will finally start the roadshow of the SSAS Tabular Workshop, a 2-day seminar about the new BISM Tabular model for Analysis Services that has been introduced in SQL Server 2012. During these roadshows, we always try to arrange some speeches at local community events in the evening - we already defined for Copenhagen, we have some logistic issue in Amsterdam that we're trying to solve. Here is the timetable:

Netherlands

SSAS Workshop in Amsterdam, NL – April 16-17, 2012

2-day seminar, I and Alberto will be the trainers for this event, register here

We're trying to manage a Community event but we still don't have a confirmation, stay tuned

UPDATE: the community event has been comfirmed, please register here: http://passnl-april2012.eventbrite.com/

Denmark

SSAS Workshop in Copenhagen, DK – April 26-27, 2012

2-day seminar, I and Alberto will be the trainers for this event, register here

Community event on April 26, 2012

This event will run in Hellerup, at Microsoft venue
All details available here: http://msbip.dk/events/26/msbip-mode-nr-5/
People from Sweden are welcome! Just register to this private group on LinkedIn in order to announce your presence, so we’ll know how many people will attend

In community events we’ll deliver two speeches – here are the descriptions:

Inside xVelocity (VertiPaq)
PowerPivot and BISM Tabular models in Analysis Services share a great columnar-based database engine called xVelocity in-memory analytics engine (VertiPaq). If you want to improve performance and optimize memory used, you have to understand some basic principles about how this engine works, how data is compressed, and how you can design a data model for better optimization. Prepare yourself to change your mind. xVelocity optimization techniques might seem counterintuitive and are absolutely different than OLAP and SQL ones!

Choosing between Tabular and Multidimensional
You have a new project and you have to make an important decision upfront. Should you use Tabular or Multidimensional? It is not easy to answer, because sometime there is a clear choice, but most of the times both decisions might be correct, at least at the beginning. In this session we’ll help you making an informed decision, correctly evaluating pros and cons of each one according to common scenarios, considering both short-term and long-term consequences of your choice.

I hope to meet many people in this first dates. We have many other events coming in May and June, including an online event (for US time zones), and you can also attend our PreCon Day at TechEd US in Orland (PRC06) or TechEd Europe in Amsterdam. I’ll be a good customer for airline companies in the next three months!

I’m just sorry that I hadn’t time to write other articles in the last month, but I’m accumulating material that I will need to write down during some flight – stay tuned…

Distinct Count of Customers in a SCD Type 2 in #DAX

Marco Russo (SQLBI) — Mon, 09 Apr 2012 20:55:25 GMT

If you have a Slowly Changing Dimension (SCD) Type 2 for your customer and you want to calculate the number of distinct customers that bought a product, you cannot use the simple formula:

Customers := DISTINCTCOUNT( FactTable[Customer Id] ) )

because it would return the number of distinct versions of customers. What you really want to do is to calculate the number of distinct application keys of the customers, that could be a lower number than the number you’ve got with the previous formula. Assuming that a Customer Code column in the Customers dimension contains the application key, you should use the following DAX formula:

Customers := COUNTROWS( SUMMARIZE( FactTable, Customers[Customer Code] ) )

Be careful: only the version above is really fast, because it is solved by xVelocity (formerly known as VertiPaq) engine. Other formulas involving nested calculations might be more complex and move computation to the formula engine, resulting in slower query.

This is absolutely an interesting pattern and I have to say it’s a killer feature. Try to do the same in Multidimensional…

Microsoft SQL Server 2012 Analysis Services – The BISM Tabular Model #ssas #tabular #bism

Marco Russo (SQLBI) — Wed, 28 Mar 2012 08:26:00 GMT

I, Alberto and Chris spent many months (many nights, holidays and also working days of the last months) writing the book we would have liked to read when we started working with Analysis Services Tabular. A book that explains how to use Tabular, how to model data with Tabular, how Tabular internally works and how to optimize a Tabular model. All those things you need to start on a real project in order to make an happy customer. You know, we’re all consultants after all, so customer satisfaction is really important to be paid for our job!

Now the book writing is finished, we’re in the final stage of editing and reviews and we look forward to get our print copy. Its title is very long: Microsoft SQL Server 2012 Analysis Services – The BISM Tabular Model. But the important thing is that you can already (pre)order it.

This is the list of chapters:

01. BISM Architecture
02. Guided Tour on Tabular
03. Loading Data Inside Tabular
04. DAX Basics
05. Understanding Evaluation Contexts
06. Querying Tabular
07. DAX Advanced
08. Understanding Time Intelligence in DAX
09. Vertipaq Engine
10. Using Tabular Hierarchies
11. Data modeling in Tabular
12. Using Advanced Tabular Relationships
13. Tabular Presentation Layer
14. Tabular and PowerPivot for Excel
15. Tabular Security
16. Interfacing with Tabular
17. Tabular Deployment
18. Optimization and Monitoring

And this is the book cover – have a good read!

Investigating on xVelocity (VertiPaq) column size

Marco Russo (SQLBI) — Mon, 19 Mar 2012 11:15:00 GMT

In January I published an article about how to optimize high cardinality columns in VertiPaq. In the meantime, VertiPaq has been rebranded to xVelocity: the official name is now “xVelocity in-memory analytics engine (VertiPaq)” but using xVelocity and VertiPaq when we talk about Analysis Services has the same meaning. In this post I’ll show how to investigate on columns size of an existing Tabular database so that you can find the most important columns to be optimized.

A first approach can be looking in the DataDir of Analysis Services and look for the folder containing the database. Then, look for the biggest files in all subfolders and you will find the name of a file that contains the name of the most expensive column. However, this heuristic process is not very optimized.

A better approach is using a DMV that provides the exact information. For example, by using the following query (open SSMS, open an MDX query on the database you are interested to and execute it) you will see all database objects sorted by used size in a descending way.

SELECT *
FROM $SYSTEM.DISCOVER_STORAGE_TABLE_COLUMN_SEGMENTS
ORDER BY used_size DESC

You can look at the first rows in order to understand what are the most expensive columns in your tabular model. The interesting data provided are:

TABLE_ID: it is the name of the object – it can be also a dictionary or an index
COLUMN_ID: it is the column name the object belongs to – you can also see ID_TO_POS and POS_TO_ID in case they refer to internal indexes
RECORDS_COUNT: it is the number of rows in the column
USED_SIZE: it is the used memory for the object

By looking at the ration between USED_SIZE and RECORDS_COUNT you can understand what you can do in order to optimize your tabular model. Your options are:

Remove the column. Yes, if it contains data you will never use in a query, simply remove the column from the tabular model
Change granularity. If you are tracking time and you included milliseconds but seconds would be enough, round the data source column to the nearest second. If you have a floating point number but two decimals are good enough (i.e. the temperature), round the number to the nearest decimal is relevant to you.
Split the column. Create two or more columns that have to be combined together in order to produce the original value. This technique is described in VertiPaq optimization article.
Sort the table by that column. When you read the data source, you might consider sorting data by this column, so that the compression will be more efficient. However, this technique works better on columns that don’t have too many distinct values and you will probably move the problem to another column. Sorting data starting from the lower density columns (those with a few number of distinct values) and going to higher density columns (those with high cardinality) is the technique that provides the best compression ratio.

After the optimization you should be able to reduce the used size and improve the count/size ration you measured before.

If you are interested in a longer discussion about internal storage in VertiPaq and you want understand why this approach can save you space (and time), you can attend my 24 Hours of PASS session “VertiPaq Under the Hood” on March 21 at 08:00 GMT.

A new SQL, a new Analysis Services, a new Workshop! #ssas #sql2012

Marco Russo (SQLBI) — Wed, 14 Mar 2012 18:03:00 GMT

One week ago Microsoft SQL Server 2012 finally debuted with a virtual launch event and you can find many intro sessions there (20 minutes each). There is a lot of new content available if you want to learn more about SQL 2012 and in this blog post I’d like to provide a few link to sessions, documents, bits and courses that are available now or very soon.

First of all, the release of Analysis Services 2012 has finally released PowerPivot 2012 (many of us called it PowerPivot v2 before this official name) and also the new Data Mining Add-in for Microsoft Office 2010, now available also for Excel 64bit! And, of course, don’t miss the Microsoft SQL Server 2012 Feature Pack, there are a lot of upgrades for both DBAs and developers. I just discovered there is a new LocalDB version of SQL Express that can run in user mode without any setup. Is this the end of SQL CE?

But now, back to Analysis Services: if you want some tutorial on Tabular, the Microsoft Virtual Academy has a whole track dedicated to Analysis Services 2012 but you will probably be interested also in the one about Reporting Services 2012.

If you think that virtual is good but it’s not enough, there are plenty of conferences in the coming months – these are just those where I and Alberto will deliver some SSAS Tabular presentations:

SQLBits X, London, March 29-31, 2012: if you are in London or want a good reason to go, this is the most important SQL Server event in Europe this year, no doubts about it. And not only because of the high number of attendees, but also because there is an impressive number of speakers (excluding me, of course) coming from all over the world. This is an event second only to PASS Summit in Seattle so there are no good reasons to not attend it.
Microsoft SQL Server & Business Intelligence Conference 2012, Milan, March 28-29, 2012: this is an Italian conference so the language might be a barrier, but many of us also speak English and the food is good! Just a few seats still available.
TechEd North America, Orlando, June 11-14, 2012: you know, this is a big event and it contains everything – if you want to spend a whole day learning the SSAS Tabular model with me and Alberto, don’t miss our pre-conference day “Using BISM Tabular in Microsoft SQL Server Analysis Services 2012” (be careful, it is on June 10, a nice study-Sunday!).
TechEd Europe, Amsterdam, June 26-29, 2012: the European version of TechEd provides almost the same content and you don’t have to go overseas. We also run the same pre-conference day “Using BISM Tabular in Microsoft SQL Server Analysis Services 2012” (in this case, it is on June 25, that’s a regular Monday).

I and Alberto will also speak at some user group meeting around Europe during… well, we’re going to travel a lot in the next months.

In fact, if you want to get a complete training on SSAS Tabular, you should spend two days with us in one of our SSAS Tabular Workshop! We prepared a 2-day seminar, a very intense one, that start from the simple tabular modeling and cover architecture, DAX, query, advanced modeling, security, deployment, optimization, monitoring, relationships with PowerPivot and Multidimensional… Really, there are a lot of stuffs here! We announced the first dates in Europe and also an online edition optimized for America’s time zone:

Apr 16-17, 2012 – Amsterdam, Netherlands
Apr 26-27, 2012 – Copenhagen, Denmark
May 7-8, 2012 – Online for America’s time zone
May 14-15, 2012 – Brussels, Belgium
May 21-22, 2012 – Oslo, Norway
May 24-25, 2012 – Stockholm, Sweden
May 28-29, 2012 – London, United Kingdom
May 31-Jun 1, 2012 – Milan, Italy (Italian language)

Also Chris Webb will join us in this workshop and in every date you can find who is the speaker on the web site.

The course is based on our upcoming book, almost 600 pages (!) about SSAS Tabular, an incredible effort that will be available very soon in a preview (rough cuts from O’Reilly) and will be on the shelf in May. I will provide a link to order it as soon as we have one!

And if you think that this is not enough… you’re right! Do you know what is the only thing you can do to optimize your Tabular model? Optimize your DAX code. Learning DAX is easy, mastering DAX requires some knowledge… and our DAX Advanced Workshop will provide exactly the required content. Public classes will be available later this year, by now we just deliver it on demand.

Why to use #Tabular in Analysis Services 2012 #ssas

Marco Russo (SQLBI) — Thu, 01 Mar 2012 12:43:00 GMT

A few months ago I gave a speech at SQLBits 9 comparing Vertipaq vs. OLAP (you can watch session recording here), which I repeated a few times in other conferences and webcasts. The title was intentionally provocative but it contained an explanation of the real content – in fact it is “Vertipaq vs OLAP: Change Your Data Modeling Approach”. In these days I’m realizing how huge the impact of this technology will be, and how I just scratched the surface of this revolution happening in the BI Modeling world. Regardless you watched the session or not (and you may have another good reason to do that after reading this post), let’s try to see what’s happening from another point of view.

How do you model a BI Solution? Well, you can have a data warehouse or not, you can apply SQLBI Methodology or another of your choice (there are many of them, yes). But, at the end, you create a star schema. Maybe a snowflake one, even if it is almost always a bad idea. But a snowflake schema can be converted in a star schema, so let’s go for that. You have to conform your data model to a well known pattern that can be easily digested by an Analysis Services cube. I mean, a Multidimensional model. And that’s fine, it works great, millions of customers are very happy with that.

But then, someone comes with a question that was not planned in advance. Someone says “I’d like to analyze customer behavior, define a cluster of my customers based on cube slicing and dicing and then I want to know if in this specific month for this promotion they bought more or less than their single individual average over the last three months”. And they expect this to be doable and fast. At this point you have several options:

Talk about the next football/baseball/NBA/rugby match hoping he’ll forget what he asked for;
Finally show your master level of MDX and write a geeky MDX query that doesn’t work in Excel and cannot be browsed in an Excel PivotTable (users seems not able to appreciate how nice is writing your custom client code with AMO – except a few Italian speaking people, who understand that AMO is just the first single person present of the verb LOVE, even if for an inexplicable reason most of them tend to associate the word with the noun fishhook, which has the same spelling in Italian);
Change the data model in Analysis Services, only to realize that you have to change the Data Mart design and the ETL, pushing the business logic of a query down to the ETL implementation, for the happiness of those guys who worked hard to pump data in your ultimate data model that should have been able to answer to any query from any user at any time.

Well, I tried all of them, and I can say that the last one is the most expensive and the only one that really makes the user happy for 5 minutes, until he realizes he just would like to see something else he hadn’t thought before. And this process is called knowledge discovery, is perfectly well known, described in the books and if only he would be able to formulate a question within a predictable path, we would live in a better world. But, hey, I’m a consultant, there are no issues, just opportunities. At least until an opportunity moves beyond the horizon of the profitability ROI line.

Are you with me? Have you experienced all of this? (If you’re not a consultant, you can just replace “profitability ROI” with “acceptable quality of working life”, which is the equivalent concept - a consultant never experience the notion of a life beyond the work and would not understand the expression at all, simply reporting a Connect item to the Word team in order to fix the Spelling and Grammar checker).

Well, now there is Analysis Services 2012. You can create models in Multidimensional or Tabular. At first, Tabular has less features than Multidimensional, it’s simpler and you can define a model that looks like a Multidimensional one. You can create a Tabular model that, connecting from Excel, appears exactly like a Multidimensional one. It could be faster, it could be easier to develop, but at the end it will seem offering the same features to the end user. Apparently, nothing is changed. At least, if you are lazy enough to not think you can do more.

A few months after my talk at SQLBits I continue to see new reasons to move to Tabular just for two capabilities that are hidden in the marketing stuffs, but are really incredible important from a data modeling point of view:

You can query data using relationships that are not defined in the data model as “regular” relationships
You can extend the data model without reprocessing the whole database by using calculated columns

These two features adds a fourth option to answer to the question that originated this blog post. And the fourth option is:

Just do it

I’m not saying it’s easy. But by moving your DAX skills to an expert level, you are able to write complex queries keeping good performance without requiring to change the underlying the data model. Translated in practical terms: happy customer, problem solved, out of office in time to watch your favorite football/baseball/NBA/rugby/whatever match on TV.

A few months after my talk at SQLBits I continue to see new reasons to move to Tabular just for the flexibility it allows if you want to query it. If you tried OLAP in the past and it was too rigid in data modeling for your requirements, give Tabular a chance. You have to learn it deeper than a casual user, but you will found an unexplored territory of new data modeling and reporting opportunities. (How many articles have you read about querying Tabular from Reporting Services?)

Next mission: convince IT Pros to not virtualize a SSAS box with Tabular because it is much more convenient to spend money on fast CPU, not too many cores, large and fast memory, cheap disks. Tactic: design a fancy dashboard for the CEO and getting immunity to fight against the SAN lobbies (Thomas would use a less polite term here).

Create a #dax Measure in a Session in #BISM #Tabular

Marco Russo (SQLBI) — Tue, 28 Feb 2012 12:47:00 GMT

Thanks to Greg Galloway that pointed me out this is possible, in this post I want to show how to create a measure local to a session. This could be done in SQL Server Management Studio, even if it is probably not so useful for a Tabular model considering that you can easily define measures local to a DAX query. However, maybe you will have a reason to do that (I can only imagine a long list of measures defined in a session that are used in small queries sent from a client – it could be an idea for custom clients connecting to Tabular that sends many short queries using the same measures, for example a dashboard or a set of charts).

First, you have to connect to Analysis Services by including the Cube=<cubename> in the connection string. By default, the cube name of a tabular model is “Model”, so you can use Cube=Model in the connection string. You can do that in SQL Server Management Studio by placing that string in the Additional Connection Parameters, as you can see in the following picture.

Then, instead of using DEFINE MEASURE before EVALUATE statement, like you would do in the following statement:

DEFINE

    MEASURE 'Internet Sales'[Internet Total Sales] = SUM([Sales Amount])
    MEASURE 'Internet Sales'[Internet Total Product Cost] = SUM([Total Product Cost])
    MEASURE 'Internet Sales'[Internet Total Margin] = [Internet Total Sales] - [Internet Total Product Cost]
EVALUATE
SUMMARIZE(
    'Internet Sales',
    'Date'[Calendar Year],
    "Sales", 'Internet Sales'[Internet Total Sales],
    "Cost", 'Internet Sales'[Internet Total Product Cost],
    "Margin", 'Internet Sales'[Internet Total Margin]
)

You can define measures by using the CREATE SESSION statement. Please note that if you didn’t set the Cube in the connection string, you would get an error.

CREATE SESSION
MEASURE 'Internet Sales'[Internet Total Sales] = SUM([Sales Amount])
MEASURE 'Internet Sales'[Internet Total Product Cost] = SUM([Total Product Cost])
MEASURE 'Internet Sales'[Internet Total Margin] = [Internet Total Sales] - [Internet Total Product Cost]

At this point, you can reference the measures you defined in your DAX query:

EVALUATE
SUMMARIZE

(
    'Internet Sales',
    'Date'[Calendar Year],
    "Sales", 'Internet Sales'[Internet Total Sales],
    "Cost", 'Internet Sales'[Internet Total Product Cost],
    "Margin", 'Internet Sales'[Internet Total Margin]
)

I’d be interested to hear whether you find some scenario in which it is useful using this feature!