Vidas Matelis Analysis Services Blog

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 »

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 »

In my previous post Katmai Analysis Services 2008 November CTP5 – tests on metadata rowsets I listed examples of new SSAS DMVs. But there were few DMVs that you could not query. For example if you would try to execute following query:

SELECT * FROM $SYSTEM.DISCOVER_PARTITION_DIMENSION_STAT

You would get this error message: 

The ‘DATABASE_NAME’ restriction is required but is missing from the request.

Read the rest of this entry »

Our company is using Microsoft Excel 2007 with Microsoft Office SharePoint Server 2007 and Excel Services as the primary interface to access Analysis Services cube data. This works very well on Windows XP PC, but was almost unusable on Windows Vista PC. On Vista PC you would get just too many “Enter UserName/Password” dialogs.

Here is what would happen on Vista PC when you try to open Excel report with Pivot Table from the SharePoint: Read the rest of this entry »

In SQL Server 2008 Microsoft introduced “Management Data Warehouse” (MDW). This blog post should give you a good idea on what to expect from this new feature. Note: All tests and screenshots for this post were done in SQL Server 2008 February CTP (CTP6). 

Before starting MDW configuration please make sure that your SQL Server Agent is running. You can configure Management Data Warehouse by going into SQL Server Management studio, then expanding Management folder and right mouse clicking on Data Collection folder and choosing “Configure Management Data Warehouse”. This will start wizard that will ask you MDW database connection information (server, database and authentication). As MDW database puts some overhead for the server, in production environment you probably will want to have this database on a separate server. After wizard completes you should see green arrows for Data collection items:

Read the rest of this entry »

I just came back from 2008 MVP summit. I had a great time, especially meeting so many new people.

Here are few pictures from this summit.

Read the rest of this entry »

Today I received an e-mail from Microsoft telling me that I received the Microsoft MVP award ”for efforts in SQL technical communities during the past year”. I am very happy and excited about this.

I already made the necessary arrangements and bought airplane tickets to the Microsoft MVP summit in Seattle on April 14-17th.

I am sure that I will have a very exciting year ahead.

Link to my MVP profile.

A few weeks ago I asked a question on Analysis Services 2008 forum if there will be PowerShell support for SSAS 2008. I got an answer from Microsoft, that no native support is planned. I know that Darren Gosbell released PowerShell Provider for SSAS 2005, but for the different reasons I cannot use it with all clients. But just this week Carl Rabeler (Microsoft) posted a new updated set of Analysis Services 2005/2008 Samples. One folder in these samples contained 4 PowerShell script examples that shows how to use it with Analysis Services. I tested these samples and they worked on a standard SSAS 2008 installation. Today Carl Rabeler actually updated these samples – fixed some minor issues and added one more sample – to get a size of Analysis Services database.

Read the rest of this entry »

I just found an great website with a live running demo of report examples with Analysis Services, SharePoint, PerformancePoint, Excel Services, Reporting Services and other Microsoft BI Technologies. This is Richard Lee’s website:

http://richardlees.com.au/sites/Demonstrations

From the site:

Site has a variety of data mining, OLAP and business intelligence tools on several real (census, web logs, Perfmon and libraries) and synthetic (foodmart) databases of over 100 Million records.  The demonstrations are platformed on Microsoft SQL Server 2005 and Microsoft SharePoint Server, Microsoft PerformancePoint Server 2007 and Excel Services.

Well worth visiting and exploring all these online examples.

Today I was reading Chris Webb post ”Visualising Analysis Services Trace Information in Reporting Services” where at the end he talks about tools to monitor SSAS, that made me thinking about SSAS 2008 DMVs again. So I went back to my SSAS 2008 installation to see what do we have new in CTP6. Just after release Darren Gosbell already blogged about new DMVs in his post “SSAS 2008: What’s new in the Schema Rowsets?“. But just after I started to run queries I realized how cool these new DMVs are. After quick test on them, I decided to blog today about my favorite DMV so far: $SYSTEM.DISCOVER_OBJECT_ACTIVITY.

Read the rest of this entry »

SQL Server 2008 February CTP6 is out and I spent last few evenings installing it and going through new features. Here is my report. 

Attaching and detaching SSAS databases 

In new CTP Microsoft introduced option to attach and detach Analysis Services databases. This works in a very similar way like with SQL Server. When detaching SSAS database you have an option to specify Password. When attaching SSAS database you have an option to specify Folder, Password and specify if you want to make new database Read-only.

Read the rest of this entry »

I got a message from Microsoft that I was nominated for MVP award. I will know on April 1st if I got the award. I am very honored and excited about this. Even if I will not get the award, just being nominated is a big deal for me.