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…

New SSAS 2008 DMVs allows you to easily access Microsoft SQL Server Analysis Services (SSAS) metadata – information about cubes, dimensions, measure groups, measures, etc. As with DMVs metadata information is returned in the data set format, it is very easy to build Reporting Services reports to generate documentation about your database.

You can execute SSAS DMV queries directly in the Analysis Services environment, but this approach has a lot of limitations – most importantly you can not do joins between DMVs. To go around this limitation, I created linked server from SQL Server to Analysis Services. This way I can do joins between DMVs. Here is the script that was used to create linked server: Read the rest of this entry »

I pre-ordered “Microsoft SQL Server 2008 Analysis Services Unleashed” (Amazon links: US, UK and Canada) some time ago and today I finally received it. The authors are Irina Gorbach, Alexander Berger and Edward Melomed. This book is the second edition of the older book “Microsoft SQL Server 2005 Analysis Services” (Amazon links: US, UK and Canada). It has the same structure as the older book – the same 9 parts, but the 41 chapters are slightly different.

Read the rest of this entry »

Previously, the majority of my scripting code was done either in the SSIS script component or in WSH. But now it is time to learn PowerShell as Microsoft included it with SQL Server 2008. I find that PowerShell has its own specific requirements – like restrictive Execution Policy or the need to load a namespace definition. I have a document where I posted my findings and links to useful PowerShell resources. I’ll keep updating that document as I learn PowerShell myself, so make sure you check it when you begin to use PowerShell. Read the rest of this entry »

I will be doing a presentation “Introduction to Microsoft SQL Server 2008 Analysis Services” for Toronto SQL Server user group on November 13, 2008. Come and join us if you are just starting to learn about Analysis Services and live in the Toronto area.

Presentation description:

The main purpose of this presentation is to demonstrate what Analysis Services is and how you can start using SSAS for your company. We will create a simple SSAS database solution, deploy it and then query the SSAS database using Microsoft Excel 2007. You will also get an idea about what the MDX language is.
Targeted audience – SQL Server DBA/Developers who want to learn how to use Microsoft Analysis Services.

In one of my previous blog posts I described SQL Server 2008 Management Data Warehouse (MDW) and how it can be used to collect performance information. This post will describe how you can create new MDW data collection sets to capture SQL Server Analysis Services (SSAS) 2008 performance counters.

SQL Server 2008 does not have a graphical interface to create MDW data collection sets, so the best way to do that is by scripting an existing system data collection set and then changing it. To script an existing data collector, start SQL Server Management Studio, expand “Management”->”Data Collection”->”System Data Collection Sets” folder then right mouse click-> “Script Data Collection As..”->”Create To”->”New Query Editor Window”. For my starting point I scripted the data collection set “Server Activity” and got this script.

Read the rest of this entry »

In Analysis Services 2008 Microsoft introduced many schema rowsets that are very similar to SQL Server Data Management Views (DMVs). Although in Books Online Microsoft refers to these new tables as “schema rowsets”, it is just easier for now to call them DMVs. You can use these DMVs to query SSAS 2008 metadata and actual data. Here is the link  to my original post about DMVs.

You can execute queries on DMVs directly in Analysis Services. For example you can connect to Analysis Services from SQL Server Management Studio, choose menu item “File”->”New”->”Analysis Services MDX Query” and write your query to select data from DMV.

You can also execute queries against SSAS DMVs from SQL Server environment. At this point I believe this is going to be preferred method to query SSAS DMVs. This is because queries on DMVs in Analysis Services has many limitations, yet these limitations do not exists for queries from SQL Server linked server. Example of such very important limitation – in Analysis services you cannot join 2 DMVs.

Read the rest of this entry »

As you probably all already know Microsoft released SQL Server 2008 Release Candidate 0 (RC0).  You can download RC0 from here. You can also download SQL Server 2008 Books Online Release Candidate (RC0) and Microsoft SQL Server 2008 Feature Pack RC0, June 2008.

Just today Microsoft also released another related download: Sample Databases for Microsoft SQL Server 2008 RC0. New sample set contains 8 BI related files that lets you install Adventure Works DW and Analysis Services databases:

• SQL2008.AdventureWorks_DW_BI_v2005.ia64.msi
• SQL2008.AdventureWorks_DW_BI_v2005.x64.msi
• SQL2008.AdventureWorks_DW_BI_v2005.x86.msi
• SQL2008.AdventureWorks_DW_BI_v2005.zip
• SQL2008.AdventureWorks_DW_BI_v2008.ia64.msi
• SQL2008.AdventureWorks_DW_BI_v2008.x64.msi
• SQL2008.AdventureWorks_DW_BI_v2008.x86.msi
• SQL2008.AdventureWorks_DW_BI_v2008.zip

As you can see, installation files are split by CPU type (x86, x64 and ia64) and also by related SQL Server version. Files with _v2008 suffix can only be used with SQL Server 2008 and demonstrates some of the new features. If you want to do manual file copy, you can use 2005 or 2008 version zip file.

Read the rest of this entry »