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

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

Dynamic Calculation of Inventory with #dax in #powerpivot and #ssas #tabular

I recently wrote an article on SQLBI describing the dynamic calculation of stock inventory in DAX. With this technique, you can avoid creating a snapshot fact table just to store the inventory stock for every day and every product, creating a table that in certain business is huge and expensive, especially if you have to load it in memory.

I also performed some performance comparisons between the two approaches. I have to say that I would not suggest one option against the other, unless I can do some test on a real set of data. The problem is that the real convenience depends on data distribution and density. Moreover, performance differences also depends on the query, and I have found that the traditional snapshot-based approach is faster when performance are always good (queries below one second), but becomes slower when there are queries returning large result set. I still don’t have a clear explanation of the reason why there are these differences, so if you have any feedback based on experience on different data sets, you are more than welcome if you will share it here!

You can read the article and download the sample workbooks (for both Excel 2010 and Excel 2013) following this link.

Published Tuesday, August 27, 2013 3:18 PM by Marco Russo (SQLBI)



Vidas M said:

I used this technique when building investment tracking PowerPivot application ( . Works great - all I need is a list of transactions when stocks are bought/sold and then I can easily calculate how many shares I have at any point in time. PowerPivot does calculations extremely fast and as you said there is no need to create snapshot table. I work with small data sets (3-4 thousand transactions) and each report comes back in under 1 sec.

August 27, 2013 9:32 AM

Marco Russo (SQLBI) said:

Vidas, thank you for the feedback! I've seen it works well also with larger data sets. The performance can depend on the size of the data set and on the number of points in time - for example, creating a daily chart is much more expensive than a monthly chart (using just the end of month day).

August 27, 2013 4:55 PM
New Comments to this post are disabled

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