ANCESTOR MDX Function

I recently started looking up on MDX Functions and one of the first MDX Function I came across was ANCESTOR. Although, MSDN does provide some very good resource on MDX functions, but it was not so simple for me. Either I am totally new to MDX or I am too lame.

MSDN tries to explain the ANCESTOR MDX function using the Product Hierarchy on the Adventure Works Database but I think Date Hierarchy can be more cool.

So, here we go...

As the name suggests, the Ancestor MDX expression helps you find the Ancestor of any MDX Expression. You can specify the either the no. of levels you want to go back or also an expression specifying a level.

The syntax for Ancestor is as follows:
Ancestor(Member_Expression, Level)
or
Ancestor(Member_Expression, Distance)

Thus, for example, if the Date Hierarchy is as follows:
Year -> Semester -> Quarter -> Month -> Date
The MDX expression results to 04th October 2003
and you want to know the total Internet Sales Amount for the Semester this date belongs to... you will use the following syntax

SELECT {
ANCESTOR(
[Date].[Calendar].[Date].[October 4, 2003], --Member Expression
[Date].[Calendar].[Calendar Semester]) --Level Expression
} ON COLUMNS,
[Measures].[Internet Sales Amount] ON ROWS
FROM [Adventure Works]

Please note as the Member Expression can return sets, this is used with { }.

If you want to know the Internet Sales Amount for the Month, Quarter and Year based on the given date you will use the following syntax

SELECT {
ANCESTOR([Date].[Calendar].[Date].[October 4, 2003], [Date].[Calendar].[Month]),
ANCESTOR([Date].[Calendar].[Date].[October 4, 2003], [Date].[Calendar].[Calendar Quarter]),
ANCESTOR([Date].[Calendar].[Date].[October 4, 2003], [Date].[Calendar].[Calendar Year])
} ON COLUMNS,
[Measures].[Internet Sales Amount] ON ROWS
FROM [Adventure Works]

The syntax produces the following output






You can use the following syntax to use the Distance (Numeric Expression) to get the same output.

SELECT {
ANCESTOR([Date].[Calendar].[Date].[October 4, 2003], 1),
ANCESTOR([Date].[Calendar].[Date].[October 4, 2003], 2),
ANCESTOR([Date].[Calendar].[Date].[October 4, 2003], 4)
} ON COLUMNS,
[Measures].[Internet Sales Amount] ON ROWS
FROM [Adventure Works]

Remeber from Date, Month is 1 Level up, Quarter is 2 Levels up and Year is 4 Levels up..

Please note the Ancestor Function always returns a single value..
Interestingly, there is also an MDX Function ANCESTORS but I could not find the difference between two. Even the Ancestors return a single value..

The MSDN article on ANCESTOR can be access here...

Sharepoint in Plain English

The very simple video by CommonCraft explains the concept of Microsoft Sharepoint Server in "plain english".


SharePoint in Plain English

Special Characters in a Table Name

The last day we accidentally changed a table name from 'dbo.TableName' to 'db.TableName'.

Afterwards we were not able to access the table at all, although we could see the table on querying sys.objects or INFORMATION_SCHEMA.TABLES.

The first impression was that the table schema/ownership got changed from dbo to db. However, the table was still existing in the same dbo schema but the name of the table became 'db.TableName' from 'TableName'.

Thus to rename the table I used the following command.
EXEC sp_Rename 'dbo.[db.TableName]', 'TableName'

Please note the use of [] brackets. It is used to embed special characters in the SQL Syntax.
Also, in the second parameter we do not specify the schema/username.

Unable to remove Partition Filegroup

Today while removing a Filegroup we came across the famous error message.
The filegroup FileGroupNameXXX cannot be removed because it is not empty.

Normally, on getting this error message the first thing I do is try to shrink the Filegroup
DBCC SHRINKFILE (FileGroupNameXXX, 0)
This will clear all unused space from it.

However SQL Server informed me that the File does not exist. On further checking I noticed that the Filegroup does not have an associated File or Partition Scheme associated with it. Then why is it not empty and what is associated with it?

At this point, SQL Server DMV (Dynamic Management Views) came to my rescue and the following command showed there is a table along with an index still associated with the Filegroup.

SELECT ds.name, i.name, o.name
FROM sys.data_spaces ds
inner join sys.indexes i on i.data_space_id = ds.data_space_id
inner join sys.objects o on i.object_id = o.object_id
where ds.name = FileGroupNameXXX

This gave me the name of the Objects associated. So, I first dropped the table and ran the following command to remove the FileGroup.

ALTER DATABASE TestDB REMOVE FILEGROUP FileGroupNameXXX

Hope this helps...

Rename a Table or a Column

Microsoft SQL Server has a very cool function to change the name of table or column or indexes.. sp_rename

To rename a table
EXEC sp_rename 'dbo.Cust' 'Customer'
The above command renames the table Cust to Customer in the dbo schema.

To rename a column
EXEC sp_rename 'dbo.Cust.CustName' 'CustomerName', 'COLUMN'
The above command renames the Column CustName to CustomerName on the table dbo.Cust

You can use the same command to rename an Index as well.
EXEC sp_rename 'dbo.Customer.idx_CustomerId', 'idx_Customer_CustomerId', 'INDEX'

More details about the sp_rename on MSDN Link

Clean Partition on SQL Server

During the development and testing for Partitions on SQL Server, I have come across this question a lot of time "How do we clear the partition, file, filegroup, partition scheme and function and then start all over again?"

This can be a really tedious process, if you do not do it right and in the right order. This post documents on the steps you need to take to clean everything and start from scratch.

The first thing I always suggest is move all the tables that are on the said partition back to PRIMARY file group. You can check the steps here.

Once you have moved all the tables to the PRIMARY file group, the second step is to remove the Partitioned file groups. Assuming you have a database named TestDB and the Partition Filegroups name is something like TEST_PARTITION_2009_01, TEST_PARTITION_2009_02 and so on for 12 months, run the following syntax

ALTER DATABASE TestDB REMOVE FILE TEST_PARTITION_2009_01
ALTER DATABASE TestDB REMOVE FILE TEST_PARTITION_2009_02
ALTER DATABASE TestDB REMOVE FILE TEST_PARTITION_2009_03
ALTER DATABASE TestDB REMOVE FILE TEST_PARTITION_2009_04
ALTER DATABASE TestDB REMOVE FILE TEST_PARTITION_2009_05
ALTER DATABASE TestDB REMOVE FILE TEST_PARTITION_2009_06
ALTER DATABASE TestDB REMOVE FILE TEST_PARTITION_2009_07
ALTER DATABASE TestDB REMOVE FILE TEST_PARTITION_2009_08
ALTER DATABASE TestDB REMOVE FILE TEST_PARTITION_2009_09
ALTER DATABASE TestDB REMOVE FILE TEST_PARTITION_2009_10
ALTER DATABASE TestDB REMOVE FILE TEST_PARTITION_2009_11
ALTER DATABASE TestDB REMOVE FILE TEST_PARTITION_2009_12

Third, remove the Partition Scheme and Function. Replace the Partition scheme and Function name accordingly.

DROP PARTITION SCHEME TEST_PARTITION_Part_Sch
DROP PARTITION FUNCTION TEST_PARTITION_Part_Func

Finally, you need to remove the Partition FileGroups from the database. I assume the same naming convention..

ALTER DATABASE TestDB REMOVE FILEGROUP TEST_PARTITION_2009_01
ALTER DATABASE TestDB REMOVE FILEGROUP TEST_PARTITION_2009_02
ALTER DATABASE TestDB REMOVE FILEGROUP TEST_PARTITION_2009_03
ALTER DATABASE TestDB REMOVE FILEGROUP TEST_PARTITION_2009_04
ALTER DATABASE TestDB REMOVE FILEGROUP TEST_PARTITION_2009_05
ALTER DATABASE TestDB REMOVE FILEGROUP TEST_PARTITION_2009_06
ALTER DATABASE TestDB REMOVE FILEGROUP TEST_PARTITION_2009_07
ALTER DATABASE TestDB REMOVE FILEGROUP TEST_PARTITION_2009_08
ALTER DATABASE TestDB REMOVE FILEGROUP TEST_PARTITION_2009_09
ALTER DATABASE TestDB REMOVE FILEGROUP TEST_PARTITION_2009_10
ALTER DATABASE TestDB REMOVE FILEGROUP TEST_PARTITION_2009_11
ALTER DATABASE TestDB REMOVE FILEGROUP TEST_PARTITION_2009_12

And voila... you have a clean slate now. Enjoy

Service Oriented Architecture Podcast

A compilation of podcasts related to Service Oriented Architecture (SOA) spread across the internet.

What is SOA? From whatis.com, sponsored by IBM

Another good article explaining what is SOA?
An introduction to SOA on Javaworld.com