Davide Mauri : Analysis Services

SSAS DMVs: useful links

Davide Mauri — Fri, 18 May 2012 14:47:42 GMT

From time to time happens that I need to extract metadata informations from Analysis Services DMVS in order to quickly get an overview of the entire situation and/or drill down to detail level. As a memo I post the link I use most when need to get documentation on SSAS Objects Data DMVs:

SSAS: Using DMV Queries to get Cube Metadata
http://bennyaustin.wordpress.com/2011/03/01/ssas-dmv-queries-cube-metadata/

SSAS DMV (Dynamic Management View)
http://dwbi1.wordpress.com/2010/01/01/ssas-dmv-dynamic-management-view/

Use Dynamic Management Views (DMVs) to Monitor Analysis Services
http://msdn.microsoft.com/en-us/library/hh230820.aspx

SQL Bits X – Temporal Snapshot Fact Table Session Slide & Demos

Davide Mauri — Wed, 11 Apr 2012 12:47:39 GMT

Already 10 days has passed since SQL Bits X in London. I really enjoyed it! Those kind of events are great not only for the content but also to meet friends that – due to distance – is not possible to meet every day. Friends from PASS, SQL CAT, Microsoft, MVP and so on all in one place, drinking beers, whisky and having fun. A perfect mixture for a great learning and sharing experience!

I’ve also enjoyed a lot delivering my session on Temporal Snapshot Fact Tables. Given that the subject is very specific I was not expecting a lot of attendees….but I was totally wrong! It seems that the problem of handling daily snapshot of data is more common than what I expected.

I’ve also already had feedback from several attendees that applied the explained technique to their existing solution with success. This is just what a speaker in such conference wish to hear! :)

If you want to take a look at the slides and the demos, you can find them on SkyDrive:

https://skydrive.live.com/redir.aspx?cid=377ea1391487af21&resid=377EA1391487AF21!1151&parid=root

The demo is available both for SQL Sever 2008 and for SQL Server 2012. With this last version, you can also simplify the ETL process using the new LEAD analytic function. (This is not done in the demo, I’ve left this option as a little exercise for you :) )

Spotlight session at PASS 2011 - Temporal Snapshot Fact Table

Davide Mauri — Sun, 29 May 2011 13:44:59 GMT

I’m very happy to announce that my proposal for the Spotlight session I’ve been invited to deliver at PASS 2011 has been accepted!

Temporal Snapshot Fact Table

You are designing a BI Solution and your customer ask you to keep a snapshot of the status of all their documents (orders, insurances, contracts, bills...whatever the word "document" may mean) for all the days of the year. They have millions of documents and they want to have in their Data Warehouse all the data they have gathered right from the very first operating day.

If you have 1 million of documents (on average) and you have to keep a snapshot of them for each one of the 365 days in a year, and you have 10 year of history, you're going to have a 3 billions table just to start with. That's a very big and challenging number, and you may have not the option to buy a Parallel Data Warehouse.

In this session, we'll see how we can turn the usual snapshot tables into temporal table so that we can store time intervals in order to avoid data duplication, while keeping the Data Warehouse design usable by Analysis Services (that doesn't know what an interval is) and optimizing it to have very good performance even on standard hardware.

The explained technique is a result of several month of research and has been applied to the Data Warehouse of an insurance company where we had to deal with two times the number said before.

The topic is very interesting and – I bet – very intriguing for many people working in BI and specially with Analysis Services, since it lacks the support of “time intervals” to define the validity period of a fact row. With my SolidQ Italian collegues we’ve been able to find a way to overcome this limitation, allowing the storage of daily snapshots of data with a very high efficency and performance.

In this session I’m going to share everything we discovered with you. It will be really interesting, I can tell you! Probably one of the most advanced – yes simple - usage of SSAS and Many-To-Many relationship you’re going to see.

SSAS Backup and other useful PowerShell scripts

Davide Mauri — Fri, 30 Jul 2010 16:34:58 GMT

Today I was looking for a Powershell script to automatize the backup of Analysis Services database also capable to manage retention.

In such search I stubled upon the SSASBackp PowerShell script created by Idera which is also released for free.

There’s other script beside that in their PowerShell Free Scripts page

http://www.idera.com/Products/Free-Tools/PowerShell-scripts/

Though many of them probably needs some customization to satisfy your own specific needs, they can be a very useful as starting point to build your own (SSAS Backup is one exception since it is full featured and immediately usable without any changes).