Vidas Matelis Analysis Services Blog

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.

When I initially migrated Analysis Services database from 2000 to 2005 version, in the new database I created single cube with all measure groups in it. At the time I believed that was a right way to do. This is quote from one of my favorite books “The Microsoft Data Warehouse Toolkit with SQL Server 2005 and the Microsoft Business Intelligence Toolset” page 322, chapter 7:

“The best practice in Analysis Services  2005 is to define a single cube for a database”… ” You are still permitted to create multiple cubes in a database, but you shouldn’t”. Instead, create a single cube with multiple measure groups.”

But later I found about Microsoft recommendation “Avoid having more than 15 measure groups in cube”. I posted question on Analysis Services MSDN forum and got reply from Greg Galloway about his experience - about 15% performance boost after splitting and about 10% performance improvement after reducing cubes MDX script code.

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 »

For the past few years I was working on data warehouse project that is customized and installed for different clients. As part of customization, sometimes clients might request to use different names for dimensions, attributes or measures. For example in Geography dimension for US customers that do business just in US you would want to have hierarchy Country-State-City, but for Canadian customer the same hierarchy would be Country-Province-City. For customers that do business in US and Canada, the same hierarchy could be called Country-State|Province-City.

Analysis Services 2005 provides easy interface to rename dimensions, attributes or measures. But things become more complicated when these object names are used in cube MDX script - these renames can break script. Instead of renaming actual object, you might consider using “Translation” feature of Analysis Services 2005. I usually create translation for language that matches clients PCs language. This way Excel 2007 uses translated names without any additional steps. For other SSAS clients you might have to specify locale ID in connection string parameter, example: ”Language Identifier=1033;”. With translations you can choose to rename just a few objects. If no translations exists, Analysis Services uses default object name assigned during object creation. Read the rest of this entry »

I just recently started to learn a bit more about Analysis Services data mining. I am very impressed how Excel 2007 add-ins makes data mining user friendly. All technical stuff is hidden, you just select your data in Excel, run add-in and review results. Behind the scene this add-in generates SSAS data mining model and process it with provided data. Of course, for more complicated stuff you will have to learn SSAS data mining to have a better control on how data mining project is build.

Just today in Microsoft webcast newsletter I noticed that there are 7 Analysis Services data mining webcasts scheduled for November. If you are also interested in this technology, make sure you don’t miss them:

I was looking into some issues with using Analysis Services translations and found not related to my problem, but new to me information about SSAS configuration property EnableFast1033Locale. From BOL:

If you use the English (United States) language identifier (0×0409, or 1033) as the default language for the Analysis Services instance, you can get additional performance benefits by setting the EnableFast1033Locale configuration property, an advanced configuration property available only for that language identifier. Setting the value of this property to true enables Analysis Services to use a faster algorithm for string hashing and comparison.

I found this properly in the msmdsrv.ini file (default folder: C:\Program Files\Microsoft SQL Server\MSSQL.2\OLAP\Config):

 <EnableFast1033Locale>0</EnableFast1033Locale>

I did some tests and found that you have to drop all databases from SSAS server before you change property value to 1. Then you have to restart server and re-deploy and re-process all databases. I found that even Adventure Works DW database would not work without redeployment. My other test databases were also giving me different errors until I redeployed and reprocessed them.

I could not see performance differences on my small tests databases. But for a big installations with 1033 locale it could be worth to see if this could give you some performance boost.

As as side effect I noticed, that when EnableFast1033Locale property value is set to 1, in Microsoft SQL Server Management Studio server properties window was always giving me error when clicking on “OK” button:

TITLE: Microsoft SQL Server Management Studio
——————————
Errors in the metadata manager. LOG file extension can be only .LOG.
Errors in the metadata manager. An error occurred while setting the value for the ‘ConfigurationSettings\Log\File’ configuration property.
 (Microsoft.AnalysisServices)

To go around this problem just change required property values directly in msmdsrv.ini file.

Years ago I was using Windows Scripting Host (WSH) for majority of my scripting tasks. It is installed by default on all Windows PCs, VBScript language is quite easy to learn/use and you can do a lot with just a few lines of code. But since DTS was introduced, I started to use DTS more and more and now SSIS is default environment where I do all my scripting. This is because SSIS has very good error loging system and is so much more powerful. But I still use a few WSH scripts that gives me quick status of my SSAS databases. To test them in your environment just copy script to text file, name it with extension *.vbs and then run it with command “CScript YourScript.vbs” with required parameters.

I have no problems running these scripts on any of my workstation PCs. But when I tested these cscripts on my server machines I was getting error message “Microsoft VBScript runtime error: ActiveX component can’t create object ‘Microsoft.AnalysisServices.Server’”. Maybe I am getting this error message because my servers are 64bit, or I might require to manually register some dll(s). If anyone knows how to make it work on server, I would appreciate if you’ll leave your comments here.
Updated: Few hours after I posted this blog I got solution from Andreau to this problem. On 64 bit environment I have to execute script using command: “c:\windows\syswow64\cscript.exe YourScript.vbs”. Thanks Andreau!

Here are 2 scripts that you might find useful: Read the rest of this entry »

While writing SSIS package to load one of my data warehouse tables, I encountered SSIS problem that took me some time to figure out. I noticed that in the dataflow task one of the OLE DB source components was not returning any data even I know that records were there. So I did some investigation and found what was causing this issue.

I found that when following conditions are met, OLE DB Source component will not return any data:

• “OLE DB Source” component is based on the SQL Query
• SQL Query contains parameters
• SQL Query contains line that starts with comments symbol “–” 

 Example, this query (database “Adventure Works DW”) works:

SELECT CurrencyKey
  FROM dbo.DimCurrency
WHERE CurrencyKey BETWEEN ? AND ?

But this query always returns empty result set:

SELECT CurrencyKey
FROM dbo.DimCurrency
– my comments
WHERE CurrencyKey BETWEEN ? AND ?

Read the rest of this entry »

Microsoft just released new best practice paper “Identifying and Resolving MDX Query Performance Bottlenecks in SQL Server 2005 Analysis Services” written by Carl Rabeler and Eric Jacobsen. I just started to read it, but I am very impressed. It describes how to use SQL Server Profiler, System Monitor, Windows Task Manager, Kernrate, Process Monitor and MDX Script Performance Analyzer to analyze MDX queries. For SQL Server Profiler this document lists what events to track and what values actually mean. For System Monitor it describes counters that could be useful for MDX query analysis. Document also describes how to establish if bottlenecks are in storage or formula engine.

Then document shows examples on how actually perform analysis on sample queries, explaining all results and provides recommendations on how to improve MDX query performance.

Amongst other examples there are suggestions on how to do Ranking over sets and demonstrates performance difference between CASE and IIF statements.

I just started to read this document, but already saw a lot of very useful information. You can find this paper here.