Vidas Matelis Analysis Services Blog

Few years ago on my blog I posted scripts “SSIS Package to drop/create partitions based on partition list in the SQL Server table” and “SSIS package that process all partitions/measure groups/cubes in one database“. These posts contained partial scripts that I developed for company “Insight Decision Solutions Inc.” to maintain partitions in the Microsoft SQL Server Analysis Services. This company sells, customizes and implements pre-packaged data warehouse solution (using SQL Server, SSAS, SSIS, SSRS, SharePoint and Excel 2007) for “Life” and “Health” insurance companies. Recently “Insight Decision Solutions Inc.” owners let me post full script on how to automate SSAS partition management . Here are step by step instructions that I adjusted and tested on Adventure Works database. Most of the code comes from my earlier published posts, here I just added information how everything works together.

Read more and download code here…

While writing SSIS package to load one of my data warehouse tables, I encountered SSIS problem that took me some time to figure out. I noticed that in the dataflow task one of the OLE DB source components was not returning any data even I know that records were there. So I did some investigation and found what was causing this issue.

I found that when following conditions are met, OLE DB Source component will not return any data:

• “OLE DB Source” component is based on the SQL Query
• SQL Query contains parameters
• SQL Query contains line that starts with comments symbol “–” 

 Example, this query (database “Adventure Works DW”) works:

SELECT CurrencyKey
  FROM dbo.DimCurrency
WHERE CurrencyKey BETWEEN ? AND ?

But this query always returns empty result set:

SELECT CurrencyKey
FROM dbo.DimCurrency
— my comments
WHERE CurrencyKey BETWEEN ? AND ?

Read the rest of this entry »

In my past blogs I showed how to use SSIS package to process dimensions, cubes or build aggregates. I am slowly rebuilding my existing packages thanks to points from Darren Gosbell and Jess Orosz. I found that using CaptureXML method is more convenient for me and it is definitely faster.
Read the rest of this entry »

Recently I posted a blog entry on how to process all dimensions in a single Analysis Services 2005 database using SQL Server Integration Services (SSIS) package. Here I’ll add code to this package that will allow you to process whole database, cubes, measure groups or partitions.

Read the rest of this entry »

After I published blog entry about a  SSIS package that processes dimensions, I received a suggestion from Darren Gosbell (his blog is here) that instead of building XMLA code by concatenating strings, I  should use the  CaptureXML option from an  SSAS server object and then process dimensions using dim.Process method and execute XMLA using ExecuteCaptureLog routine.

Read the rest of this entry »

Vidas: My next blog entry contains updated code !

It is quite often that I have to process all the dimensions in a database. I like to use scripts for that. Here I have step by step guide on how to create an SSIS package that processes all dimensions in one database.

Read the rest of this entry »

I prefer to use usage statistics to generate SSAS 2005 aggregates, but during the initial phases of a project they are not available. So I usually build a set of random aggregates, and after enough usage statistics are accumulated, I rebuild aggregates based on usage. Microsoft provides a nice wizard to generate aggregates on measure groups and partitions. But because I have to do this task way to many times, I decided to build a simple SSIS package that does it for me.

Read the rest of this entry »