Vidas Matelis Analysis Services Blog

Using SSRS to report SSAS 2008 database structure using DMVs

Vidas Matelis — Sun, 01 Mar 2009 23:54:07 +0000

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.

New Book – Microsoft SQL Server 2008 Analysis Services Unleashed

Vidas Matelis — Tue, 30 Dec 2008 03:20:17 +0000

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.

Powershell script to process SSAS database objects

Vidas Matelis — Mon, 15 Dec 2008 01:58:13 +0000

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.

Powershell scripts to process dimensions and print SSAS DB info

Vidas Matelis — Mon, 10 Nov 2008 00:10:38 +0000

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.

My SSAS presentation for Toronto SQL Server User Group

Vidas Matelis — Fri, 31 Oct 2008 01:15:43 +0000

I will be doing a presentation "Introduction to Microsoft SQL Server 2008 Analysis Services" for Toronto SQL Server user group.

Microsoft BI Conference 2008 – just 11 days left

Vidas Matelis — Wed, 24 Sep 2008 23:08:51 +0000

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 [...]

How to use MDW to collect Analysis Services 2008 performance counters

Vidas Matelis — Thu, 18 Sep 2008 23:27:36 +0000

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 [...]

A great website with SSAS video learning material

Vidas Matelis — Thu, 07 Aug 2008 02:15:36 +0000

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 [...]

SSAS 2008 DMVs – querying from the SQL Server and database diagrams

Vidas Matelis — Wed, 23 Jul 2008 03:01:12 +0000

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.

Microsoft SQL Server 2008 RC0 – New Adventure Works Sample Databases for SSAS

Vidas Matelis — Wed, 11 Jun 2008 03:06:04 +0000

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 [...]

SSAS 2008 RC0 – New function SYSTEMRESTRICTSCHEMA for restricted schema rowsets – DMVs

Vidas Matelis — Sat, 07 Jun 2008 03:42:45 +0000

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 [...]

Excel 2007 Pivot Table with SharePoint On Vista PC – works good after SP1

Vidas Matelis — Fri, 06 Jun 2008 01:50:28 +0000

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 [...]

SQL Server 2008 Management Data Warehouse

Vidas Matelis — Thu, 24 Apr 2008 03:04:08 +0000

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. [...]

Pictures from the 2008 MVP summit

Vidas Matelis — Mon, 21 Apr 2008 02:02:08 +0000

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. Microsoft awarded Darren Gosbell with XBox 360 Elite for finding the most important security bug in SQL Server. Here is Mosha Pasumansky handing the XBox to him. Congratulations Darren! “Analysis Services Table” during SQL Server [...]

I received Microsoft MVP award

Vidas Matelis — Tue, 01 Apr 2008 22:40:18 +0000

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 [...]

Analysis Services 2005 and 2008 with PowerShell – it works

Vidas Matelis — Thu, 27 Mar 2008 02:32:59 +0000

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. [...]

Live report example website with Microsoft BI technologies

Vidas Matelis — Fri, 21 Mar 2008 22:48:23 +0000

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 [...]

SSAS 2008 CTP6 – new DMV $SYSTEM. DISCOVER_ OBJECT_ ACTIVITY

Vidas Matelis — Wed, 19 Mar 2008 04:17:11 +0000

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 [...]

SQL Server 2008 February CTP6 – Read-only SSAS Shared Scale-out and other changes

Vidas Matelis — Thu, 21 Feb 2008 03:08:20 +0000

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 [...]

I am nominated for MVP award

Vidas Matelis — Wed, 30 Jan 2008 23:02:36 +0000

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.