ABC Classification – Dynamic
The dynamic version of the ABC Classification pattern is an extension of the Dynamic Segm…
The dynamic version of the ABC Classification pattern is an extension of the Dynamic Segm…
This article describes how to implement a custom year-over-year calculation in DAX based on arbitrary associations between different periods. As an example, we describe the comparison of the 53rd week in a ISO Calendar.
The names used for table, columns, and measures in DAX cannot be renamed without breaking existing DAX formulas. This article describes how to leverage translations to decouple user interface from internal model names in Analysis Services Tabular.
This page contains descriptions and links to necessary tools and resources for DAX and Tabular developers.
SUMMARIZE is a function that looks quite simple, but its functionality hides some secrets that might surprise even seasoned DAX coders. In this article, we analyze the behavior of SUMMARIZE, in order to completely describe its semantic. The final advice might surprise you: we will suggest to avoid the use of SUMMARIZE in your code, unless you know exactly which of the functionalities of SUMMARIZE are safe to use.
The autoexist feature used by MDX queries sent to an Analysis Services or Power Pivot data models can have different behaviors depending on the normalization level. This article describes these differences and provides best practices to avoid unwanted results.
The GROUP BY condition of a SQL statement is natively implemented by SUMMARIZE in DAX. This article shows how to use SUMMARIZE and an alternative syntax to group data.
The WHERE condition of a SQL statement has two alternatives in DAX: FILTER and CALCULATETABLE. In this article we explore the differences between them, providing a few best practices in their use.
The DIVIDE function in DAX is usually faster to avoid division-by-zero errors than the simple division operator. However, there are exceptions to this rule, described in this article through a simple performance analysis.
Computing the rolling 12-month average in DAX looks like a simple task, but it hides some complexity. This article explains how to write the best formula avoiding common pitfalls using time intelligence functions.
The new forecasting capabilities in Power View for Office 365 has certain requirements for the column used in x-axis. This article provides a guide to make your data model compatible with the new forecasting feature.
In DAX there are different ways to test whether a table is empty. This test can be used in complex DAX expressions and this short article briefly discuss what are the suggested approaches from a performance perspective.
A common best practice is to use CALCULATETABLE instead of FILTER for performance reason. This article explore the reasons of that and explain when FILTER might be better than CALCULATETABLE.
How do you get the last process date for an SSAS Tabular Database? This article shows the techniques you can use and explains how to interpret the information available.
Creating a hierarchy in a Power Pivot or Tabular data model is very easy, but you might experience performance issues if the hierarchy is not “natural”. This article explains how to create hierarchies avoiding these problems.
Values such as inventory and balance account, usually calculated from a snapshot table, require the use of semi-additive measures. In Multidimensional you have specific aggregation types, like LastChild and LastNonEmpty. In PowerPivot and Tabular you use DAX, which is flexible enough to implement any calculation, as described in this article.
In a Power Pivot or Tabular model that has inactive relationships, you can use the USERELATIONSHIP function to apply an inactive relationship to a particular DAX expression. Its usage is simple in a measure, but you might consider alternative syntax in a calculated columns, as it is explained in this article.
In the traditional star schema design of a Data Mart, you replace a missing, unknown or wrong date in the fact table with a dummy value in the Date dimension table. In Tabular, handling a Date table requires an existing date and you cannot use a NULL date in a Date table. This article describes how to apply the NULL replacement in the fact table using views, without altering the relational structure of the Data Mart.
With DAX you can calculate the sales of the first, second and third year of a new customer without any ETL. In this article you see how to implement this calculation with good performance.
You can implement the calculation of stock inventory over time in several ways in DAX. The xVelocity technology in Power Pivot and Analysis Services Tabular is so fast that you might considerboth dynamic calculation and snapshot based calculation
When you create a Tabular model in Microsoft SQL Server Analysis Services (SSAS), you have to define all of the measures that the user will be able to use. All the measures defined in the measure grid of a table are displayed within a measure group having the same name of the table itself.
Grouping transactions by customers’ age requires a computation made row by row at transaction level. DAX offers an elegant solution using calculated columns, which is described in this article.
This article contains a short checklist of what you have to do in order to optimize the memory used by a data model in PowerPivot or in Analysis Services Tabular, including links to tools and resources that can help you in this task.
The DAX language provides several Time Intelligence functions that simplify writing calculations such as year-to-date (YTD), year-over-year (YOY) and so on. However, if you have special calendar structure such as 4-4-5 weeks’ calendar, you need to write your custom time intelligence calculation. In this article, you will learn how to write the required DAX formulas.
When you use Excel to connect to a Tabular database in Analysis Services, you get a PivotTable as a result. In this article, you learn how to import data in a table writing a DAX query than runs on Analysis Services.
In PowerPivot for Excel 2010 it is possible to import an Excel table in a PowerPivot data model by using the Linked Table feature. Excel 2013 expands this capability by
If you have a PowerPivot workbook created with Excel 2010, in order to navigate and/or update the data model with Excel 2013 you need to upgrade the workbook.
With the modern editors that have automatic code formatting features, each one has its own code formatting style. Usually there are a few “common rules” for code formatting depending on the language, but there was nothing for DAX
Several Time Intelligence functions in DAX use a <dates> argument, which has a special behavior because it can be expressed by using both a column reference syntax, a table expression or a Boolean expression.
In this article we see a function to enable parallelism in commands sent to Analysis Services by using the AMO library even when you need to use the change the QueryBinding. This function is useful because the current AMO library (up to SQL Server 2012) cannot correctly generate the required XMLA code if multiple commands using QueryBinding are executed within the same batch.
Everyone using DAX is probably used to SQL query language. Because of the similarities between the Tabular data modeling and the relational data modeling, there is the expectation that you can do the same operations that SQL allows. However, in its current implementation DAX does not permit all the operations that you can do in SQL. A few of the limitations are caused by the lack of equivalent syntax, others depend on a counterintuitive behavior of the xVelocity in-memory engine when extension columns are involved in a query.
PowerPivot and Analysis Services 2012 Tabular do not support many-to-many (M2M) relationship directly in the data model. However, you can obtain the desired result from a many-to-many relationship by writing a DAX expression. For example, consider the classical M2M relationship between bank accounts and customers.
DAX is the new language used by PowerPivot and Analysis Services in Tabular mode and it resembles the syntax of Excel formula and it can be considered a functional language. You do not have iterative statements, but you can run iterative functions like, for example, SUMX and FILTER. The most important functions in DAX are CALCULATE and CALCULATETABLE, and these functions are also the most complex to master, because of the many operations they do.
Hierarchy handling in DAX is not very easy, due to the fact that hierarchies, unlike it was in MDX, are not first-class citizens in the DAX world. While hierarchies can be easily defined in the data model, there are no DAX functions that let you access, for example, the parent of the CurrentMember. Well, to tell the truth, there is no concept of CurrentMember in DAX either.
In Analysis Services 2012 you can process a table in a Tabular model by several ways: you can process the whole table, you can split the table in several partitions and process a single partition, you can merge partitions and you can incrementally process a single partition by using ProcessAdd, which is the topic of this article.
Memory settings in Analysis Services might be an important tuning of a correct setup, both for production and developer machines. Analysis Services 2012 can be installed in different ways and the new Tabular instance has new memory settings that are important to know.
VertiPaq is the internal column-based database engine used by PowerPivot and BISM Tabular models. High cardinality columns might be the more expensive parts of a table. If you cannot remove a high cardinality column from a table, by using the techniques described in this article you might optimize its storage saving up to more than 90% of original space.
PowerPivot and BISM Tabular handle relationships between tables by using a single column that requires to be a unique column in the lookup table. In this article you will see how to handle relationships between tables that have different granularities, which is a typical scenario whenever you have a monthly budget table and a daily level sales table.
If you want to execute a DAX query from your code to a Tabular model, you might think that an OLE DB connection should be the right way to get your data. It works, but if you your code is written in .NET a better solution is using the ADOMD.NET library. In this article you will see why ADOMD.NET is better in this scenario.
When you design a data model for Tabular you should pay attention to a though topic, which is that of circular dependencies in formulas. It is very important to learn how to handle circular dependencies now because in SQL 2012 (and in PowerPivot 2012) there will be a stronger validation of circular dependencies. Some of the checks described in this article have been introduced with the release RC0 of SQL Server 2012 and were not present before.
In DAX string comparison requires you more attention than in SQL, for several reasons: DAX doesn’t offer the same set of features you have in SQL, a few text comparison functions in DAX are only case-sensitive and others only case-insensitive, and performance might have a relevant impact in your query according to the comparison technique you use.
Using DAX as a query language you will lack a fundamental feature like projection. In fact, DAX allows you add columns to an existing table, but you cannot simply remove some column from a table. In order to do that, you have to use SUMMARIZE, or you can use ADDCOLUMNS starting from a column that you want to include in the query output and that has to be unique for each row of the result.
Calculating a ratio in DAX is relatively simple in case the underlying data model is a star schema, but you have to consider additional complexities whenever you have a more normalized model, even just a snowflake schema. In this article, you will see how to perform the right computation.
Sorting a column by using another column is one of the nice features of the new version of PowerPivot introduced with SQL Server 2012 (we call it PowerPivot v2, but it really v11 because it is aligned with SQL Server). For example, this allows you to sort the Month name in the right order (January, February, …) instead than in alphabetical order. Usually, you should have a month number column that allows you to correctly sort the month name. However, sometime it is necessary to build a calculated column in order to provide the right sort, as you will see in this article.
We presented a few session at SQLBits and the video of these sessions are available.
This is the list of sessions available from SQLBits 8 and SQLBits 9 – just click the link on the title and jump to the session page with video, slide deck and demo!
A BISM Tabular model can be queried by using both MDX and DAX. These two different languages rely on different modeling concepts, because MDX has a semantic based on dimensions, attributes, hierarchies and measures, whereas DAX only knows table and columns. In order to understand these differences and how to find a corresponding behavior in DAX and assuming you already have MDX knowledge, I’d like to examine how you can convert an MDX query in DAX.
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.
Having read this question on the mdsn blogs, I investigated on the KEEPFILTERS function and, after having learned it, it is now time to write about it. Moreover, before start to write about it, I need to thank the dev team of SSAS and Marco Russo who helped me understanding this complex topic.
In many workshops and sessions about PowerPivot the most frequently asked question has always been: how can I automate the PowerPivot Data Refresh in Excel? I talked about that a few weeks ago.
In PowerPivot you can import data from several sources and it is often the case that you import a DateTime column from a database. From an analytical point of view, you usually make analysis over dates (day/month/year/quarter/week) and over time (hour/minute) but to do that it is often useful to have two independent set of attributes: one for the date (the Calendar table) and one for time (for those businesses where this element is relevant).
In an Analysis Services cube you can define drillthrough actions on a cube. This feature has been working since SQL 2005 and now it will be available in both BISM Multidimensional and BISM Tabular in the next version of SQL Server “Denali”.
Year-To-Date and Difference-Over-Previos-Year (or Year-Over-Year Growth) are among the most required features of any user. Some OLAP client (like ProClarity) offers features that try to solve this problem client-side, but I don’t like this approach given that you might have a server-side feature doing that (so you don’t discriminate Excel users).