THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

  • The preview of Power BI is now available worldwide #powerbi

    Today Microsoft announced the availability of Power BI preview also outside United States.

    One important thing to note is that also the mobile app for iPad and iPhone are also available worldwide. Jus go in the Power BI Download page and select the app you want. For a PC, I strongly suggest to download the Power BI Designer. You will find another good reason to do that later this week on this blog... but no spoilers by now!

    I suggest you to read the longest recap made by Chris Webb on his blog: there is also some news about public preview of Office 2016. 

  • New article in the From SQL to DAX series: IN and EXISTS functions in #DAX

    I published another article in the “From SQL to DAX” series I launched many months ago on This time, the goal is to analyze DAX alternatives for IN and EXISTS operators that you have in SQL. The name of the article is From SQL to DAX: IN and EXISTS.

    I hope that a syntax corresponding to the SQL operator IN will be available in future versions of DAX, it would simplify the syntax to write for long list of values to include in a selection.

  • Use FIRSTNONBLANK and LASTNONBLANK instead of MIN and MAX #dax

    You are probably used to think to FIRSTNONBLANK and LASTNONBLANK as DAX functions related to time-intelligence calculations (usually for semi-additive measures). However, these functions have a syntax and a behavior that can be useful in other scenarios.

    For example: you might be aware that MIN and MAX cannot be used with text columns. However, you can use FIRSTNONBLANK and LASTNONBLANK instead of MIN and MAX, respectively. In fact, this is the corresponding syntax:

    MIN ( <column> ) = FIRSTNONBLANK ( <column>, 1 )
    MAX ( <column> ) = LASTNONBLANK ( <column>, 1 )

    In my new article Alternative use of FIRSTNONBLANK and LASTNONBLANK, you can find a broader explanation of FIRSTNONBLANK and LASTNONBLANK behavior, with other scenarios where these functions can be useful.

  • New DAX Courses from SQLBI and conference updates #dax #powerpivot #tabular

    In the last two years, I and Alberto Ferrari delivered many courses about Power Pivot, SSAS Tabular, and DAX. We had DAX content in many of the courses, and this created some overlapping and the lack of a clear guidance for studying DAX, regardless of the tool you were using.

    With the upcoming release of Power BI, the number of tools where you can use DAX to write formulas is going to increase. We reviewed the structure of the courses, moving DAX in a separate course (Mastering DAX – 3 days), removing most of the DAX content from the SSAS Tabular course (now is 2 days) and creating a new course about DAX optimization (Optimizing DAX – 2 days).

    Previously, we had an Advanced DAX course that included both DAX (for more complex topics) and optimization. After two years of delivery, we have seen an increased demand for a more complete DAX course (not just an advanced one), and we accumulated a lot of knowledge and content about DAX optimization. A single course of 3 days mixing all together was becoming too dense, so we extended the DAX course and moved the optimization part in a dedicated course. In the meantime, we also adapted the SSAS Tabular Workshop content, reducing its length to 2 days, because all the DAX content has been moved to the Mastering DAX workshop, and there is more time for Analysis Services specific issues, like processing, memory, partitioning, security, and so on.

    This is the new learning path starting from scratch:

    • SSAS Tabular Workshop – covers the Tabular modeling in Microsoft SQL Server Analysis Services 2012/2014 and includes exercises to make practice with DAX and Tabular modeling.
      • Length: 2 days
      • Hands-on-labs: yes
      • Target: BI Architects and BI Developers that already have an experience in building Business Intelligence solutions.
      • Prerequisites: knowledge of SQL language, no previous experience in Analysis Services is required.
    • Mastering DAX Workshop – the more complete and deep course about the DAX language.
      • Length: 3 days
      • Hands-on-labs: yes
      • Target: BI Developers and Advanced Excel Users that want to master the DAX language.
      • Prerequisites: experience in data modeling with Power Pivot, or Power BI, or Analysis Services. A basic knowledge of DAX syntax is suggested even if not strictly required. Attending the SSAS Tabular Workshop or Power Pivot Workshop satisfy the prerequisites.
    • Optimizing DAX Workshop – a course about optimizing queries and measures in the DAX language.
      • Length: 2 days
      • Hands-on-labs: yes
      • Target: BI Developers, Advanced Power BI and Excel Users that want to optimize measures and queries written in DAX.
      • Prerequisites: good experience in writing DAX formulas and queries. Attending the Mastering DAX Workshop should satisfy the prerequisites. We will provide a test to check prerequisites.

    We planned many courses in Europe from April to July, and when possible we scheduled 2 courses in the same week (SSAS Tabular + Mastering DAX, or Mastering DAX + Optimizing DAX), allowing a full immersion for the entire week. We are also planning more online and recorded courses later this year (and other public classroom next Fall). At the moment, the SSAS Tabular Workshop online will still include the DAX content and will be modified when we will provide a Mastering DAX course online.

    Here is a list of planned courses divided by country. We’ll try to participate to user group meetings when we are visiting other countries.

    AT  Austria (Vienna):
    SSAS Tabular - Apr 20-21
    Mastering DAX - Apr 22-24

    NL  The Netherlands (Amsterdam):
    Mastering DAX - Jun 29-Jul 1
    Optimizing DAX - Jul 2-3

    BE  Belgium (Antwerp):
    SSAS Tabular - Jun 8-9
    Mastering DAX - Jun 10-12

    NO  Norway (Oslo):
    Mastering DAX - Apr 13-15
    Optimizing DAX - Apr 16-17
    SSAS Tabular - May 26-27

    DK  Denmark (Copenhagen):
    Mastering DAX - May 18-20
    Optimizing DAX - May 21-22

    SE  Sweden (Stockholm):
    SSAS Tabular - Jun 1-2
    Mastering DAX - Jun 3-5

    FR  France (Paris):
    Mastering DAX - May 27-29

    GB  UK (London):
    Mastering DAX - Jun 8-10
    Optimizing DAX - Jun 11-12

    DE  Germany (Munich):
    SSAS Tabular - May 18-19
    Mastering DAX - May 20-22

      Online (live):
    SSAS Tabular - Apr 27-29
    Power Pivot Full - Jun 3-5

    IE  Ireland (Dublin):
    SSAS Tabular - Jun 16-17
    Introducing DAX - Jun 18

      Online (on-demand):
    Power Pivot Video Courses
    Power BI Video Course
    DAX Video Course

    Talking about user group and conferences conferences, we will also be speaker at the following events:

    As usual, if you attend one of these conferences, don’t be shy, stop us and say hello!

    There is a lot of buzz going on about Power BI – we are waiting the consolidation of certain features before starting to write about it. However, thanks to Project Botticelli, you can already get a very good training with a 10% discount using the coupon SQLBI2015 to access the entire library, including the Power BI Video Course and the DAX Video Course available there.

    Another task for the following months… stay tuned!

  • Customize period comparison in DAX #powerpivot #dax #powerbi

    I have seen many different types of custom calendars implemented in Power Pivot, Tabular, and DAX. I also wrote several articles (week based calendars and time patterns). However, there is a scenario that is hard to manage with the classical approaches, which I discussed in a new article.

    Suppose you want to implement custom comparisons. For example, if you select a range of dates including the Thanksgiving day, how do you compare that period with the previous year? For example, Thanksgiving was in November 27 in 2008 and in November 22 in 2007. Two different weeks also from the calendar point of view. Similar problems when you want to compare Easter days. Not to mention any particular requirements when you want to define an arbitrary correspondence between days of one year with the previous year. You can see an example of this in the ISO YOY Date column in the following screenshot.


    With DAX it’s relatively easy implementing this approach by using the Calendar table. You simply store for each day the corresponding day in the previous year, and write a DAX formula that will use this correspondence to apply the filter for the comparison, regardless of the selected range of days (it will work also when you select weeks, months, or quarters). You can fine more details and download an example in the Custom Year-Over-Year Calculation in DAX article on SQLBI web site.

  • Dynamic ABC Classification in DAX #powerpivot #dax #ssas #tabular

    Less than one year ago I published a pattern about ABC Classification in DAX. Such a pattern was static, so an item (e.g. customer, product) is classified statically when you process a Power Pivot or Tabular model, so filters and slicers querying data do not affect the classification’s results. In the following weeks I discussed with Alberto Ferrari and Gerhard Brueckl about how to create a Dynamic pattern, which considers the selection made on slicers and filters in order to perform the classification. The challenge was to create a relatively efficient implementation, because doing this calculation at query time could have a huge impact.

    After months of work and reviews, I am happy to announce that a few days ago we published the first pattern written by a guest author (Gerhard Brueckl) on the DAX Patterns web site: it is the new Dynamic ABC Classification. This is also the first pattern not included in the DAX Patterns 2015 book, which contains all the others we published before. The idea is to gather future patterns in a future book (this one made a terrific debut – thanks!).

  • Power BI Customer Event on February 24-25, 2015 on Invite Only

    Microsoft recently released a public preview of Power BI Dashboard. This new service is raising interest because of new visualizations and for the openness to developers, thanks to APIs that can be used also for real-time visualizations.

    Are you interested in Power BI services for your organization? Do you want to provide early feedback to Microsoft about the new services in Power BI, with particular regards to mobile story? (iPad app is available now, other platforms will follow).

    Microsoft is organizing a small Power BI Customer Event targeted to BI decision makers in the organization, either IT manager, BI & Analytics managers, and so on – people that can represent their organization and end users’ needs. This internal event is targeted to customers; it is not for developers/ISV/SI. Here are the customers Microsoft is looking for:

    • Companies with 100 or more employees.
    • Already using Power BI, or at least interested in Power BI adoption (if your company will never use cloud services, this is not for you).
    • If you tried Power BI for Office 365 and might have found it does not meet all your company needs, this event can be the right one for you to learn about the new features, provide feedback and get future directions.
    • You are based in EMEA (Europe, Middle East, Africa). The event will be in the Mediterranean area.

    Please consider that:

    • Number of seats is very limited.
    • The event will take place on February 24-25, 2015.
    • You will have to sign an NDA.
    • Microsoft will evaluate requests and only invite companies who corresponds to the profile they are looking for.
    • Thus, there is no guarantee that all requests will be accepted.
    • The event is subject to change based on number of registrants.

    If you fit all the prerequisites and want to submit your request to attend and/or get more detailed information, please write to

  • DAX Patterns 2015 book available now #daxpatterns #dax

    In 2014 we published several articles in the DAX Patterns web site. We’ll continue to publish new patterns in 2015 (the first one later this month!), but in the meantime we created a first printed version of the patterns available: DAX Patterns 2015.DAX Patterns 2015


    It might seem strange, but we really received several requests for a book version of the patterns, so we added two chapters at the beginning, created a cover page (thanks Daniele!) and formatted the content for the paperback and eBook version. Please, remember this: you will not find any new content in this book, every pattern corresponds to an article published on that it’s available for free!

    So, if you like the idea of having a nice printed version, or want an offline copy in your eBook reader, we have created paperback, a Kindle, and an iBook version:

    We set a price that should make us able to cover the cost for editing, but we set a very special launch offer until January 19, 2015: less than 2$ for the eBook version, and 14.50$ for the printed version. Price might vary depending on the country (currency and taxes/VAT). After January 19, the regular price will be 9.99$ for the eBook and 29.00$ for the printed version.

  • The Power BI dashboard in public preview - available also without Office #powerbi

    Microsoft released a new version of Power BI in preview mode, including many new visualizations that are immediately available to all existing subscribers also in production, such as the long waited treemap, combo charts (combining line chart and column chart), and more. These features are available only in HTML5 visualizations, so you can only use the new features online. Microsoft shown these visualizations several times this year (PASS BA Conference in San Jose, and PASS Summit in Seattle), so now this is finally available to anyone. But there is much more!

    Power BI Dashboard is a new service, now in public preview (unfortunately only in United States, not sure about which other countries are supported by now, certainly not Europe), that does not require an Office 365 subscription and, more important, provide a design experience on desktop also without having Excel or Office at all. In other words, there is a separate Microsoft Power BI Designer that enables you to:

    • Import data with Power Query
    • Create relationships between tables
    • Create data visualizations with Power View (running the latest HTML5 version locally in a desktop application)

    This very first release does not include the full data modeling experience we are used to in Power Pivot, so you cannot create calculated columns or measures, but hopefully this will come in the next updates. In this way, you can use Power BI with a separate “data model” environment that is not tied to Excel. You can have an older version of Excel, or no Excel at all, and still design your data model with the Designer.

    The goal of this app by now is to simply offer an offline design experience, and I have to say that performance of data visualization is very good. With the Designer you design data models and reports. Once published in the Power BI web site, you can “consume” data, but you can also modify the report and “pin” objects to a dashboard, so that you can build your own custom dashboard, such as the Retail Analysis Sample you can see below.


    You can create datasets getting data from several SaaS applications, such as Dynamics CRM, Salesforce, GitHub, ZenDesk, SendGrid, and Marketo. You can also connect to live Analysis Services through a new gateway named Power BI Analysis Services Connector and use new native mobile apps for Power BI. Support for iPad should be already available (again, depending on countries, it seems not available in Europe by now). Future support for iPhone and Windows tablets has been already announced.

    This is a very interesting evolution of the Power BI platform and I look forward to use it with real data and real users! Many tutorial videos are available on YouTube.

  • Rename table, columns, and measures in #ssas #tabular without breaking existing #dax

    A feature that many people require in SSAS Tabular is the ability to refactor existing names, doing a correspondent rename in all existing objects in the model. I agree that this is an important feature that should be added in the development environment, but this will help only the development cycle. Once you release a Tabular model, the names you published becomes part of queries created by the users. For example, if you save a pivot table, the objects selected (table, column, and measure names) are all part of the MDX code that is generated automatically by Excel. If you rename something… at the next refresh, Excel will remove renamed objects from the Pivot Table. In less politically correct way, any renaming operation potentially break existing reports.

    Some years ago I heard from a student in a course that they were using translations in SSAS Multidimensional to avoid this issue. They were developing using English, but since users were using other languages (I was in North Europe) they had a decoupling layer between internal model names (in English) and external ones. Any rename operation was completely painless in this way.

    A few days ago, I reminded that and I thought if it was usable for Tabular… and I discovered that you can also use a translation for the same primary language of your model! This is really interesting and deserve to be investigated more. Please, read my article Frictionless Renaming in Tabular Models with Analysis Services and give me your feedback if you have time to test this approach. I am curious to see possible issues of this technique. Thanks!

  • DAX Studio 2.0 finally available! #dax #ssas #tabular #powerpivot

    Darren Gosbell announced on his blog that DAX Studio 2.0 is available. This new release has a completely new user interface, a renewed architecture and also work as a standalone executable without Excel. Yes, you read it right. You can use DAX Studio on a server without having to run it in Excel. You can now connect with Remote Desktop to your SSAS Tabular server and create DAX queries with a decent editor.

    I don’t see any good reason to use SSMS to run DAX queries now. Until yesterday, the zoom in the editor was the only one, but now also this feature is available in DAX Studio. I could spend other lines of this blog post describing the other new features, but I don’t see how this could be more important than doing the only thing you should do at this point: download, install and use it!

  • Power Query now connects to Analysis Services #ssas #mdx #dax

    The last update of Power Query finally supports Analysis Services Multidimensional and Tabular. I waited this version for a very long time, but now it's finally here!

    Chris Webb already wrote an interesting blog post with several useful link and informations. 

    You can connect to both Multidimensional and Tabular, but queries are generated in MDX. For this reason, I consider Multidimensional as a primary source by now. Many improvement can be done for multidimensional, whereas Tabular would benefit from DAX support at first.

    I want to share my feedback and I already look forward to future improvements: please use the comment to this blog post to share your priorities for these features I would like to see.

    Expose Keys

    Each Hierarchy in Multidimensional has one or more levels, corresponding to dimension attributes.
    Each attribute has up to three properties:

    • Key
      • Can be multipart, which means it has one or more columns, each column can have a different data type
    • Name
      • This is always a string. If not defined, it corresponds to the Key, which must have a single column. If the attribute has multipart key, the Name has to be defined in the Multidimensional model
    • Value
      • This property is optional and is not used often. However, it could represent the name in a numeric value and it’s used when the key cannot be used to represent the corresponding value. This property is not shown to the user but can be used in MDX calculations.

    The Name is the one shown in the user interface of power Query. However, when you extract data from a cube, most of the times you need the key column(s) in order to create relationships with other query. For example, a very common scenario is creating three tables, corresponding to two dimensions and a fact table. The keys used to connect the dimension tables to the fact table are mandatory, but are not exposed in the attribute name visible in the user interface of a cube.
    Thus, exposing the Key is very important. Please remember that the Key can be a multipart key, so you might have one or more columns to add.
    If a user request the Key, by default I would show all the columns of a multipart key so he can remove those columns that are not required (but usually you’ll need all of them). Since the Value is not requested so often, I would expose it as a separate menu.

    Surrogate Keys and Other Hidden Attributes

    Depending on the cube design, it would be nice exposing those attributes that are hidden in the cube.
    For example, in a well-designed cube, the model should not expose the surrogate keys in a visible attribute, because this would create a dependency in reports that would break a filter based on a surrogate key in case the relational tables are reprocessed and surrogate keys are regenerated. The general idea is that a surrogate key does not have a semantic meaning. Thus, it shouldn’t be exposed to an end user.
    However, if you are importing several tables from a Multidimensional model, trying to create one table per dimension and one table per fact table (maybe by changing the cardinality, reducing the number of dimensions used), then you should import the surrogate keys too, at least for SCD type 2. Unfortunately, there are no info in the cube model that could help you discriminating between SCD1 and SCD2, so I’m not sure about what could be the best practice in this case. Probably, hidden attributes should be accessible only by advanced users, exposing them by default could be really confusing and I would avoid that.
    This is an area where it’s hard to make a right choice, a compromise is required.

    Related Properties

    An attribute can have other properties related to it. By default, all browsable parent attributes can be considered attribute’s properties. However, when we talk about attribute’s properties we usually refer to the non-browsable attribute. Each non-browsable attribute is just another attribute. Non-browsable attributes are not shown in the list of attributes of a dimension, but they should be available to the user that want to import data for a certain column. The user interface could be designed in several ways for that:

    • Show attribute’s properties in a window from which the user can drag&drop – but maybe confusing – the UI should show only properties existing for a particular attribute and each attribute might have different properties. User interface might be a concern here.
    • Automatically import all the properties of an attribute (maybe by asking confirmation to the end user?) when adding that attribute to a query. Then the user can remove the columns that are not required in the Query.

    Show Formatted Measures

    Sometime it might be useful to import the formatted value of a measure. However, I would not replace the number imported today with the formatted value, because the latter could be a string that does not have any semantic meaning. Providing the option of importing the formatted measure as an additional column in the PowerQuery window would be very welcome, but don’t just replace one with the other.

    Invisible attributes

    Currently, Power Query shows all the attributes of a dimension, which in general is a good thing. However, I would put a checkbox that shows/hides invisible attributes. By default, I would show only visible attributes, because this is what user would be more familiar with. THe “show invisible attributes/columns” should be an advanced view.

    Multiple selection without measures

    if you select attributes from several dimensions without selecting a measure, you obtain as a result the Crossjoin between the tables you selected. In my opinion, this is counterintuitive and useless: I cannot imagine a use case where this would be meaningful. A much better solution would be importing every dimension as a single table, just as you do when you select many tables from SQL Server. It is the user that will handle joins between table, if necessary. My suggestion is to keep the existing behavior (import a single table) only when you import also a measure, even if I would like to be able to import all the dimensions and the set of measures as separate tables in the data model – creating one query for each dimension and one query for each measure group (or for each cube – not sure about the better approach here).

  • What are useful tools and resources for DAX developers? #dax #powerpivot #tabular

    At the last PASS Summit I received an interesting question: is there a list of all the useful (I would say necessary…) tools for DAX developers? My answer was… “no, but this is an interesting topic for a blog post”.

    In the meantime, I thought that a page to keep updated would have been better, and of course an easy-to-remember URL is equally important. So here is the URL where you will find an updated list of tools and resources useful to any DAX developer:

    Of course, feedback are welcome!

  • Don’t use SUMMARIZE to sum your data–or just be careful #dax #ssas #powerpivot

    During the last PASS Summit I and Alberto Ferrari had long discussions at SQL Clinic with some of the developers of our loved tools. Sometime you really have to dig in the internals of certain feature to understand why there are some “unexpected” behaviors for certain functions. One of the discussions was about SUMMARIZE. This function can be very powerful (after all, it’s a way to do a join between related tables…) but also very dangerous, because of the way it implements its logic (especially for the ROLLUP condition).

    The rule of thumb, that we already mentioned in the past, is to use SUMMARIZE only as a way to execute a sort of SELECT DISTINCT, and not to create column to aggregate values. Use ADDCOLUMNS for this other job. We previously mentioned mainly performance reasons for that, but now we have a more complete description of why you should avoid SUMMARIZE for computing aggregations: you might obtain a different result than the one expected. The complete discussion of the issue and of the workarounds is included in the new article All the secrets of SUMMARIZE written by Alberto Ferrari.

  • BI Announcements at PASS Summit 2014 #sqlpass #powerbi #powerpivot

    This morning the PASS Summit 2014 started in Seattle and during the keynote there was several announcements from Microsoft. I’m considering here only the ones about Business Intelligence (you will find other blogs around about SQL Server).

    • In the coming months, Azure SQL Database will get new features such as column-store indexes, which can be very interesting for creating data marts on the cloud
    • Another upcoming feature in SQL Server will be an updateable columns-store index on in-memory tables. Real-time analytics will like this feature.
    • For a store analysis, an interesting demo using Kinect capturing heatmap to display which areas of a shop store have been visited more using Power Map. Just a demo, but it’s an interesting idea and the best big data demo I’ve been so far (something you can implement in the real world using big data technologies without being Twitter or Facebook).
    • New Power BI dashboards: many new visualizations and a new user interface to place data visualizations on a dashboard (similar to the grid you have in DataZen if you know that product)
      • You can connect to your data source from the cloud, without creating a local data model and sending it to the cloud
      • Q&A is integrated in the new user interface – the web site is a domain, it seems not in SharePoint
      • Q&A generates the report in HTML5, no Silverlight signs here
      • The entire editing is done in a web browser – a preview of that was presented at PASS BA Analytics keynote, this seems a more refined version (still not available, however)
      • TreeMap is available as a new visualizations
      • You can upload an Excel file from your disk or from OneDrive – just Excel file, no Power Pivot data model required (it is created on the fly on the cloud?)
      • Combo chart combining line and bar chart visualization available
      • Private preview now, public preview available soon
      • Request access to public preview on
    • Azure ML is publicly available for free in trial mode

    The Power BI story seems the real big news. Combining this with the fact that you can query *existing* on-prem databases on Analysis Services without moving them on the cloud opens up interesting scenarios. Many questions now about when it will be available and how it will be deployed. Interesting times ahead.

This Blog



Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement