SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus
In the last months I assisted many companies implementing solutions based on Analysis Services Tabular. There is not so much difference between the versions 2012 and 2014, because SQL Server 2014 didn’t introduce new features to the BI services. Thus, my considerations are valid for both.
One issue observed in different cases was a general performance degradation after a few days of work. Restarting the msmdsrv.exe service was enough to restore normal performance. The problem might affect both query and process operations. Microsoft released a hotfix (KB2976861) that mitigates the problem for slowness of full process, but it is not something that completely solve the problem.
The real reason of the issue is the fragmentation of the memory heap. Analysis Services can use its own heap algorithm, or the standard Windows one. It seems that the workload generated by Tabular creating objects of a dynamic size is an issue for the Windows Low-Fragmentation Heap, which is the default setting in Analysis Services (because of a better scalability).
In the Heap Memory Settings for Analysis Services Tabular 2012 / 2014 article on SQLBI you can find a complete description of the settings to control heap memory used by Analysis Services. If default values produces the symptoms described above, then consider changing them with the suggestions included in the article.
The last update of Power BI Designer allows you to create measures (not calculated columns yet). Download the new version of Power BI Designer and you will see the New Measure button. The editor is much better than anything you have seen in Excel 2010/2013, but it can be improved (larger real estate is the first request).
The real important fact is another. You have a new version of DAX in your hands. It is not just because you have a some new functions or because the engine is faster (way faster). No, the big change (which is not a breaking change, but just a new feature) are "variables". I'm not sure this is the right name, but it is the intuitive name you give to a feature where you use the keyword VAR before specifying an identifier. What are we talking about? Look at this example:
TotalQuantity = SUM ( Sales[Quantity] )
TotalQuantity > 1000,
TotalQuantity * 0.95,
You can assign an expression to an identifier within a larger DAX expression. The evaluation context is the one where you write the definition. You can avoid repeating the same expression multiple times within the same measure, and you can simplify the writing of code avoiding too many nested evaluations and avoid using EARLIER in most of the cases. For example, consider this expression
= SUMX ( Sales, Sales[Date] <= EARLIER ( Sales[Date] ) )
Now you can write:
CurrentDate = Sales[Date]
SUMX ( Sales, Sales[Date] <= CurrentDate )
Which is longer, but way more readable.
A longer and more detailed article about the new VAR / RETURN syntax in DAX is available at Variables in DAX on SQLBI.
In the last few weeks I and Teo Lachev took the ownership of DAX Editor code. It is an add-in for Visual Studio that allows you to edit DAX measures in a text editor instead of using the Measure grid. It also provides other features (such as editing measures of an online Tabular database and performing a few queries). The user interface is not so user-friendly, so I suggest you to carefully read the documentation in order to understand how you can edit DAX measures in your own Visual Studio project.
So, what are the new features we added in the last weeks?
We also re-published the DAX Editor in Visual Studio Extension Gallery. If you had a previous version, please uninstall it and then install the new one. We had to change the internal VSIX ID so the upgrade to this new version doesn’t work automatically. However, once you install this new build, upgrade should be easier in future builds.
In the last months, I’ve been trying to suggest a direction for Power BI, but I start to realize that in these days Microsoft is really prioritizing features based on customer feedback. This can make things harder for a new idea to be prioritized, because nobody ask for something completely new. A few days ago, Jamie Thompson asked support for PBIX files saved on OneDrive. I think it’s a good idea, Excel is already supported there, and you can add your vote here: http://support.powerbi.com/forums/265200-power-bi/suggestions/7259274-view-pbix-files-on-onedrive-onedrive-4-business
But I want to ask community support for requesting a feature in Power BI Designer that would help thousands of ISV and millions of users. So, Microsoft, please:
Create a Power BI Designer API and support connecting to PBIX from Excel
You can click on the link and vote it. If you are in a hurry, just to these 2 clicks and receive a big thank you! If you have more time, Please, let me elaborate it – I will include the description I published on UserVoice adding a few comments.
Power BI Designer saves a local PBIX file, which can be a file to export data and data model – in other words, it’s a format containing a complete semantic model. All the applications that today export data in several formats (CSV, Excel, XML), might provide a richer semantic model exporting a PBIX file.
Many ISV/SI that have OLTP and other applications that stores data in some database, usually struggle to offer a compelling BI story to their customers. The smaller they are, the more they feel this pressure because probably the effort they can put in their custom software is minimal.
Today these ISV/SI integrate their solution with external vendor technologies (QlikView is a common choice here). However, the cost of such a solution for the end user is not always appealing, and for this reason the MS partner ecosystem always look for components (charts and pivot tables) to integrate in their solutions.
Providing them an easy and inexpensive way to produce PBIX files “ready to use” straight from their product/solution would provide several benefits:
- Customers would have something ready to be uploaded to Power BI service
- ISV/SI would be able to provide a BI solution integrated with MS ecosystem
- ISV/SI can implement solutions like “send a PBIX file via mail every week to all the agents including only the data of their prospects/customers”
- Today they already do that using the .CUB format, which can be consumed by both Excel and custom applications
- Microsoft would increase the number of Power BI users very quickly - Small ISV/SI would be able to implement such integration very fast
What I propose to do is, in descending order of importance:
- Support Power BI Designer as a local engine with an API that can be used by anyone and officially support local connections by other programs (starting from Excel)
- The API should provide the ability to create a data model and to populate it with data by just using API, without any manual interaction
- Providing the ability to connect from other clients (today it is possible but not officially supported) would increase the adoption.
- Document and “open” the PBIX file, so that it can be generated by anyone
- I think that this is easy for the data model, but not for the data.
- But without the data, this model would be not so useful, requiring a manual refresh to be populated.
- Move Power BI Designer to open source
- Not really a priority in my opinion, but if the first two wouldn’t be possible, this one could be ok
If you think this is a good idea, here is the point. The top ideas are SQL Server on-prem and SQL Server Analysis Services cubes. Very important features. They have more than 1,000 votes. We need to go there to receive attention from Microsoft. Thus, your vote is not enough. Please, forward the message, convince other people to vote, add your comments, talk very loud.
Microsoft is prioritizing cloud services, but getting quick and large adoption for small databases of thousands of applications, each one with hundreds of users, means generating a huge volume of data models ready to use. Yes, we need DAX in Power BI Designer in order to make it useful, but we know it is already in the roadmap. Microsoft released yesterday support for Google Analytics, and by now it works only in Power BI Designer. Working in a desktop app has its own advantages. I never had a so powerful tool to navigate in Google Analytics data. I’m waiting for DAX. But being able to generate a PBIX file from within an application would be a great leverage to Power BI adoption also for people who are not ready to query data or create a new data model, but that want to analyze their data.
And, of course, if you think it’s a bad idea… comments are open, I’d like to hear other point of views.
After the initial release of DAX Studio 2.0, in December 2014, I started working on a few features to analyze performance of DAX queries. Darren Gosbell implemented many other features, and Daniele Perilli helped us with the graphics of the UI. We certainly can do more, but I think that the current feature set of the new release is good enough to use DAX Studio only during your daily job with DAX, without opening SQL Server Management Studio (SSMS) and SQL Profiler anymore.
For this reason, I’m so proud to announce the release of DAX Studio 2.1, just after Darren Gosbell also officially announcement on his blog.
I created a very short video (less than 3 minutes) to show the new features for analyzing performance of DAX queries with DAX Studio 2.1.
Here is the list of the new features:
- Improved Server Timings Tab
- Show storage engine query events
- Sort events by CPU and Duration
- Filter events by type (Cache, Internal)
- Show formula engine and storage engine timings
- Different layouts for complete display of storage engine query text
- Cleanup of storage engine query text
- Improved Query Plan Tab
- Display of physical query plan in a list that highlights number of records processed
- Separation of physical query plan and logical query plan in two different lists
- Save query plan and server timings with DAX query
- When you save a DAX file, if you enabled Server Timings and Query Plan panes, two other files are created with the same name and a different extensions (.dax.queryPlans and .da.serverTimings).
- When you load a DAX file, if the other two files exist in the same directory, they are loaded and the Server Timings and Query Plan panes display these information
- This feature is useful to get useful information from a remote user asking help without having to connect remotely or to download the entire database.
- Metadata Search: find measure, column, and table names in the entire metadata tree
- Query text search & replace
- Integration with DAX Formatter for automatic query layout (plus static syntax check without metadata)
- Accept MDX queries
- You can copy a query from a pivot table in Excel using OLAP PivotTable Extensions and run it in DAX Studio
- By using DEFINE MEASURE at the beginning of the query, you can define new DAX measures and see the effects in the same MDX query
- Most important, you can see the profiler events for executing DAX measures in a MDX query, which is very important for performance tuning
- Connect to Power BI Designer
- Just open Power BI Designer and then open DAX Studio
- You can choose to connect to Power BI Designer
- At the moment, we don’t support multiple instances of Power BI Designer and if you close Power BI Designer, you lose the connection without any warning.
There are also many other bug fixes and small improvements. In this release, we still didn’t solved an issue we have in getting trace events when connected to Power Pivot, but debugging work nicely when using Power BI Designer. Yes, you read it correctly!
In the last weeks of beta testing, I never used SQL Server Management Studio anymore to debug DAX measures and queries. In particular, I no longer open the SQL Profiler, unless I want to capture a query generated by other programs (this is a feature we might integrate in DAX Studio in the future, sniffing all profiler events and debug queries run by other processes).
Remember: DAX Studio is free and open source. If you want to contribute, post and vote bugs and feature requests on CodePlex. If you are also a .NET Developer, join us writing other features. There have been more than 2500 downloads in 3 months since 2.0 release. It’s a good number, but I expect to reach such a number much sooner with this release. Spread the word!
I published another article in the “From SQL to DAX” series I launched many months ago on www.sqlbi.com. 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.
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:
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.
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.
Talking about user group and conferences conferences, we will also be speaker at the following events:
- SQL Rally Nordic, March 2-4, 2015 – Copenhagen (Denmark)
- SQLBits IV, March 4-7, 2015 – London (United Kingdom)
- PASS Business Analytics Conference 2015, April 20-22, 2015 – Santa Clara, CA (United States)
- Microsoft Ignite 2015, May 4-8, 2015 - Chicago, IL (United States)
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!
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.
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!).
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 email@example.com.
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.
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 www.daxpatterns.com 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.
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.
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!