SQL Server

SQLBits X PreCon SSAS Deep Dive Summary – Part 1

Posted by Jens Vestergaard on in Programming 0 Comment

As an exercise for myself I will be writing fairly short post on the session I attended at SQLBits X´. I will begin with the training day where I went for the Deep Dive into SSAS.

The only bad thing there was to say about the SSAS Deep Dive precon by Akshai Mirchandani, was that he had so much material, that he had to skip some. Besides that it was a fantastic whole-day session.

The base of the material was taken from the SSAS Maestro program, and from several of the white papers and guideline documents out there. See a complete list by Vidas Matelis (blog|twitter) here.

General view of SSAS

For starters Akshai took the crowd through the major parts contributing to the execution of an incoming query. The main parts are illustrated on the following diagram:

MDX Query execution architecture
MDX Query Execution

What this diagram doesn’t show, is the change made in the 2012 edition, to better serve processing and querying by allowing a split of the two on separate thread pools. The 2012 edition will have two (2) thread pools; one for process jobs and one for IO. Read more info about this new feature here.

Dimension Design 1on1

This was followed by a best practise walk-through on designing dimensions in a cube.
Some of the highlights were:

  • Simple Cubes => Faster
  • String AttributeKey => BAD
  • Dummy attributes
  • Natural Hierarchies
  • String Store (4 GB limit)

The “dummy attribute” construction was new to me, and among the note taking and listening I lost the general concept. But I believe it had to do with dimensions where no immediate relationship could be made. Anybody reading this, please feel free to fill in the big blanks.

Akshai then continued on to give a brush up on Parent-Child (PC) Hierarchies in Analysis Services. Here he wanted to debunk some of the myths out there in regards to size and usage. In general the guidelines he gave was that one or two PC Hierarchy is acceptable in a cube. In SQL Server 2005 there was a guideline saying that when ever a dimension with a PC Hierarchy has more than 250K members, it was advised to convert this hierarchy into a natural hierarchy. This can be done by using the Parent-Child Dimension Naturalizer found at CodePlex. Akshai stated that the 250K members limit was not advised in the SQL Server 2008 edition and forward, but when performance issues arrises it could be a solution to convert a PC Hierarchy.

In regards to Many-To-Many (M2M) dimension relationships Akshai had an interesting notion on compression. Luckily BIDS Helper has a build in feature that generates the rather complex SQL needed for this operation. The technique  is described in this white paper.
In general Akshai recommended BIDS Helper and highlighted several of the tool features.

ΑΩ – Biggest take-away

The maybe biggest take away from the part on dimensions was to always check if you really need the AttributeHierarchyEnabled to be true on an attribute. There is much to gain by going through your attributes disabling those you only need as detail and not in hierarchies. See this TechNet article on the subject.

Loading

SSMS Quick Tip

Posted by Jens Vestergaard on in Programming 0 Comment

If you ever need to test Analysis Services roles, you can easily do so either by browsing the cube or by means of a query.

When browsing a cube through SQL Server Management Studio (SSMS) or Bisuness Intelligence Developer Studio (BIDS), you can select one or more roles to be enforced on data, by clicking the little “user” icon in the top left corner of the cube browser:

You will then be able to select on or more of the roles available on the database via a dialog:

This way of browsing roles has the following pros and cons:
Pros: Visual confirmation of PC Hierarchies
Cons: A lot of clicking top verify permission integrity

The other way of testing roles is through SSMS is by creating an MDX query. The trick is to click the options button when the connection dialog is active. The last tab allows you to specify additional connection parameters. The Roles property is described in detail at technet. Basically it’s a comma seperated list of roles (case sensitive). The trick is showed in the screendump below:

Pros: Custom queries
Cons: No visual confirmation of integrity, can be hard to visually confirm permissions on a PC hierarchy as an example

Loading

Renaming the name of the physical database and log file

Posted by Jens Vestergaard on in Programming 0 Comment

During one of my current tasks, which was to run the same test on multiple copies of the same database, I needed to setup a flow where I could

  1. deploy the database from VS2010 or setup database w/ vsdbcmd
  2. run the test (import 5.5 mio facts + 250K dimension elements into a dw model)
  3. rename the database including the physical files
  4. goto step 1 w/out overwriting the databases already created

Since both a deploy from VS2010 and a setup run through vsdbcmd will produce the same name on the physical files over and over again, I needed to alter these names. Here’s how the script looks:

USE [master]

DECLARE @Database NVARCHAR(255) = '<name of database>'
DECLARE @BackupDevice NVARCHAR(255) = N'<full path to backupmedia>'
DECLARE @CurrentDataFileName NVARCHAR(255) = 
              N'<name of logical database file>'
DECLARE @TargetDataFileFullPath NVARCHAR(255) = 
              N'<operating system file name for new database file>'
DECLARE @CurrentLogFileName NVARCHAR(255) = 
              N'<name of logical log file>'
DECLARE @TargetLogFileFullPath NVARCHAR(255) = 
              N'<operating system file name for new log file>'

DECLARE @SQL VARCHAR(MAX) = N'
RESTORE DATABASE ' + @Database + N'
FROM  DISK = N''' + @BackupDevice + '''
WITH  FILE = 1,  
MOVE N''' + @CurrentDataFileName + ''' 
TO N''' + @TargetDataFileFullPath + ''',  
MOVE N''' + @CurrentLogFileName + ''' 
TO N''' + @TargetLogFileFullPath + ''',  STATS = 10
'
--PRINT @SQL
EXEC (@SQL)

rename_database_physical_files.sql

Loading

Installing MS SQL Denali CTP3 failed

Posted by Jens Vestergaard on in Programming 3 Comments

While I was on vacation, the news of the Denali CTP3 release (ref) reached me. When I got back to work I immediately downloaded the package. From there on it would be a smooth ride through the wizard, or at least so I imagined it. Only once before have I met problems installing a version of SQL Server on a machine, that was when I tried to install SQL Server 2005 Dev Ed. on an ASRock HT330. That didn’t work out too well. That aside, I wasn’t expecting any problems, since I was installing the CTP3 on a brand new stationary machine (Windows 7 Ent.) with lots of power.

Power
Current desktop

This isn’t a power issue though, going through the steps of the wizard, I’ll explain my actions and the outcome.

First the welcome screen, we you can view all kinds of requirements and documentation.

Welcomescreen

Selecting ‘Install‘ to proceed with the installation, and choosing the ‘New SQL Server stand alone…‘ option

Install

Click ‘OK’ on the Setup Support Rules dialog

Setup Support Rules

Click ‘Next’ on the Product Update dialog

Product Updates

The Install Setup Files dialog will show up (not clicking anything here)

Installing Files

Going through the Setup Support Rules, inspecting the Firewall warning. Since this is for local testing only this issue is not solved and should cause any problems.

Setup Support Rules

The details of the Windows Firewall warning

Firewall warning

Clicking ‘Next’ brings us to the Product Key dialog, where we will select ‘Evaluation’ as free edition

Product Key

Onwards to the License Terms dialog, where we will accept and choose to send any information to Microsoft

License Terms

On to features selection on the Setup Role dialog, where we will select ‘All Features With Defaults’

Setup Role

A detailed view awaits on the Feature Selection dialog, where ‘All Features With Defaults’ appears to not include ‘Reporting Services Add-in for Sharepoint products’ and ‘Master Data Services’, I wonder why…

Feature Selection

Now, the Installation Rules dialog is where I can’t get any further…

Install Issue

Errormessage

I’m stuck and can’t get any further. There was SQL Server 2005 client tools installed, but I uninstalled those the first time I was hit by the issue. I’ve tried this: http://bit.ly/pyt7mm and suggestions like it, w/out any effect.  I’ve tried to tweet about the issue and was by guided to: http://cot.ag/9OUV5A and http://cot.ag/qnzZid by @MicrosoftBI. None of those two provided any solution to the issue at hand.

How to get past this rather frustrating installation rule? And why does SQL Server 2005 Express tools block Denali CTP3?

Just as documentation, no SQL Server Express installed, allthough ‘Microsoft SQL Server “Denali” CTP3 Setup (English)‘ is listed twice…:

Programs and Features

There is a forum post on the issue: http://bit.ly/qjLTsJ

UPDATE:

I’ve tried to run the installer with the parameter /skpirules=Sql2005SsmsExpressFacet as show in picture, but the error persists:Command prompt

Error page #2

Loading