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!