Search results matching tags 'DAX' and 'Excel'

DAX Studio for Excel 2013 finally available! #dax #excel #powerpivot #ssas #tabular

sqlbi — Sat, 04 May 2013 05:49:00 GMT

I'm so happy that DAX Studio finally supports Excel 2013! As Darren Gosbell described in his blog, this release has a few internal changes that will better support future enhancements. I will port the code to capture the query plan for a query in this new release, but unfortunately it will require some weeks because I'm traveling a lot in these days.

If you write DAX formulas and queries for PowerPivot or Analysis Services Tabular, DAX Studio is a must have tool: do you really want to live without a DAX Editor? There are a lot of possible improvements and I hope other contributors will give their help to this Codeplex project.

LASTNONBLANK and FIRSTNONBLANK functions work with any column #dax #powerpivot #ssas #tabular

sqlbi — Fri, 19 Apr 2013 10:28:50 GMT

During a PowerPivot Workshop course we received an interesting question from a student: “Can I use LASTNONBLANK (and FIRSTNONBLANK) with a column which is not a date column?”

The reason is that we introduce LASTNONBLANK in the Advanced Time Intelligence module, because its typical use case is on a date column. However, you can use these functions on any column, which raises the question about what happens at that point. The sort order used is the one that depends on the data type of the column. If it is a Text column, the alphabetical sort order is the reference order. If it is a number, then the numeric order is the reference.

What happens if a column has the “Sort By Column” property set to another column? This sort order is *not considered* by LASTNONBLANK and FIRSTNONBLANK functions. Even if a PivotTable shows you data sorted according to Sort by Column property, any DAX formula ignores such a sort order. Thus, be careful writing your DAX queries if you have to do some assumptions on the sort order of a column using DAX functions that rely on sort order, such as LASTNONBLANK and FIRSTNONBLANK.

Execute a #DAX Query on #SSAS #Tabular in #Excel

sqlbi — Thu, 31 Jan 2013 12:54:00 GMT

Apparently Excel does not offer a way to import data in Excel by using a DAX query on Analysis Services. The Data Connection Wizard seems to offers only the ability to create a PivotTable when you connect to Tabular, but not a Table (see the Table option disabled in the next picture).

However, the workaround is to create a connection file and changing it with an editor (such as Notepad) so that you can write your own DAX query (or MDX, too!) and import data in an Excel table.

You can write a step-by-step guide on the article Import Data from Tabular Model in Excel Using a DAX Query I published on SQLBI web site.

DATE function does not support all the dates in DAX by design #powerpivot #tabular #dax

sqlbi — Mon, 05 Nov 2012 12:52:00 GMT

The DATE function in DAX has this simple syntax:

DATE( <year>, <month>, <day> )

If you are like me, you never read the BOL notes that says in a clear way that it supports dates beginning with March 1, 1900. In fact, I was wrongly assuming that it would have supported any date that can be represented in a Date data type in Data Models, so all the dates beginning with January 1, 1900. The funny thing is that in some of the BOL documentation you will find that Date data type supports dates after March 1, 1900 (which seems not including that date, but this is a detail…). But we should not digress. The real issue is that if you try to call the DATE function passing values between January 1 and February 28, 1900, you will see a different day as a result.

evaluate row ( "x", DATE( 1900, 1, 1 ) )

-- return WRONG result
-- [x] 12/31/1899 12:00:00 AM

evaluate row ( "x", DATE( 1901, 2, 29 ) )

-- return WRONG result
-- [x] 2/28/1900 12:00:00 AM

evaluate row ( "x", DATE( 1900, 3, 1 ) )

-- return CORRECT result
-- [x] 3/1/1900 12:00:00 AM

As usual, this is not a bug. It is “by design”. The DATE function works in this way in Excel. And also in Excel it was “by design”. In this case the design is having the same bug of Lotus 1-2-3 that handled 1900 a leap year, even though it isn’t. The first release of Lotus 1-2-3 is dated 1983. I hope many of my readers are younger than that. I tried to open a bug in Connect. Please vote it. I would like if Microsoft changed this type of items from “by design” (as we can expect) to “by genetic disease”. Or by “historical respect”, in order to be more politically correct.

Stock Analysis and Moving Average with PowerPivot

sqlbi — Thu, 27 Jan 2011 17:09:00 GMT

One week ago Alberto Ferrari wrote a post about how to do working days calculation in PowerPivot. You might think this is necessary only for accounting department or something like that… but in reality the same techniques are really useful to implement calculations that might be useful when you want to implement some stock analysis using PowerPivot and Excel!

As you might know, in PowerPivot it is important having a Dates table containing all the days, without exceptions. But when you manage stock data, you don’t have values for holidays and probably you have all the stock data into a single table, for all the stocks. If you want to calculate the moving average for each ticker, you need to get always the same number of days with data, without counting days without stock prices.

Yesterday Alberto wrote a new post that describes how to calculate the moving average with PowerPivot, which is a useful technique whenever you want to calculate time-based indicators used to generate charts like that:

If you want to know more about PowerPivot, don’t miss the opportunity to follow one of the workshops I and Alberto are bringing all over Europe in the next few months.

You can find more info on www.powerpivotworkshop.com. Hope to see you there!

The Microsoft BI Roadmap: BISM, UDM and Beyond

sqlbi — Mon, 15 Nov 2010 13:05:00 GMT

Microsoft recently announced a new roadmap for its BI architecture. The next version of SQL Server, codenamed “Denali”, is going to introduce a new semantic model named BISM (Business Intelligence Semantic Model). Analysis Services will host it and it will be queryable through MDX and DAX. DAX has been introduced in PowerPivot as an expression language, but it will be extended in Denali to provide also query capabilities, but it will keep its nature of a “functional” language.

A more complete description about this roadmap has been published in a blog post made by the SSAS development team. Since we still don’t have a working beta product to test (CTP1 of Denali doesn’t include any new feature in SSAS) I can only make some consideration based on the many information I gathered at PASS Summit 2010 and during private meetings and conversations with members of the SSAS development team. You can of course read other interesting posts from Chris Webb and Teo Lachev to look at some concerns the announcements made at PASS have been raised up in the MS BI Community.

In the long term, the Microsoft strategy is to provide a platform for BI to everyone that will provide the same basic building blocks to any user interested in building a data model for any kind of reporting or analytical needs. Many tried to do the same in the past, and Microsoft tried the same too by introducing UDM (Unified Dimensional Model) several years ago. UDM is great to build models that can be expressed in a multidimensional way, but it might be too complex to be used for simple reporting purposes. Its learning curve requires a certain investment just to start with a simple project. And many developers that are used to SQL simply refuse to approach MDX and UDM just to build a few reports. For these reasons, and also to contrast other vendor’s products, Microsoft is going to introduce a new “big thing”, which is BISM.

To describe BISM, the best thing is looking at PowerPivot today. You can define a model by simply defining tables, relationships and calculations, which are made by using DAX. These concepts are very familiar to both Excel users and developers who are used to relational databases. So, why not using SQL? The reason is that in PowerPivot (and then in BISM) the relationships are part of the model, whereas in a RDBMS a relationship is just a relational constraint. And, most important, DAX is a language that is very simple at the beginning, and that can be learned in a very incremental way. Under the cover, there is a calculation engine called Vertipaq. It is very fast. Faster than any competitor and also faster than columnar indexes that will be implemented in SQL Denali. But BISM will also allow querying an underlying relational database in pass-through mode – in Denali only SQL Server will be supported for this type of real-time usage. Something that is very important to enable BISM as the “unified model” for any reporting need. Finally, to query BISM you can use MDX and, in Denali, also DAX (which will be extended for this purpose), making it easier to express a query over a set of unrelated tables, something that would be nearly impossible in MDX and UDM today.

BISM sounds very promising and the long term strategy is very consistent. What caused many concerns in many of us is the transition strategy. After many discussions and many thoughts, I have this roadmap to share with you:

UDM is here to stay. It is a full multidimensional model that can be used to create complex models with complex calculations. If your business model fits well in a multidimensional model, this is something that can make your life easier
BISM will not replace UDM. At least, it will not replace all the feature of UDM very soon. In the long term, BISM will be able to satisfy all of the requirements of any data analysis and reporting needs. But in its first release it will not have this level of coverage.
BISM will be far better of similar products of other vendors, even if UDM will be more advanced of BISM for very specific requirements. At least, this is the goal for Microsoft. If you look at BISM and UDM in this perspective, it gives much more sense to the overall architecture. BISM will be much more interesting than UDM to customers that are used to other BI technologies, which are less advanced than UDM but good enough for their own requirements.
Existing UDM implementations will continue to work in SSAS. There are no reasons to plan a migration by now. Only after BISM will be released in a version that will be able to satisfy all the existing requirements for your project, than a migration might be considered. But it will not be required, because UDM is not going to be deprecated. The recent case-study of a 12TB cube implemented by Yahoo! should be a good point to support this statement.
New projects starting before the Denali release should be implemented by using UDM. Only in case where UDM doesn’t fit the requirements (i.e. massive leaf-level calculations resulting in low performance) then an early adoption of Denali should be considered.
New projects starting after Denali release should be implemented in BISM if it fits all the requirements. Probably, many projects that wouldn’t have implemented in UDM today (because some SSRS reports on a RDBMS are “good enough”,) might be considered for a BISM implementation. This is probably the key selling point for Microsoft: getting new customers for Analysis Services by offering BISM as a more affordable entry point for a BI solution than UDM. Ideally, this category will contain also all those projects that today would be implemented in UDM just because it is the only “semantic model” that they have today to make a user able to navigate data by using Excel.
In the years to come, as long as the BISM will be always more feature-complete compared to UDM, it will become a viable alternative to UDM. Only time and user adoption will tell if BISM will be able to completely replace UDM. From my point of view, it will require at least three release cycles to reach a point of real competition. It means that we will see new projects starting in UDM at least since 2015. Considering the traditional policy support of Microsoft, any investment made on UDM will be safe at least until 2025/2030. It’s a very long time.

Thus, I’m really confident with the strategy about the server side. I still need to hear more news about the client-side, even if rumors seem better than actual evidence.

Excel is the primary BI client tool. It navigates data by using MDX. It natively supports both UDM and BISM. It seems that there is an important ongoing effort that will see the light in the next release of Excel. I really don’t have any other information here and I can only speculate about some of the former ProClarity features will be implemented inside Excel. What I know is that the resources that are involved in the BI client part of Excel are higher than ever today.
Crescent is the codename for a new ad hoc reporting and data visualization tool that functionally resembles Data Analyzer. Yes, it is completely new, much more graphical, more interactive… but the basic idea is fundamentally the same. It is (like Data Analyzer was) a complementary tool to Excel, and not an alternative one. This tool was supposed to generate queries only in DAX. This would exclude the possibility of querying an existing UDM model. However, I would wait a few weeks for an official statement by Microsoft about Crescent support of existing UDM models.
Reporting Services and Report Builder should support BISM in a native way. Today it already supports UDM through MDX. It should be able to query BISM in MDX as well, but supporting DAX should be considered to make life easier to those developers who are not used to MDX. I don’t have information about this kind of support, but it should be the natural evolution.
I haven’t heard any news about PerformancePoint, but I can imagine it will have BISM support as a natural evolution as well. However, because PerformancePoint should be aligned with Excel, we should see a new version of Excel and PerformancePoint only in 2013, I suppose. However, MDX will be available to query BISM from PerformancePoint, in case a Service Pack with BISM support will not be released in time.

As you can see, we are just at the beginning of a major wave of innovation in the BI space. In this case, the innovation start from the Self-service BI and will grow-up until it will reach the corporate BI at a more pervasive level. A key point of the Microsoft strategy is the Vertipaq engine. Only in these days I started to understand how much disruptive this technology can be. I know very well that several UDM cubes in these days run on server that have more RAM than the cube size. Not every project is inside these boundaries, but many are. And with Vertipaq compression, the bar is simply higher.

Finally, these are my advices for the current and future BI developments:

If you are a company who want to start a BI project, don’t wait and go to UDM now.
If you are a BI firm or consultant, start your training for DAX by using PowerPivot. It is an excellent tool for prototyping and you can use it to train yourself and to prepare proof of concepts of BI models for your customers. Then continue the implementation using UDM by now. Commercial: my recent book has several chapters about DAX, too.
When a feature-complete CTP of Denali will be available later next year (maybe not very soon) start to explore it to understand its capabilities and whether they can fit your requirements or not.
Once BISM will reach a feature set that satisfy your requirements for a new project, start to consider it because development time might be considerably lower and skills required could be easier to build, especially if your data model is not too complex.
Whatever you do in your professional life, if you are reading this blog you have to learn DAX. You can start today and my recent book can be a good start point also to cover more advanced data models and calculations.

A final thought is about MDX. I know that mastering MDX is hard, but I cannot say that DAX is so simpler. Yes, it is simpler at the beginning, but for more complex calculations, the required DAX expression might be more complex than the corresponding MDX one. Coming from a relational background (SQL) DAX is more intuitive at the beginning, but coming from an MDX background it is easier to learn the more advanced part of DAX that allows you to create the more complex and powerful expressions that solve real-world complex problems in a very efficient way. Thus, also your investments in MDX are preserved. Your MDX queries will still run and you will still be able to write new MDX queries. But the more important asset you have is the MDX knowledge and understanding, which puts you in pole position to really master DAX too, even if a further study will still be required.

How CALCULATE works in DAX

sqlbi — Sun, 03 Jan 2010 07:39:51 GMT

The CALCULATE function in DAX is the magic key for many calculations we can do in DAX. However, it is not pretty intuitive how it works and I spent a lot of time trying to understand how it can be used.

First of all, this is the syntax.

CALCULATE( <expression>, <filter1>, <filter2>… )

The expression that we put in the first parameter has to be evaluated to return the result (that is a value, not a table). For this reason, the expression is usually an aggregation function like SUM, MIN, MAX, COUNTROWS and so on.

This expression is evaluated in a context that is modified by the filters in the following parameters. A key point is that these filters can both enlarge and restrict the current context of evaluation. Let’s try to understand what it means by considering a few examples. The following table is the one we import in PowerPivot in a table named Demo.

If we project the count of Rows in a PivotTable putting the Name on the Rows, we get the following result:

Now, we might have the need to calculate a measure which is not affected by the selection of the Name or, in other words, which always calculate the context for all the names. Thus, we define this calculated measure:

CountAllNames = CALCULATE( COUNTROWS('Demo'),
ALL( 'Demo'[Name] ) )

We obtain the CountAllNames column in the PivotTable that always returns the number of all the rows of the Demo table, without considering the filter on the Name.

However, if we add Color attribute to the rows of the PivotTable, the CountAllNames is filtered by that attribute too. In fact, for each color, we have 2 rows for a Name (see Count of Rows column) and 6 rows considering all the names (see CountAllNames column).

Now, we can also add a Boolean expression as a filter of the CALCULATE function. For example, we might filter just the Car transport.

CountAllNamesCar = CALCULATE( COUNTROWS('Demo'),
ALL( 'Demo'[Name] ),
'Demo'[Transport] = "Car" )

In this case, we will reduce the CountAllNamesCar column for a color of Emily to 3, because the number of rows with color Green and Car transport are 3.

At this point we might wonder whether the Calculate filter parameters are enlarging or restricting the context of evaluation. The rule is the following one.

If the current context has a filter on a column of a PowerPivot table (which is a selection of a PivotTable, regardless it is a slicer, a report filter or a row/column selection), any reference for that column in one or more filter parameters of the Calculate function replaces the existing context. Then, the filters specified in the CALCULATE parameters are combined together like they were in an AND condition of a WHERE clause of a SQL SELECT statement.

For instance, consider a filter on the Color green using a Boolean expression in the CALCULATE function:

ColorGreen = CALCULATE( COUNTROWS('Demo'),
'Demo'[Color] = "Green" )

A Boolean expression used as a filter parameter in a CALCULATE function corresponds to an equivalent FILTER expression that operates on all the values of a column (for this reason, you can only a single column can be used in a Boolean expression that is used as a table filter expression):

ColorGreen = CALCULATE( COUNTROWS('Demo'),
FILTER( ALL('Demo'[Color]), 'Demo'[Color] = "Green" ) )

We obtain that ColorGreen column always filters by color Green and each Name has only 2 rows with color Green.

Any filter expression in a CALCULATE statement overrides the existing selection of the PivotTable for the columns it contains. In the previous rule we highlighted the “reference” definition, because the FILTER that is internally used in place of the Boolean expression uses a FILTER expression that returns a set of values for the Color column. Thus, the existing selection for the color (the color is in fact specified in the rows of the PivotTable) is overridden by our filter and only green rows in the source table are considered to calculate the ColorGreen measure value. The true reason we lose the current selection on the color attribute is that the ALL( Demo[Color] ) expression returns a set of all the color values and ignores the existing selection.

If we don’t want to lose the existing selection of the PivotTable (that means that we don’t want to lose the existing filters on the calculation context), we can simply use in the FILTER expression a function that doesn’t ignore the existing selection. Instead of using the ALL( Demo[Color] ) expression as the source of the filter, we can use the VALUES( Demo[Color] ) expression, which keeps existing selections and returns the values still available in the color attribute.

In fact, if we use the following calculated measure:

ColorGreen = CALCULATE( COUNTROWS('Demo'),
FILTER( VALUES('Demo'[Color]), 'Demo'[Color] = "Green" ) )

We obtain as a result that the Color filter in the PivotTable is still active, and returns no rows for all the colors but green.

We can see that the FILTER expression in a CALCULATE function always replaces the previous context for the referenced columns. However, we can save the existing context by using an expression which uses the existing context and further restricts the members we want to consider for one or more columns. And this is what we have done using VALUES formula instead of ALL as the first parameter of the FILTER call.

Thanks to Marius Dumitru, the various combination of FILTER, ALL, VALUES in a CALCULATE statement can be summarized in this way.

= CALCULATE(
    COUNTROWS('Demo'),
    FILTER( ALL('Demo'[Color]), 'Demo'[Color] = "Green" ) )
    -- ignores/replaces existing Color filters and sets a filter on Green

= CALCULATE(
    COUNTROWS('Demo'),
    FILTER( VALUES('Demo'[Color]), 'Demo'[Color] = "Green" ) )
    -- keeps existing Color filters and adds a further filter on Green

= CALCULATE(
    COUNTROWS('Demo'),
    FILTER( ALL('Demo'[Color]), 'Demo'[Color] = "Green" ),
    VALUES('Demo'[Color]) )
    -- same as the previous expression (keeps existing Color filters)
    -- NOTE: the first filter would consider all the colors, but the second
    -- expression (VALUES) only consider the current selection and
    -- the two filters will be considered using an AND condition, thus

= CALCULATE(
    COUNTROWS('Demo'),
    FILTER( ALL('Demo'), 'Demo'[Color] = "Green" ) )
    -- ignores/replaces filters on all Demo columns,
    -- and sets a filter on all columns (Name, Transport etc., not just Color)
    -- with rows that meet the condition

Finally, some words of caution have to be spent to the first parameter we pass to the FILTER function. If we consider this expression:

ColorGreen = CALCULATE( COUNTROWS('Demo'),
FILTER( 'Demo', 'Demo'[Color] = "Green" ) )

We pass the whole Demo table to the FILTER condition, which results a filter of the current context with all the columns! In this way we apply a restrictions on the color green and we get the same result as before (no rows for all the colors but green, the selection of color of the PivotTable is still applied) but, remember, the FILTER is returning ALL the rows. What does it mean?

Well, consider a further selection on the PivotTable where the Transportation attribute is filtered by Bike. This is the result using the ColorGreen definition we have just defined.

Now, let’s add another filter to the CALCULATE function, filtering also the rows with Trasport equals to Car.

CarGreen = CALCULATE( COUNTROWS('Demo'),
FILTER( 'Demo', 'Demo'[Color] = "Green"),
'Demo'[Transport] = "Car" )

which, as we have seen before, corresponds to:

CarGreen = CALCULATE(
               COUNTROWS('Demo'),
               FILTER( 'Demo', 'Demo'[Color] = "Green"),
               FILTER( ALL( 'Demo'[Transport] ), 'Demo'[Transport] = "Car" ) )

The results is that the filter for Bike defined in the PivotTable plus the filter for Car defined in the CarGreen measure returns no rows at all in the CarGreen result. If we think about it, why this happens is not very intuitive at this point!

Our filter on the Transport column is actually restricting the current selection and is not replacing it! But, wait, if we write just the filter on Transport, without the filter on Color, we have:

TransportCar = CALCULATE( COUNTROWS('Demo'),
'Demo'[Transport] = "Car" )

that produces the following result, which replaces the Bike selection of the PivotTable in the TransportCar column!

This last calculation (TransportCar) is simple to explain, because it has the same behavior we have seen before with the first ColorGreen calculated measure, where the filter of the color green replaced any existing color selection in the PivotTable. The difference in the CarGreen calculation is the other filter parameter, which returns all the columns from the Demo table. Consider the highlighted filter in the CarGreen formula:

CarGreen = CALCULATE(
              COUNTROWS('Demo'),
              FILTER( 'Demo', 'Demo'[Color] = "Green"),
              FILTER( ALL( 'Demo'[Transport] ), 'Demo'[Transport] = "Car" ) )

The filter on color green returns all the columns of the current context. If we consider the corresponding rows for the cell B5 of the PivotTable (Emily, Green, Bike), this is just one row (the yellow one), and this row has the Bike value for the Transport attribute. When we apply the second filter, we have a single value for the attribute Transport, which is Car. At this point, the intersection between those two sets of Transport (one is only Bike, the other is only Car) is an empty set. Thus, the result for CarGreen measure is empty, because there are no corresponding rows for the selection made.

This can be tricky, but we finally have this behavior.

The CALCULATE function applies a calculation (the first parameter) for each cell, considering the resulting context by applying the filters (the second and following parameters) to the current context.
Each filter can have values for one or more columns.
Each column is computed individually in the filters expressions of the CALCULATE function
If a column value is specified in at least one filter, it replaces the selection of the current context for that column.
If a filter expression returns more columns, each one has its own independent set of values in the final calculation context definition
If a column is specified in more filters, the resulting values are the intersection of these set of values (for that column).
After all the filters have been evaluated, the intersection of the column values determines the calculation context for the expression passed as the first parameter to the CALCULATE function

Despite its complexity, this calculation is pretty fast. The key point is to understand all the side effects we have when a filter returns more columns than those we specified in the filter condition itself, which is something we have to consider carefully each time we use one or more FILTER functions inside a CALCULATE expression.