Registrations are now open for the SSAS Maestro course in July 2012, which will take place in Milan (Italy).

I introduced this course in a previous blog post and I’m not going to repeat all the details here. I just want to highlight a few updates, the biggest one is related to the cost of the course. We received several feedback and cut some of the costs for reducing the fee. One of the consequences is that we will not include Tabular as part of the SSAS Maestro course, which will be dedicated only to Multidimensional. We’ll evaluate how to include Tabular in the future. As a result, the price is now 4500€ (reduced to 3500€ for students of a previous SSAS Maestro edition). This is not going to lower the overall value of the course and the price includes the complete evaluation process after the training.

Another very good news is that Thomas Kejser (from the SQLCAT team) will join me and Chris Webb teaching the class. This is another important contribution that adds value to the course: Thomas has been involved in development and deployment of TBs-sized cubes and discussions about scalability of SSAS cubes are an important part of the SSAS Maestro training.

Please read my previous post to get other details about the SSAS Maestro certification process. If you want to get more information and/or ask if you are a good candidate for SSAS Maestro certification, write to ssasmaestro@sqlbi.com sending your CV/resume and a short description of your level of SSAS knowledge and experience. The seats are limited and it is important that students have the right prerequisites in terms of SSAS experience. When you are ready, you can register here.

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!

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…

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

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 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).

I recently wrote an article about how to implement a Process Add on a table in a Tabular model. This is an area where there is a lack of documentation by Microsoft, especially if you want to use a custom binding query for every Process Add command, which should be the common case for a Process Add scenario. Cathy Dumas wrote several blog posts about this argument and I tried to put everything together, showing the same example written in XMLA Script, AMO and PowerShell. You can also use Integration Services, but considering you probably want to customize the binding query, chances are that you will opt for a more programmatic approach, maybe embedding an XMLA Script or an AMO Script in a standard Task in SSIS.

The next step for me will be studying how much “near real time” a Tabular model can be by using this type of Process option. In fact, it should be possible to use a push model processing, like in a Multidimensional model, and my initial tests say that there is no “out of service” window when you process data in Tabular. So you can forget the lock issue you may have in a Multidimensional model in Analysis Services. But you need memory and it is hard today to say how much this architecture can scale in terms of concurrent users when you start processing data incrementally. Also the quality of compression made by Vertipaq might suffer.

If you have any experience in this area, please contact me and share your knowledge! Otherwise, stay tuned, I’ll try to further study this topic, then blogging what I’ll found.