Vidas Matelis Analysis Services Blog

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 »

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 »

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 »

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 »

In SSAS 2005 to access SSAS metadata you had to use object model.  In SSAS 2008 November CTP Microsoft introduced schema rowsets as an alternative way to access metadata. I did some tests last few days and here I’ll post examples of what could be done. Generally it is quite easy to write these queries. BOL already have description for most of the tables and fields you can query. What was not so easy is to get metadata on structure that is hierarchical. That is there is an easy way to get a list of hierarchies in one dimension. But to get list of levels of hierarchies of dimensions becomes not an easy task.

SELECT statements you can write on these rowsets appear to be quite limited. For example:

• SELECT DISTINCT does not return DISTINCT values
• ORDER BY clause accepts just one field to order by. Adding second field raises error: “Error (Data mining): Only one order expression is allowed for TOP expression at line 1, column 1″
• COUNT, SUM does not work
• WHERE clause works
• ORDER BY <number> does not ORDER, but no error
• JOINS appear not to work
• LIKE does not work
• string functions like LEFT do not work

As I did not find restriction list in documentation, list above is from my experience. It could be that I just did not do my tests properly, but I am sure sooner or latter there will be official list of what is supported.

Bellow are examples of  queries that are very simple, but enough for anyone to get an idea of what it is possible. All these examples where run in Adventure Works DW database.

Read the rest of this entry »

As Microsoft released today November CTP 5 for SQL Server 2008 and this time there are quite a few changes in Analysis Services area. Documentation lists these SSAS changes in November CTP:

• MOLAP Performance (FITS): New MOLAP-enabled write-back capabilities in SQL Server 2008 Analysis Services remove the need to query ROLAP partitions. This provides users with enhanced writeback scenarios from within analytical applications without sacrificing the traditional OLAP performance.
• Resource Monitoring: This concept is similar to DMV found in the relational engine. Analysis Services DMV addresses immediate needs of DBAs to get answers to the questions like: Who is connected to my server? What are the active sessions and what commands/queries they are running? What CPU consumed by particular query/command? What are the objects being referenced by particular command?
• MDX Query Optimizer - Block Computation: Block computations provide a significant improvement in processing performance, enabling users to increase the depth of their hierarchies and complexity of the computations.
• Aggregation Design:  SQL Server 2008 drives broader analysis with enhanced analytical capabilities and with more complex computations and aggregations. The AS Aggregation Design improvement exposes Aggregation Design objects in SQL Server BI Dev Studio and SQL Server Management Studio and provides tools for users to better work with these aggregation designs. In addition, an advanced view in the new Aggregation Design tab of the cube editor provides the ability for an advanced user to view and manually edit individual aggregations within an aggregation design.
• Analysis Services Cube Design:  New cube design tools help users streamline the development of the analysis infrastructure, enabling them to build solutions for optimized performance. The AS Cube Design improvement introduces a new Cube Wizard which helps users create better cubes in fewer steps.  The new wizard focuses on having the user answer a few questions to create leaner cubes that better targets their needs.  It also unblocks the previously difficult scenarios of creating a cube a cube based on a single, de-normalized table and creating a cube containing only linked dimensions.

Read the rest of this entry »

Mosha Pasumansky just posted blog entry about dynamic named sets in SSAS 2008. In his post Mosha listed 4 SSAS changes that were included in June CTP release:

• Dynamic named sets
• CREATE MEMBER statement extension to allow specify display folder and associated measure group
• New CREATE KPI statement
• Ability to change calculated member “on the fly”

As Mosha focused on changes with Dynamic named sets, I decided to do a quick test on other 3 changes. To be honest, just from Mosha’s blog entry I realised that these MDX changes are already in CTP. I knew that some of these changes were planned, but from webcasts and chat sessions I was under impression that these changes will be available just in the future releases. Is it possible that these changes were included just in July CTP?

So here are my tests on other changes. 

Read the rest of this entry »

In my last post I listed changes that are expected in SSAS 2008 next CTP 4. But it looks like none of the changes made into July CTP. They probably were postponed last minute. Even new Microsoft documentation (read Chris Web  blog) lists some of the features as present. For example block computation is listed in documentation, but just today in webcast I saw block computation example where in CTP4 it took query to execute 1min, and in new SSAS build the same query come back in a few seconds. So I hope that all changes listed in my previous post will be in the next CTP5, that is targeted for September 18th release (during PASS conference).

Today there was another good webcast presentation “The value of Business Intelligence with SQL Server 2008” by Julie Strauss (Product Manager) & Donald Farmer (Principal Program Manager). From this presentation I got more info on 2 new enhancements (not listed in my previous posts) that we should expect in the next CTP.

Read the rest of this entry »

Today I participated in the Microsoft webcast “TechNet Webcast: Data Warehousing Enhancements in Microsoft SQL Server 2008” (Event Code: 1032344497) by Torsten Grabs. Some information in this webcast was about upcoming changes in SSAS 2008.

In SSAS 2008 we should expect MDX query performance improvement as now Analysis Services will be able to deal better with cube sparsity. Cube space is generally “sparse” - values only exists for small number of dimension intersections. SSAS 2005 evaluates expressions on complete space. With SP2 SSAS 2005 does some subspace computation, but in SSAS 2008 this is improved a lot. SSAS 2008 divides the space to separate calculated members, regular members and empty space. Then it can better evaluate cells that needs to be included in calculations.

 There was interesting information presented about SSAS backups. I did not know that that for SSAS 2005 backup time grows exponentially for databases over 20GB in size. This is fixed in SSAS 2008 and backup time growth now is linear. Also in SSAS 2008 Analysis Services backup storage subsystem will be replaced and all limitations on backup size will be removed.

Presenter also listed other upcoming SSAS improvements, but because of time shortage did not elaborate more:

• Query Tracing
• Writeback performance
• Read-only scalable database
• Resource monitoring 

 I understood that these changes will be included in the next CTP (CTP 4) that is expected in 2-3 weeks. Microsoft is planning to to CTP releases every 2-3 months.

I posted this in SSAS 2008 forum, but decided also to post it here.

I did some testing with Analysis Services 2008 June CTP and found following:

• No problems restoring databases that were archived in SSAS 2005.
• I like new dimension wizard and can see myself using it more, as I have more control on how attributes are created.
• June CTP can be used to test how my database is holding to “best design practices”, as I can see all the warnings after database is restored.
• Adventure works database that is shipped with Katmai June CTP has a lot of warnings. I know that this is just a warning and not error, but when I open Adventure Works DW cube, I keep getting distracted by all these blue lines in almost every dimension.

Read the rest of this entry »

Just participated in chat session on Katmai release and found some new information on SSAS 2008. Full transcript of this chat will be published shortly here.

Some interesting Q/A below:

• Q: What are improvements to Analysis Services 2008? I saw new feature visual attribute relationship in Dimension Editor, and also I saw “Design warning” interface where you can enable/disable design warnings. Anything else we should check in SSAS 2008?
  Rob Zare [MSFT] (Expert):
  A: This CTP includes a redesigned dimension wizard, a new attribute relationship designer, and real-time design/best practice warnings. The warnings are implemented in AMO, exposed in the UI via blue squiggly lines, and can be dismissed individually (a single occurrence) or turned off all together.

Read the rest of this entry »

This weekend I had some time to play with SQL Server 2008 (Katmai) June CTP, and I am posting here my first impressions (focus on Analysis Services).

Installation

As a first attempt I tried to setup SQL Server 2008 on the computer that already had SQL Server 2005 default instance installed. I wanted to install SQL 2008 as a named instance and compare SQL 2005 and 2008 side by side. After answering few standard questions I got message that because SQL Server 2005 was found on the machine, SQL Server 2008 cannot install client tools. I proceed with installation without client tools. After installation I tried to use SQL Server 2005 BIDS environment and connect to Katmai instance, but somehow that did not work. Then finally I decided that maybe I should read readmesqlkatmai.htm file. Found that side by side installation is not supported. From readme file: Read the rest of this entry »