Vidas Matelis Analysis Services Blog

I recently posted a new PowerShell script to process all dimensions and cubes in the specified Analysis Services database. This script has a few additional parameters that give you more control for your SSAS DB processing. First of all, you can specify the MaxParallel processing parameter to control the level of parallelism. You can also specify how many processing commands you want to execute per batch.  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.

Microsoft BI Conference 2008 will start in just 11 days, and today I finally finished building my session list. This was really hard to do, and in most of the time slots I still have 2-3 sessions that I would like to attend. Last year Microsoft recorded Breakout sessions, so if they will do the same this year, I’ll try to attend more Chalk Talk sessions and will view sessions that I missed from a recorded DVD.

After last years BI conference I complained about a lack of technical content. But this years session list looks exceptionally good. There are plenty of sessions on SSAS, SSIS, SSRS, Performance Point, Excel Services and Master Data Management. Marco Russo already blogged about the expected announcement during this BI conference regarding “Self-service BI”. I am very interested to hear what Microsoft will say about this. I’ll blog about my conference experience as soon as I’ll come back.

By the way, does anyone want to join us for a beer after the Sunday Evening Welcome Reception? We started to talk about this in this forum post and if you have no other plans then let’s meet.

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 »

If you are like me and prefer a visual learning approach, then you should check out the website http://www.learnmicrosoftbi.com. The website owner Craig Utley has recorded a list of videos that are a good way of learning about Microsoft BI technologies and specifically Microsoft SQL Server Analysis Services 2005 and 2008. At the time of writing this post there were 14 videos ranging from 6min to 57min each.

His latest video “SSAS 109 – Attribute Relationships” should be watched by all SSAS developers who want to understand why attribute relationships are so important. This video shows plenty of examples and compares SSAS 2005 and 2008 versions. This is one of the best tutorials on this subject and I highly recommend it.

The best part – all of the videos are high quality and free, all you need to do is register and start downloading.

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 »