Tuesday, August 30, 2005

Added A Post

I have added a post about MSAS 2005 aggregations to the MSAS section.

Monday, August 29, 2005

I am breaking up ....

I have created two new categories in my blog as i expect to making a larger amount of postings

markiehillmsis.blogspot.com will cover MSIS
markiehillas.blogspot.com will cover MSAS

If i get around to pocking more in data mining and reporting services i will create some categories for them.

General ramblings about weird stuff can still be found here at the main site. If you want to subscribe to the above then just stick a /atom.xml onto the end.

You can reach these sites from the categories link on the left hand side !!

Restricting Rows In Analysis Services 2005

Quick Tip:

When working with a cube and you want to restrict the rowset that is returned for testing etc you can do this within the DSV.

Right click on the fact table and select the replace table with named query option. You will then be presented with a query editor, edit the select statement to include select top x where x is the number of rows you want to test with.

This saves you from having to ask for or change database objects / views etc , pretty cool !

Saturday, August 27, 2005

Moving cube directories in SQL Server 2005

I ran out of space in my VMware session and had to create a new drive for my AS cubes, this means that i had to move them from their current location to a newly created partition.

This is the process i used, i know you can edit some of this stuff through the GUI , but its far more fun to play with ini files (isn't it)?

All this information applies to the June CTP build, i am guessing it will remain relevant for future builds.

Moving cube directories in SQL server 2005 is fairly easy, all the options for the OLAP component of the software are stored in the installation directory under \MSSQL.2\OLAP\config in a file called msmdsrv.ini

Stop the SQL Server OLAP service.

Before editing anything you should take a copy of this file (store it as msmdsrv.bak) so you can find it later should you need it :)

Underneath the Configuration Settings entry you will see , ensure the service is stopped and then change this value to your new cube directory (remember to save it).
For example my new setting would be e:\as\data

Copy the contents of the old directory which are by default stored in your installation directory under MSSQL.2\OLAP\Data to your new directory and restart the service.

Job Done !!!

Thursday, August 25, 2005

I quit !

Today i quit my job :)

Thursday, August 18, 2005

Integration Services Puzzle

A friend of mine emailed me today and asked how can you get SQL Server 2005 Integration Services to substitue a null to a value (like unknown).

He had been playing around with expressions and was struggling to work out how to make a conditional decision within the expression itself. So without getting to the argument of whether an expression is the correct place to do this, i thought i would take a look.

I have to say it was not immediately obvious to me how you would approach this, however after a little trial and error , the ? operator seems to work nicely and does the trick

(boolean expression?expression 1:expressio2)

So in our case we would have , ISNULL(CUSTOMERNAME)?"Unknown":CUSTOMERNAME

Which provides the desired result.