My 4yo son keeps asking me why I should leave home so often to attend SQL conferences around the world and today, after a couple of days of snow, he discovered a good reason, along with the best place for the SQLBits scarf.

Data modeling in Tabular is different from Multidimensional. I spend much time teaching people that they need to change their mind when approaching the Vertipaq engine, because it is a different tool with different capabilities. Nevertheless, I still have a lot to learn on this topic, as I am going to show in the next paragraphs.

One customers of mine has a 4 Billion rows table containing some numbers he wants to work with. Among those, two columns contain a value and a weight and, when aggregating the value, it should be weighted by the weight. This is a very common pattern: in the banking environment, for example, you might have the rate and the amount and, to get the mean rate, you need to weight the detail rates by the amount. In my specific scenario, the value represents the age of a customer, ranging from 0 to 100, while the weight is an integer number ranging from 1 to 60M. Take note of the ranges, it will come handy later.

I am used, in such a scenario, to store in the fact table the result of Age * Weight in a column called AgeMultipliedByWeight so that the aggregate can be computed as SUM (AgeMultipliedByWeight)/SUM(Weight). Following this pattern, I developed the solution and performances were pretty good. Nothing exceptional, but it worked fine. The reason to store the intermediate calculation is to use SUM instead of SUMX, getting better performances, as I normally teach during my classes.

Then, during PASS 2011, I had the option to discuss this model with the dev team and it has been very funny to get their feedback, which sounded like: “why did you do something that stupid? You don’t need it, just compute the values runtime using SUMX, it is much faster and you consume a lot less memory”. Learning why this pattern is wrong in Tabular is definitely worth spending some time, because in Multidimensional this is the right pattern. So, let us think in DAX and try to figure out what’s wrong with storing intermediate calculations.

First of all, we already know that the memory usage of Vertipaq depends on the number of distinct values of a column. It turns out that, while the age has only 100 distinct values, AgeMultipliedByWeight has 600 millions of different values. The difference is huge and, in fact, it turned out that the AgeMultipliedByWeight column was using a lot of memory (9Gb in my scenario with 4 billion rows). First lesson learned, increasing the number of distinct values of a column is easy as performing some computation over it and a lot of precious memory is wasted if you don’t pay attention to that.

The second and most important consideration is that the DAX Formula Engine is capable to push simple calculations to the Vertipaq Engine, which runs in a multithreaded environment (while the Formula Engine is still single threaded). Thus, if a SUMX contains a simple calculation (i.e. simple math with no complex formulas), this formula will be pushed down to Vertipaq and will be executed in a parallel way directly at the leaf level. There’s a lot of work yet to be done in order to understand what “simple calculations” means, but I will speak about that in future posts, as soon as I discover more information about what can and cannot be pushed down to Vertipaq.

Back home, I had the option to try this on my server and, as expected, performance is now much better. By changing the formula from SUM to SUMX, avoiding the intermediate calculated column, I saved 9Gb of RAM and the test query, which was running in 13 seconds, is now executed in 3 seconds, i.e. 4 times faster, scaling on all the available cores.

Thus, the first lesson here is always the same: thinking in DAX is not easy, we need to get rid of many of the concepts we have learned with UDM/MDX and change the way we approach problems. The results, when following this practice, are awesome. There is another lesson, probably the most important one: speaking with people at conferences lets you learn a lot, much more than you will ever be able to learn alone in the office or at home.

For the ones of you coming at SQL PASS Nordic… I am eager to speak with you too, see you there on 8-9 November.

Using Visual Studio to author SSAS Multidimensional projects, I was used to copy the project, deploy it with another name and then proceed with updates, until I was satisfied with the final result. Then, working with the two projects side-by-side I could check performance and numbers.

I tried the same technique with Visual Studio 2010 with CTP3 Denali with a Tabular model. I created a copy of an existing project but, as soon as I opened it, I got the error “This model is already open in another location by the same user, please close the other project before working on this one”:

The reason for the error is that, copying the project folder, I copied the user settings file too, which contains the workspace database definition. Because the workspace is already in use by the previous instance of the project (which is still open, due to the way I work) and a workspace cannot be used by more than one active project. For more information about the user settings file and the behavior when opening a BIM project in Denali, refer to this very comprehensive post of Cathy Dumas.

The solution to this problem is straightforward (once you learn it, thanks Cathy for explaining this!): just locate and delete the user settings file (which, by the way, is a hidden file, thus you will need to show hidden files before searching for it). As soon as Visual Studio opens a project with no user settings file, it will create a new workspace, detaching the copied project from the original one, and I can work with the two projects side-by-side.

Tabular projects work in a different way, when compared with Multidimensional ones, we just need to learn new tricks, as always.

September 29th I and Marco Russo will be delivering a full day of deep dive into the DAX programming language, during the training days of SqlBits 9. Later on, during the conference, I will deliver a session about many to many relationships in DAX, exploiting the power of the Tabular data model with DAX to solve complex problems with many to many relationships. Mine is just one among several great sessions delivered by many of the most talented SQL professional from all around the world.

There are still a few seats available for the training day on DAX. Thus, if you are interested, hurry up and register yourself, I’m eager to meet you in Liverpool.

You can find full information here: http://www.sqlbits.com/information/Event9/DAX_Deep_Dive/TrainingDetails.aspx.

See you there

A new date is available for the PowerPivot workshop I and Marco are delivering all over Europe.

On September 19-20, 2011 we will be in Lisbon (Portugal) to present the workshop to Excel users and BI professionals.

Moreover, as Denali CTP3 of PowerPivot is now disclosed, we will present, for the first time, an enhanced version of the workshop with a new set of slides and demos aimed to understand what’s new in Denali for all the PowerPivot users.

In the meantime, we are organizing a community event on the evening of the 19th, where we are going to speak about the roadmap from UDM to BISM and how to shape your data and skills for the incoming version of Analysis Services. We will publish all the details of the event as soon as we will know them.

You can find more information and the registration page at www.powerpivotworkshop.com.

I know from personal experience that Lisbon is a wonderful city and the food is simply great. Moreover, I have been assured that the weather will be nice too in that period. Thus, I look forward to see many of you at the workshop and at the community event, even from outside of Portugal!

In my last post about Parent/Child hierarchies, there is a question, in the comments, that I found interesting. Nevertheless, the formula is a complex one and cannot be written in a simple comment. Thus, I am making a follow-up to that post. I am not repeating all the stuff of the previous post so, please, read that before reading this one, to have the necessary background.

The question looks a simple one: “If you needed a measure (SumOfLeafAmount) that only shows childrens values, what would it be like?”

I was about to answer that “I have a formula that is too large to fit in the margin” but… Fermat did it some years ago and caused a real mess. I don’t want to be responsible for such a complication. Thus, I am providing the answer in this new blog post. And… ehy, no, I don’t consider myself anyhow similar to that genius, I’m just joking!

There two interesting points in this simple question:

1. There are two interpretation of the formula: one is “sum only the leaves”, the other ones is “sum only the children”. Both are valid and interesting formulas, but they will lead to completely different implementations, and I am going to show both.
2. During the development of the “sum of the children” we are going to hit the CALCULATE wall with our head and… yes, it hurts.

Let us start with the set of data we are going to work on. The first figure is the hierarchy:

And, in the second one, I have put in the same figure a PivotTable containing the P/C structure with the SumOfAmount measure and the content of the Invoices table, which is the source of the Amount column.

Now, what are the desired results?

1. SumOfChildren, for Bill, is 1600, because his amount is coming from children only, i.e. Brad, Chris, Vincent, Julie. Bill himself is not producing any sale. SumOfChildren for Brad, on the other hand, is 900, because he sold 400 by himself with two invoices.
2. SumOfLeaves, for Bill, is 1200 because the value produced by Brad is not on a leaf. Brad has children, thus he is not a leaf and his value should not be aggregated. Among its children, only Brad has a SumOfLeaves greater than zero, because he has children, while all others (Chris, Vincent and Julie) are leaves.

In the case of Annabel, both formulas will return the same value because all of Annabel’s children are leaves.

Let us start with SumOfLeaves, which is pretty easy. Any row in the hierarchy can be a leaf or not. It is a leaf if it has no children, otherwise it is the parent of somebody, thus it is not a leaf. In other words, it is a leaf it there aren’t any rows in the hierarchy where the ParentNodeId has the value of the NodeId.

We can define a new calculated column, called IsLeaf, with this formula:

=COUNTROWS (
    FILTER ('Hierarchy', 'Hierarchy'[ParentNodeID] = EARLIER ([NodeId]))
) = 0

Or, if you are a real fan of Denali and of the new Parent/Child functions, this one works too (and, these days, it makes you look cooler):

=COUNTROWS (
    FILTER (
        'Hierarchy', 
        PATHCONTAINS ('Hierarchy'[HierarchyPath], EARLIER ([NodeId]))
    )
) = 1

Now, the hierarchy has a new column:

And the formula for the measure SumOfLeaves becomes straightforward, just a simple usage of CALCULATE:

SumOfLeaves=IF (
    [BrowseDepth] > [MinNodeDepth], 
    BLANK(),
    CALCULATE (
        SUM (Invoices[Amount]), 
        'Hierarchy'[IsLeaf] = TRUE
    )
)

The result, compared with SumOfAmount, is very clear:

The reason for which SumOfLeaves is easy is because each row in the hierarchy is either a leaf or not, its behavior is easy to define. In the case of SumOfChildren, this is no longer true. Each row can be aggregated to the total or not, depending on the level we are browsing.

Take, for example, the value of Brad. When we are summarizing Brad’s amount, his value should not be computed, because he is not a child of himself. Thus, SumOfLeaves(Brad)=900. But, when we summarize the value of Bill, Brad is a children of Bill and the complete amount of 1300 should be computed for Bill’s amount. Same row, different behaviors… things are becoming interesting.

If we want to compute the sum of all the children of a node in a P/C hierarchy, it is enough to create a filter context that isolates the current root of the tree. Once we have isolated that row, we can create a FILTER expression that computes all the nodes but that one. The problem is that a row can be the root at some point and a regular node at other points, the difference being the value of BrowseDepth.

In order to understand the incoming formula, we need to take some time to study our data set. I have created a new measure, to see the value of NodeDepth inside the PivotTable, which basically shows the value only when a single row of the hierarchy is selected:

[Node Depth] = IF (
    COUNTROWS ('Hierarchy') = 1,
    VALUES ('Hierarchy'[NodeDepth]),
    BLANK ()
)

With this new measure, and after having modified slightly the definition of the Level1..3 calculated columns to always show the value of a node (it was previously blank), I got this very interesting figure:

Up to now, we have used the difference between BrowseDepth and NodeDepth to hide rows. Now we will use the same values to compute the SumOfChildren. By carefully looking at the PivotTable, it is easy to verify that the SumOfChildren of Brad will need to avoid computing the highlighted row, where BrowseDepth > NodeDepth. Clearly, when computing the value for Brad, we will be interested in the rows in the red box, all others are of no interest.

When the same row (the highlighted one) will be computed for Bill’s total, the value of BrowseDepth will be 1, while NodeDepth will still be 2. In that case, the value of Brad will be used to compute the total for Bill.

Thus, by using this simple test, SumOfChildren will compute the sum of the values of all the children of a node, avoiding the node itself, which is the only one where the condition is false. With all this in mind, the formula is straightforward:

[SumOfChildrenWrong]=
IF (
    [BrowseDepth] > [MinNodeDepth], 
    BLANK(),
    CALCULATE (
       SUM (Invoices[Amount]),
       FILTER (
            'Hierarchy',
            'Hierarchy'[NodeDepth] > [BrowseDepth]
       )
    )
)

We use FILTER to remove the nodes whose NodeDepth is not higher than BrowseDepth. Add this measure to the PivotTale and this is the result:

Et voilà, BLANK everywhere, not a single number shown. Something is wrong but… what? I strongly suggest you to check the formula by yourself at least twice before to continue reading because this is the CALCULATE wall and, hitting it, is always a pain.

Now, the solution. I already gave you the first hint: the problem has something to do with CALCULATE. Ok, how many CALCULATE do you see in the formula?

If you answered 1, which is the naïve answer, you are able to read a formula, but you hit the CALCULATE wall. If you answered zero, two or three, it means you need better glasses. But if you answered the only right answer, which is FOUR, then you have already touched the DAX karma: you already know what I am going to write.

Where are those four CALCULATE? We need to remember that whenever a MEASURE is used inside a formula, it is automatically surrounded by a hidden CALCULATE. And, in this formula, we have three measure calls:

• [BrowseDepth] in the first parameter of IF
• [MinNodeDepth] in the first parameter of IF
• [BrowseDepth] in the inner test of the FILTER

These three measures, plus the evident CALCULATE, make the total of four CALCULATE inside the formula.

Now, what happens for the [BrowseDepth] measure computed inside FILTER? We have a CALCULATE inside a loop, that means that the row context introduced by FILTER (yes, FILTER is an iterator, never forget it!) is converted into a filter context before evaluating the measure. Thus, the [BrowseDepth] is computed in a filter context where the only visible row of the Hierarchy table is the currently iterated row. Check it by yourself, the value of that [BrowseDepth] is always three, because only a single row of the hierarchy is visible. The original filter context is lost when that measure is computed.

The value of [BrowseDepth] inside the FILTER is NOT the value of [BrowseDepth] for the original filter context, it is the [BrowseDepth] in a new context introduced by the automatic CACLULATE added by the measure call.

If we want to compute our measure, we need to avoid that nasty CALCULATE created by the engine. The only way to do that is to remove the measure call and expand it to its measure definition (which you can find in my previous post):

[SumOfChildren]=
IF (
    [BrowseDepth] > [MinNodeDepth], 
    BLANK(),
    CALCULATE (
       SUM (Invoices[Amount]),
       FILTER (
           'Hierarchy',
           'Hierarchy'[NodeDepth] > 
               IF (ISFILTERED ('Hierarchy'[Level3]), 3,
               IF (ISFILTERED ('Hierarchy'[Level2]), 2,
               IF (ISFILTERED ('Hierarchy'[Level1]), 1
           )))
       )
    )
)

The formula is identical to the previous one but, this time, we are not calling a measure, we are computing a formula. Thus, no automatic CALCULATE is added to the expression. Guess what? It now works as expected

Now, you see that all the leaf level nodes do not have any value and this is expected, since their value is provide by themselves only, not by their non-existent children.

I personally don’t believe that this formula is really useful but it might mean something to a customer who give me some money to compute it. Thus, it would be useful at least for me. The interesting point of this post is that DAX is simple, but not easy. This is the best definition of DAX I have ever heard. Until you fully understand CALCULATE and all its implications… DAX will be a black art. Take your time to study it, and DAX will become your best friend.

Surfing on the web, here, I came into this intriguing question:

How do we ask something like "Show me how many customers have an iPad but don't have a book?"

We are speaking about a sales analysis where the canonical entities are Customers, Products and Sales. Moreover, because we have been used to speak about cycles since many years ago, due to the nature of AdventureWorks, the question can be posed in AdventureWorks’s language as:

Of all the customers who have bought a mountain bike, how many have never bought at least one mountain tire tube?

The marketing need to answer this question is evident, if my customers have bought a mountain bike, they need to buy tire tubes. If they don’t buy them from me, it means that they are taking them somewhere else, and this is not good.

There are some interesting points in this simple question that turn the scenario into a challenging one:

1. We want to filter customers based on sales and then perform further analysis on the behavior of these customers
2. The simple presence of “not” in the question makes it much more complicated because we will need to check the customer that did not buy tires and this is not very intuitive to compute because we need to search for lacking information
3. The question has been posed for a single product but it is clear that the customer might want to perform the analysis on categories of products too. Moreover he probably want to be able to slice at the single product level.
4. Finally, we need to find a generic solution which is easy to query with a PivotTable, because users like Excel

The scenario is an interesting one, definitely worth a post. What about the solution, is it interesting too? It is for one simple reason: in order to solve it we will need to search for a many-to-many relationship where we are not used to find one. Moreover, I love all the solutions that force me to think in DAX and, as we are going to see, the scenario is quite simple in DAX while it would be much more complicated using any other tool. Well… beware that “quite simple” does not mean “simple”, the DAX code is a bit intricate but, hopefully, I am going to help you reading it.

A simple side note: this data model has been developed with PowerPivot but, as the new data modeling paradigm has been disclosed just a few days ago with the name “tabular” in the BISM architecture, I will refer to this as a “tabular data model” to distinguish it from the “multidimensional data model”.

In fact, this scenario is very similar to the survey data model described in the “Many-to-Many revolution” by Marco Russo even if the data structure is not so evident at first glance.

Let us start speaking about the data model. It is a canonical star schema as taken from the AdventureWorks data warehouse:

Importing these tables (and their relationships) inside PowerPivot is a good first step but soon we will discover that something is missing. We need to have two instances of the DimProduct table. One is needed to detect the customers who have bought one of the selected product, in our example the mountain bike. The other one will be needed to select the product which we want to check, in our example the tire tubes. Thus, we will need to load the DimProduct twice inside the tabular data model. Because the new instance of DimProduct will be used for filtering, we name it “ProductFilter”.

Thus, the complete data model we are going to work with is the following:

This definitely looks like a strange diagram: the new ProductFilter table has no relationships with the fact table nor with any dimension. In fact, if you think at it carefully, you’ll discover that we have no way to create a relationship between the fact table and the ProductFilter table, because the only available ProductKey in the fact table has already been used for the relationship with DimProduct. Tabular, among its limitations, can use a column for one relationship only. Nevertheless, we don’t care about it: the point is that that relationship is not needed, we will write the DAX code without leveraging its existence.

Before to dive into the DAX solution, let us spend some time to describe the desired result. We want to be able to produce a report like this:

In the report, we have selected four model types in the vertical slicer (mountain bikes from 100 to 500) which are the product we require the customer to have bought. Then we have selected two product models in the horizontal slicer (tire tubes and patch kits) which represent the products we want to check whether the customer have bought or not.

On the rows we have put FilterModelName and the ProductName columns. On the columns, as usual, there is the time. The two measures shown are:

• HavingProduct: it counts the number of customer who bought a mountain bike and some tire tube or patch kit.
• NotHavingProduct: it counts the number of customer who bought a mountain bike but no tire tube nor patch kit.

Due to the nature of the measures, we have decided to make these measures incremental over time. This means that, in 2003, we count all the customers that have bought a mountain bike before the end of 2003 and have bought tire tubes before the same time. In 2004, we perform the same computation, thus including the customers of 2003. It can be changed, but it makes sense to compute the value this way.

Thus, the PivotTable can be read as (first line): 396 (43+353) people have bought a Mountain-100 bicycle before 2003 and, of those, 43 have bought either a mountain tire tube or a patch kit. 353 of them did not buy tires or tubes (at least from us). The same for 2004 and for all other cells.

Ok we spent enough time describing the scenario, it is now  time to write some DAX code. Because the final formulas turn out to be quite complicated, we will describe them step by step, trying to figure out the algorithm before giving the final formula.

Let us start with the HavingProduct measure, which is somehow simpler since it does not contain the “not” part.

First of all, we need to compute the set of the customers who have bought a mountain bike before 2003. Let us focus on the worksheet: there is a slicer filtering model names on the ProductFilter table and we want to use this filter to detect the customer who have bought these products. Moreover, the presence of the year on the columns creates a filter on the time table, showing only the sales for 2003. The steps we need to carry on are two:

• We need to push the filter from the ProductFilter to the customers, using the fact table as a bridge
• We need to extend the filter context on time to show all the time before the end of 2003, since we want “before 2003”

We need to take into account the fact that the scenario is a bit more complicated than what we have spoken so far. In reality, we forgot to speak about the horizontal slicer. Its presence means that the fact table is filtered from the DimProduct too. That filter exists because there is a relationship between DimProducts and the fact table. Moreover, remember that our task is to push the filter on ProductFilter to the DimCustomer, passing through the fact table and there are no relationships between ProductFilter and the fact table. It comes out that the fact table, for our first task, is automatically filtered by the wrong product dimension.

Now, with all these considerations in mind, the algorithm looks like:

• For each customer
  • For each product
  • Check if there are sales
    • Of at least one of the products selected in ProductFilter
    • Anytime before the end of the currently selected year

The key of the solution is to think at the fact table as being a bridge table that implements a many-to-many relationship between customers and products. While sales are not usually intended to implement this relationship, it is clear that this relationship holds. Thus, for each customer, we can filter the fact table showing only the sales of that customer, of the products selected in ProductFilter and of the dates before the end of the current year. If there exists at least one row, this means that that customer has bought one of the ProductFilter products. But, remember that the fact (now bridge) table is already filtered by the wrong product dimension. Thus, we need to remove that filter and replace it with the one we want to use.

Although non very easy, the formula that detects these customers should now be understandable:

FILTER (
    DimCustomer,
    SUMX (
        ProductFilter,
        CALCULATE (
            COUNTROWS (FactInternetSales),
            ALL (FactInternetSales),
            FactInternetSales[CustomerKey] = EARLIER (DimCustomer[CustomerKey]),
            FactInternetSales[ProductKey] = EARLIER (ProductFilter[ProductKey]),
            FILTER (
                ALL (DimTime),
                DimTime[TimeKey] <= MAX (DimTime[TimeKey])
            )
        )
   ) > 0
)

If we use this FILTER inside a CALCULATE, we will be sure that only the customers who have bought one of the ProductFilter products will be visible. Please note that the inner CALCULATE need to call ALL on the fact table to extend the filter context outside of the current year and remove the existing filter based on DimProduct (which is still active at that point). Having done that, we need to re-apply the filters on the customer, on the product (this time based on ProductFilter) and, finally, on the time (the fact table gets filtered due to the relationship with DimTime).

Before continuing to read, please focus on the SUMX (third row) and answer this simple question: “why is it there?”. I ‘m not going to give the answer, use it to check if you got a good insight of the formula behavior. If not, read it again.

For what concerns the HavingProduct, the hard part ends here. The final formula of HavingProduct is simply a COUNTROWS(DISTINCT…)) using this complex filter context:

CALCULATE (
    COUNTROWS (DISTINCT (FactInternetSales[CustomerKey])),
    FILTER (
        ALL (DimTime),
        DimTime[TimeKey] <= MAX (DimTime[TimeKey])
    ),
    FILTER (
        DimCustomer,
        SUMX (
            ProductFilter,
            CALCULATE (
                COUNTROWS (FactInternetSales),
                ALL (FactInternetSales),
                FactInternetSales[CustomerKey] = EARLIER (DimCustomer[CustomerKey]),
                FactInternetSales[ProductKey] = EARLIER (ProductFilter[ProductKey]),
                FILTER (
                    ALL (DimTime),
                    DimTime[TimeKey] <= MAX (DimTime[TimeKey])
                )
            )
       ) > 0
    )
 )

Nevertheless, before continuing, try to clearly understand exactly the different filter contexts that are active in this formula, because we are using completely different filter contexts in different parts of the same formula, computing different values. In fact, the interesting part is understanding in which part of the formula is the DimProduct filter (horizontal slicer) still active.

Now, after you have digested this first formula, it is time to think at the other one: the one implying the “not” part. If you got the insights of the first one, this formula should be pretty easy. It is enough to note that the customers who did not buy any of the products selected in DimProducts, will simply have no sales for those products in the required period. Thus, using again the fact table as a bridge between customers and products, we can apply one more time the same pattern reversing the condition.

The formula for NotHavingProduct is very similar to the previous one, the only difference being in the first argument of the first CALCULATE:

=CALCULATE (
    COUNTROWS (
        FILTER (
            DimCustomer,
            CALCULATE (COUNTROWS (FactInternetSales)) = 0
        )
    ),
    FILTER (
        ALL (DimTime),
        DimTime[TimeKey] <= MAX (DimTime[TimeKey])
    ),
    FILTER (
        DimCustomer,
        SUMX (
            ProductFilter,
            CALCULATE (
                COUNTROWS (FactInternetSales),
                ALL (FactInternetSales),
                FactInternetSales[CustomerKey] = EARLIER (DimCustomer[CustomerKey]),
                FactInternetSales[ProductKey] = EARLIER (ProductFilter[ProductKey]),
                FILTER (
                    ALL (DimTime),
                    DimTime[TimeKey] <= MAX (DimTime[TimeKey])
                )
            )
       ) > 0
    )
)

You should recognize the many-to-many pattern twice in this formula: once for ProductFilter (same as above) and another one in the formula computed by the first CALCULATE. Please note that this time (first part of the formula) we had no need to use SUMX and, again, understanding why is one of the key points of understanding the formula. Here we leverage automatic relationships, before now we had to manually implement a sort of “IN” operator and the SUMX came from that need.

Well, the work is done, the two formulas can be used as measures in a PivotTable and the user is free to slice using any attribute of the two product dimensions, going at the customer level, if he wants to, with the usual blazing speed of DAX. And… if you think that these are long and complex formulas, please think at the length of your average SQL query, is it really shorter than these two formulas? Mine are not.

The post should end here, or it might end with an invitation to our PowerPivot Workshop, as I often do. But, this time, I want to end it in a different way, sharing with you the considerations I made while I was writing the post.

Long before starting to think at this scenario, I already had a good knowledge of how to handle many-to-many relationships with DAX. Nevertheless, I never thought about using fact sales as a bridge table and the “not” part of the problem was completely new to me. I started to work at this data model and reached the final formulas in less than one hour. Looking at the final formula I am sure that no Excel power user will be able to write such a formula by his own, so I agree that this is not self-service BI.

But now we all know that DAX is the foundation of the tabular side of BISM. This means that BI consultants, with good data modeling skills and a good DAX knowledge, will be able to solve such a scenario for their customers in less than one hour, ending up with a working model the customer can play with. Not a prototype… a working model!

I don’t know what are your feelings about DAX, BISM, tabular and multidimensional, but there is a fact that simply cannot be denied: DAX and the tabular data model are cool. Once you get used to think in DAX, formulas can be written at an amazing speed and they end up working at a more amazing speed. I think there is no doubt that customers will be happy to see what DAX can do for their needs, even if they will not be able to write code by themselves. How many of your customers would be happy to pose you such a question and get answered: “ok, give me an hour and I’ll bring you a working Excel workbook”?

To come to an end, I think that the future of BI is brighter than ever, thanks to the effort of all the guys at Microsoft who spend their time figuring out what we will eventually need and who have decided to implement such a wonderful environment for BI, years before we even started thinking at it. I oftentimes blame MS guys when something does not work as expected but I am very happy to say that whenever I am able to solve complex problems so quickly I can think only one thing of them: “You guys rock, DAX rocks. Thank you and keep on with your great work”.