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

Thinking in DAX (#powerpivot and #bism)

Last week Alberto published an interesting post about Counting Products in the Current Status with PowerPivot. Starting from a question raised from a reader, Alberto described how to solve a common issue (let me know the “current status” of each item at a given point in time starting from a transactions table) by using a single DAX formula. I suggest you to read his post to understand the technical details of that.

What is inspiring of this example is that we can look at Vertipaq and DAX from several point of view.
In PowerPivot, it is simply the formula engine that replaces the Excel one, allowing you to query multiple tables in a single formula (something you’ve never been able to do in Excel).
In BISM, it will be an intermediate query language between SQL (really tied to physical table and unaware of existing relationships) and MDX (much more related to an abstract dimensional model, with so many side effects related to underlying data model that makes MDX so hard to master).
But in both cases we consider DAX just as a query engine for reporting. Now, think for a moment about SQL. It is not just a query language, it is also a data manipulation language. Even if you don’t use its INSERT/UPDATE/DELETE statements, you can still use a SELECT to shape data at your will saving time/work from your ETL process. Well, I’m starting to consider DAX as a possible tool for ETL. Not the only one and not the smartest one. But it could be one that I can rely to. DAX and Vertipaq are so fast and powerful that might worth the time to load data in memory to do the processing by using DAX.

I still don’t have any evidence to justify DAX use over SQL. I’m just starting to evaluate it as a possible alternative. In the coming months I will investigate more on that (and I already have some ideas where it could be useful), but I’d like to get your feedback in case you already found practical cases where DAX can help you in reducing processing window time of an ETL job.

In the following weeks there will be several PowerPivot workshops in Europe (Copenhagen next week, Dublin on March 28-29 and Zurich on April 4-5) and tomorrow is the last day to get advantage of the Early Bird discount for registering to the Zurich event. There will be also free evening events in Copenhagen (March 21) and Zurich (April 5 – write me to get more info). Many opportunities to discuss the future of DAX with the attendees. I hope to meet you there!

Published Thursday, March 17, 2011 6:46 PM by Marco Russo (SQLBI)
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS


No Comments

Leave a Comment


About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Privacy Statement