Search results matching tags 'SSAS', 'Conference', and 'DAX'

LASTDATE dates arguments and upcoming events #dax #tabular #powerpivot

sqlbi — Mon, 01 Oct 2012 17:04:00 GMT

Recently I had to write a DAX formula containing a LASTDATE within the logical condition of a FILTER: I found that its behavior was not the one I expected and I further investigated. At the end, I wrote my findings in this article on SQLBI, which can be applied to any Time Intelligence function with a <dates> argument.

The key point is that when you write

LASTDATE( table[column] )

in reality you obtain something like

LASTDATE( CALCULATETABLE( VALUES( table[column] ) ) )

which converts an existing row context into a filter context.

Thus, if you have something like

FILTER( table, table[column] = LASTDATE( table[column] )

the FILTER will return all the rows of table, whereas you probably want to use

FILTER( table, table[column] = LASTDATE( VALUES( table[column] ) ) )

so that the existing filter context before executing FILTER is used to get the result from VALUES( table[column] ), avoiding the automatic expansion that would include a CALCULATETABLE that would hide the existing filter context.

If after reading the article you want to get more insights, read the Jeffrey Wang's post here.

In these days I'm speaking at SQLRally Nordic 2012 in Copenhagen and I will be in Cologne (Germany) next week for a SSAS Tabular Workshop, whereas Alberto will teach the same workshop in Amsterdam one week later. Both workshops still have seats available and the Amsterdam's one is still in early bird discount until October 3rd!

Then, in November I expect to meet many blog readers at PASS Summit 2012 in Seattle and I hope to find the time to write other article on interesting things on Tabular and PowerPivot. Stay tuned!

A #DAX training day and other sessions at #SQLBits

sqlbi — Thu, 11 Aug 2011 10:10:00 GMT

It seems that the next SQLBits will be larger than ever! The SQLBits 9 will take place in Liverpool from 29th September to 1st October 2011. It will be hard to get a nicer weather than in Brighton, but we will try to bring a bit of sun from Italy!

I and Alberto will deliver a whole training day on September 29th: DAX Deep Dive will be a full immersion into DAX that will be the fundamental asset for any BI developer in the next release of SQL Server. You can already use it with Denali CTP3 in BISM Tabular and PowerPivot. We will cover both features of version 1 (the current released PowerPivot for Excel) and version 2 (PowerPivot v2 and BISM Tabular). Registrations are open and seats limited: at the previous SQLBits our training day (SQLBI Methodology) was full and I think that this could sold out faster. I’m curious to see if my forecast is reliable!

I will also have a session on Saturday (October 1st), Vertipaq vs OLAP: Change Your Data Modeling Approach, in which I will try to discuss whether the dimensional modeling (i.e. the star schema) is still the best way to model your relational data for analysis now that you have two different analytical engines, BISM Tabular and BISM Multidimensional. I know, the easy answer is “yes, of course” but it will be interesting to understand when and why this is true and when other design choices might be better.

In the same day (Saturday, October 1st) Alberto Ferrari will present the Many-to-Many Relationship in DAX. This session will show some design patterns based on many-to-many dimension relationships (most of them covered in my The Many to Many Revolution whitepaper). There will be *a lot* of DAX in this session, because BISM Tabular doesn’t support many-to-many relationships natively but using DAX we can workaround this limitation. I’ve already seen a draft version of this session and I have to say that just the Basket Analysis pattern (which is one of the many covered) justify the time you will spend attending this session.

Finally, if you are interested in a onsite course, we have an offer for DAX Training on SQLBI website.

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.