Hitachi Consulting Microsoft BI

Automating Partition Creation

2007-09-27T17:39:00.001-07:00

By Bryan C. Smith

The use of multiple partitions per measure group is promoted to improve both query and processing performance as well as the overall manageability of cubes. Partitioning strategies are often time-based, so that a single partition may represent a specific year, quarter, month, or some other unit of time.

One of the central challenges of partitioning a measure group on time (or any other non-static dimension) is that new partitions may be required over the life of the cube. If it can be predicted when these partitions might be needed, the required partitions can be manually created in advance by the admin. Where values cannot be predicted, an "overflow" partition can be created to catch any values not attributed to other, more precisely defined partitions.

Still, having the ETL layer query the database to determine the partitions required and then create those partitions on your behalf is a much more attractive approach. Below is a simplistic script demonstrating the use of AMO to do just that.

The script is written for execution within an SSIS Script task and requires a reference to the AMO library. Its goal is to add any year-based partitions to the Reseller Sales measure group within the Adventure Works cube of the Adventure Works DW multidimensional database. It is tested by dropping any partition from the Reseller Sales measure group.

Here is a quick rundown of the logic.

The script starts by establishing references to the Adventure Works cube and the Reseller Sales measure group within the Adventure Works DW multidimensional database. A query is issued against the DimTime table in the AdventureWorksDW relational database to determine which years exist within the table and the key ranges of the records associated with those years. (There is a critical assumption here that records in the DimTime table are sequenced by date. This may not be true within your data warehouse.)

The routine then loops through each partition in the Reseller Sales measure group to determine is a partition for that year exists. If not, a new partition is created with the appropriate query bindings. The first aggregation design used within the measure group is assigned to the new partition. (This is not a recommended approach but simply serves to demonstrate how aggregations can be assigned to new partitions.) The partition is then associated with the measure group and the change is submitted to SSAS. (It is important to note, the new partition is unprocessed at this time.)

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.AnalysisServices

Public
Class ScriptMain

Public
Sub Main()

'Locate Measure Group of Interest (and a Known Partition)

'---------------------------------------------

Dim MyServer As
New Server

MyServer.Connect("localhost")

Dim MyDatabase As Database = _

MyServer.Databases.GetByName("Adventure Works DW")

Dim MyCube As Cube = MyDatabase.Cubes.GetByName("Adventure Works")

Dim MyMeasureGroup As MeasureGroup = _

MyCube.MeasureGroups.GetByName("Reseller Sales")

'Retrieve Years in Dimension

'---------------------------------------------

Dim MyConnectionString As
String

MyConnectionString = "Server=localhost;" + _

"Database=AdventureWorksDW;Integrated Security=SSPI;"

Dim MySqlConnection As
New SqlClient.SqlConnection(MyConnectionString)

Dim MySqlCommand As
New SqlClient.SqlCommand( _

MySqlQueryString, MySqlConnection)

MySqlConnection.Open()

Dim MySqlReader As SqlClient.SqlDataReader = MySqlCommand.ExecuteReader

'Add Partitions That Don't Exist

'---------------------------------------------

While MySqlReader.Read

Dim MyYear As
String = MySqlReader.GetString(0)

Dim MyStartKey As Int32 = MySqlReader.GetInt32(1)

Dim MyEndKey As Int32 = MySqlReader.GetInt32(2)

Dim MyPartitionName As
String = "Reseller_Sales_" + MyYear

Try

Dim MyPartition As Partition = _

MyMeasureGroup.Partitions.GetByName(MyPartitionName)

Catch

Dim MyPartition As
New Partition

With MyPartition

.Name = MyPartitionName

If MyMeasureGroup.AggregationDesigns.Count > 0 Then

.AggregationDesignID = _

MyMeasureGroup.AggregationDesigns.Item(0).ID

End
If

.Source = New QueryBinding(MyCube.DataSource.ID, _

MyParitionQueryString(MyStartKey, MyEndKey))

End
With

MyMeasureGroup.Partitions.Add(MyPartition)

MyPartition.Update()

End
Try

End
While

MySqlReader.Close()

MySqlConnection.Close()

MyServer.Disconnect()

Dts.TaskResult = Dts.Results.Success

End
Sub

Private
Function MySqlQueryString() As
String

Dim Ret As
String

Ret = "select CalendarYear," + _

"min(TimeKey),max(TimeKey) " + _

"from dbo.DimTime " + _

"Group by CalendarYear"

Return Ret

End
Function

Private
Function MyParitionQueryString(ByVal StartKey As Int32, _

ByVal EndKey As Int32) As
String

Dim Ret As
String

Ret = "SELECT " + _

"[dbo].[FactResellerSales].[ProductKey]," + _

"[dbo].[FactResellerSales].[OrderDateKey]," + _

"[dbo].[FactResellerSales].[DueDateKey]," + _

"[dbo].[FactResellerSales].[ShipDateKey]," + _

"[dbo].[FactResellerSales].[ResellerKey], " + _

"[dbo].[FactResellerSales].[EmployeeKey]," + _

"[dbo].[FactResellerSales].[PromotionKey]," + _

"[dbo].[FactResellerSales].[CurrencyKey]," + _

"[dbo].[FactResellerSales].[SalesTerritoryKey]," + _

"[dbo].[FactResellerSales].[SalesOrderNumber]," + _

"[dbo].[FactResellerSales].[SalesOrderLineNumber]," + _

"[dbo].[FactResellerSales].[RevisionNumber]," + _

"[dbo].[FactResellerSales].[OrderQuantity]," + _

"[dbo].[FactResellerSales].[UnitPrice]," + _

"[dbo].[FactResellerSales].[ExtendedAmount], " + _

"[dbo].[FactResellerSales].[UnitPriceDiscountPct], " + _

"[dbo].[FactResellerSales].[DiscountAmount], " + _

"[dbo].[FactResellerSales].[ProductStandardCost], " + _

"[dbo].[FactResellerSales].[TotalProductCost], " + _

"[dbo].[FactResellerSales].[SalesAmount], " + _

"[dbo].[FactResellerSales].[TaxAmt], " + _

"[dbo].[FactResellerSales].[Freight], " + _

"[dbo].[FactResellerSales].[CarrierTrackingNumber], " + _

"[dbo].[FactResellerSales].[CustomerPONumber]" + _

"FROM [dbo].[FactResellerSales] " + _

"WHERE OrderDateKey BETWEEN " + StartKey.ToString + " AND " + _

EndKey.ToString

Return Ret

End
Function

End
Class

AMO Script for Automating SSAS Database Backups

2007-08-21T12:56:00.001-07:00

By Bryan C. Smith

I apologize in advance, but I'm afraid I need to get up on my soapbox for this one. Bear with me.

START SOAPBOX

I have not seen folks pursuing regular backups of multidimensional databases as aggressively as they do relational databases. Part of this is an attitude that multidimensional databases are simply redundant copies of their relational data sources. Still another part of this is a lack of familiarity with the SSAS technology and the tools available to support regular backups. Whatever the reason, the "zero data loss" mentality has not spread to the multidimensional landscape.

So, let's clearly state a few things. Multidimensional databases do occasionally get corrupted, developers do introduce errors, and systems do fail. Projects housing the database definition do get lost and corrupted as well, even in Visual Source Safe and other related systems.

Though you may have a good set of project files available with which to rebuild a multidimensional database, important changes can be introduced through management activities that are not reflected in the BIDS project. Furthermore, the time it takes to reprocess a cube following a failure is often not acceptable to end-users, especially when those databases support critical business processes.

It is important we insure regular backups are taken of our multidimensional databases and are managed in a manner commensurate with our relational data sources.

END SOAPBOX

So, I've taken a look at a few means for handling regular backups of multidimensional databases. In the end, I find AMO the most appealing tool for this.

Below is a sample script that you may wish to explore for your own purposes. This was implemented in the Script task of an SSIS package. If you wish to test the script out, be sure to add a reference to the Analysis Management Objects library in your script editor.

Also, I need to note that this is not intended to be deployed to production as is. If you wish to use this script in a production setting, please thoroughly test it, add appropriate error handling, etc. If you use all or part of this script, you assume responsibility for its behavior. Sorry, but I've got to say it.

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports Microsoft.AnalysisServices

Public
Class ScriptMain

Public
Sub Main()

'Get TimeStamp in form YYYYMMDDhhmmss

'------------------------------------------------------

Dim MyTimestamp As
String

MyTimestamp = CStr(Year(Now()) * 10000 + _

Month(Now()) * 100 + Day(Now()))

MyTimestamp += CStr(Hour(Now()) * 10000 + _

Minute(Now()) * 100 + Second(Now()))

'Connect to SSAS Instance

'------------------------------------------------------

Dim MyServer As
New Server

MyServer.Connect("localhost")

'Backup Databases

'------------------------------------------------------

Dim BackupFileName As
String

Dim AllowOverwrite As
Boolean = True

Dim BackupRemotePartitions As
Boolean = False

Dim MyLocation() As BackupLocation

Dim ApplyCompression As
Boolean = True

For
Each MyDatabase As Database In MyServer.Databases

BackupFileName = "c:\temp\" + MyDatabase.Name + _

"_" + MyTimestamp + ".abf"

MyDatabase.Backup(_

BackupFileName, AllowOverwrite, _

BackupRemotePartitions, MyLocation, _

ApplyCompression)

'Disconnect from SSAS Instance

'------------------------------------------------------

MyServer.Disconnect()

Dts.TaskResult = Dts.Results.Success

End
Sub

End
Class

Implementing User-Specific Security in SSAS

2007-08-19T08:07:00.001-07:00

By Bryan C. Smith

You often have a requirement to restrict access to members of a dimension on a user-specific basis. You can accomplish this through the use of what is referred to as "dynamic security". The purpose of this blog entry is to demonstrate a standard technique for implementing this.

In this demonstration, you will limit access to the Reseller dimension of the Adventure Works DW multidimensional database. You will implement structures in both this database and the associated AdventureWorksDW relational database. It is assumed these databases are hosted locally, and it is recommended you backup both databases prior to making any of the required changes.

In addition, you will leverage two local user accounts, TestUser1 and TestUser2, created for the purposes of this demonstration. The name of your local system is assumed to be MyLaptop so that the full names of these accounts are MyLaptop\TestUser1 and MyLaptop\TestUser2. When these exercises are completed, these two accounts should be dropped from your system.

The User Dimension

Your first objective is to create the User dimension. This dimension simply houses a list of end-user accounts. You start by implementing the DimUser table in the relational database:

create
table DimUser (

UserKey int identity(1,1) not
null,

UserName varchar(256)
not
null

)

alter
table DimUser add

constraint PK_DimUser primary
key
(userkey),

constraint AK_DimUser unique
(username)

We then add entries for our two test accounts:

insert
into DimUser (UserName) values
('MyLaptop\TestUser1')

insert
into DimUser (UserName) values
('MyLaptop\TestUser2')

TestUser1 and TestUser2 should have received UserKey values of 1 and 2, respectively.

In the multidimensional database, you now add the DimUser table to the DSV and create a new dimension, User, from it. The User dimension has a single attribute hierarchy, User, whose key and name are the UserKey and UserName fields, respectively.

The ResellerUser Fact Table

With the User dimension in place, you now need to construct a list of which resellers your individual users are allowed to see. In the relational database, you implement this through the fact-less fact table, FactResellerUser:

create
table FactResellerUser (

UserKey int
not
null,

ResellerKey int
not
null

)

alter
table FactResellerUser add

constraint PK_FactRsellerUser

primary
key
(resellerkey,userkey),

constraint FK_FactRsellerUser_UserKey

foreign
key
(UserKey)

references DimUser (UserKey),

constraint FK_FactRsellerUser_ResellerKey

foreign
key
(ResellerKey)

references DimReseller (ResellerKey)

For the purposes of this demonstration, let's say TestUser1 (UserKey=1) may see reseller's with ResellerKey values of 1 to 5:

insert
into FactResellerUser (UserKey, ResellerKey)
values
(1,1)

insert
into FactResellerUser (UserKey, ResellerKey)
values
(1,2)

insert
into FactResellerUser (UserKey, ResellerKey)
values
(1,3)

insert
into FactResellerUser (UserKey, ResellerKey)
values
(1,4)

insert
into FactResellerUser (UserKey, ResellerKey)
values
(1,5)

Let's then say TestUser2 (UserKey=2) may see reseller's with ResellerKey values of 3 to 7:

insert
into FactResellerUser (UserKey, ResellerKey)
values
(2,3)

insert
into FactResellerUser (UserKey, ResellerKey)
values
(2,4)

insert
into FactResellerUser (UserKey, ResellerKey)
values
(2,5)

insert
into FactResellerUser (UserKey, ResellerKey)
values
(2,6)

insert
into FactResellerUser (UserKey, ResellerKey)
values
(2,7)

In the multidimensional database, you now add FactResellerUser to the DSV. The relationships between this table and the DimReseller and DimUser tables should be reflected in the DSV as well.

In the Adventure Works cube, you then create a new measure group, Reseller User, based on the FactResellerUser table. It will contain a single measure using the count aggregation function. The BIDS Cube Designer should automatically add the User dimension to the cube and associate the Reseller User measure group with it. The Cube Designer should also automatically associate our measure group with the Reseller dimension. You will need to confirm this and manually update the cube if necessary.

Cleaning Up the Cube

The Reseller User measure group and the User dimension will provide the basis for your user-specific security, but these do not represent items with which you intend to have your users directly interact. Your objective now is to hide these items.

To hide the measure group, set the Visible property on its single measure to False. With no visible measures, the measure group will not be displayed to end-users. To hide the User dimension in the cube, set the Visible property on the cube dimension to False.

It is important to keep in mind you have only hidden these items. End-user's aware of their presence can still query them.

Setting-Up the Role

Now it's time to set up the role in the multidimensional database. You create a new role, MyRole, with Read access to the Adventure Works cube. You add TestUser1 and TestUser2 as members.

Your next step is to limit access to the User dimension. You don't want end-users to query the User dimension and see a list of user accounts with access to the database. To secure it, you set an allowed set on the User attribute of the User database dimension as follows:

STRTOSET("[User].[User].["+Username+"]")

The Username function returns a string which is the full name of the end-user's user account. For example, when your TestUser1 user connects to the multidimensional database, the Username function will return "MyLaptop\TestUser1".

The Allowed Set requires you to specify a set of members in the User attribute hierarchy. You concatenate the string returned by the Username function with other strings to assemble a definition for a valid, one-member set. You then convert that string into an actual set with the STRTOSET function.

Now it's time focus your attention on the Reseller dimension. You will leverage the relationship between the User dimension and the Reseller dimension captured in the Reseller User measure group. Of course, this relationship exists within the Adventure Works cube so you will be working with the Reseller cube dimension.

For the Allowed Set on the Reseller attribute hierarchy, you specify the following:

EXISTS(

[Reseller].[Reseller].Members,

STRTOSET("[User].[User].["+Username+"]"),

'Reseller User'

)

The EXISTS function returns the set of members from the first set, [Reseller].[Reseller].Members, associated with the members in the second set, STRTOSET("[User].[User].["+Username+"]"), as determined by a specified measure group, 'Reseller User'. It is important to note that in indicating measure group, you are must state the measure group's name, not the name of a measure within the measure group, and that name must be enclosed in single-quotes. Many first time user's of this variation on the EXISTS function are frequently confused by these points.

Testing Security

Once the MyRole is deployed to the SSAS instance, you need to test the set up. To do this, you run SQL Server Management Studio as one of our test users, TestUser1. (From the Start menu, select Programs then Microsoft SQL Server 2005. Right-click the SQL Server Management Studio icon and select Run As from the context menu. Enter the appropriate account information to start SSMS as TestUser1.)

Once started, you open an MDX Query window and issue the following statement:

select

{} on 0,

[User].[User].Members
on 1

from [Adventure Works]

;

All

MyLaptop\TestUser1

You then execute this statement:

with
member [Measures].[Reseller Key] as

[Reseller].[Reseller].CurrentMember.UniqueName

select

[Measures].[Reseller Key] on 0,

[Reseller].[Reseller].Members
on 1

from [Adventure Works]

;

	Reseller Key
All Resellers	[Reseller].[Reseller].[All Resellers]
A Bike Store	[Reseller].[Reseller].&[1]
Advanced Bike Components	[Reseller].[Reseller].&[3]
Metropolitan Sports Supply	[Reseller].[Reseller].&[5]
Modular Cycle Systems	[Reseller].[Reseller].&[4]
Progressive Sports	[Reseller].[Reseller].&[2]

Non-Deterministic Functions in Named Sets

2007-08-13T13:04:00.001-07:00

By Bryan C. Smith

Named sets are a great way to encapsulate complex or frequently used set definitions. There are three ways to create a named set:

The WITH SET clause of an MDX SELECT statement
The CREATE SESSION SET statement
The CREATE SET statement within the cube calculations script

Which of these you select depends on your needs and determines how and when the set is resolved. This is critical to understand when using non-deterministic functions as demonstrated in the following exercises.

These exercises assume you have a copy of the Adventure Works DW database running on a local instance of SSAS 2005. It is also assumed you have the permissions needed to adjust the Date/Time settings of this system.

The WITH SET clause

Change your system's time to the year 2004. Open an MDX Query window in SSMS, connect to the local instance containing Adventure Works DW, and execute the following:

with
set [Current Year] as

STRTOMEMBER("[Date].[Calendar Year].[CY "+CSTR(YEAR(NOW()))+"]")

select

[Measures].[Reseller Sales Amount] on 0,

[Current Year] on 1

from [Adventure Works]

;

	Reseller Sales Amount
CY 2004	$16,038,062.60

Change your system's time to the year 2003 and re-execute the same query:

	Reseller Sales Amount
CY 2003	$32,202,669.43

The named set created with the WITH SET clause is query-scoped. It is compiled and resolved with each execution of the query but is not available to other queries unless the set definition is repeated.

The CREATE SESSION SET Statement

Set your system's time to the year 2004 and execute this query to create the named set:

create
session
set [Adventure Works].[Current Year] as

STRTOMEMBER("[Date].[Calendar Year].[CY "+CSTR(YEAR(NOW()))+"]")

;

With execution completed, change your system's time to the year 2003. Now, execute this query:

select

[Measures].[Reseller Sales Amount] on 0,

[Current Year] on 1

from [Adventure Works]

;

	Reseller Sales Amount
CY 2004	$16,038,062.60

A couple things are happening here. First, the CREATE SESSION SET statement is compiled separate from the query. Any query utilizing the connection within which the set was compiled can leverage it. Once the connection is terminated, the set is lost.

Next, the set is resolved to its members when the CREATE SESSION SET is submitted. Set membership does not change until the set is dropped and recreated.

The CREATE SET Statement in the Calculations Script

Change your system's time back to the year 2004. In BIDS, add the following to the calculations script and redeploy the cube:

create
set [Adventure Works].[Current Year] as

STRTOMEMBER("[Date].[Calendar Year].[CY "+CSTR(YEAR(NOW()))+"]")

;

Change your system's time to the year 2003, reconnect to the Adventure Works DW database, and execute the following query.

select

[Measures].[Reseller Sales Amount] on 0,

[Current Year] on 1

from [Adventure Works]

;

	Reseller Sales Amount
CY 2003	$32,202,669.43

Now, reset your system's time to the year 2004 and re-execute the query from above:

	Reseller Sales Amount
CY 2003	$32,202,669.43

Open a new query window and execute the SELECT statement again:

	Reseller Sales Amount
CY 2003	$32,202,669.43

With your system's time still set to the year 2004, restart the SSAS 2005 service and reconnect. Execute the query again:

	Reseller Sales Amount
CY 2004	$16,038,062.60

Unlike the session-scoped set, the named set is not resolved until the first time it is used. The members of that set are then defined and used across all queries until the SSAS service is restarted. With the first query submitted against the restarted service, the set's membership is defined again.

Before going further, please reset your system to the current time.

Additional Thoughts

I originally wrote this blog entry to use the RND function as my non-deterministic function. I would randomly select a Reseller to demonstrate the patterns shown above. I found an unusual pattern when I deployed the named set as part of the calculations script. Restarting the SSAS service resulted in a random member being returned with the first query, but redeployment of the database reset the membership of the set to the same member every time. I guess the take home message here is when using non-deterministic functions in your named sets, be sure to understand the basic patterns and test thoroughly.

Data Member, Part 2

2007-07-25T19:58:00.001-07:00

By Bryan C. Smith

In my previous post, you took a look at Amy, an employee who supervises three other employees, Jae, Rachel, and Ranjit. Each of these four employees has sales quotas associated with them. Since Amy is the supervisor of Jae, Rachel, and Ranjit, her sales quota reflects her personal sales quota as well as those from Jae, Rachel, and Ranjit. You used the DataMember function to separate the sales quota directly attributable to Amy from her total sales quota figure:

with
member [Measures].[Personal Quota] as

([Employee].[Employees].DataMember, [Measures].[Sales Amount Quota]),

format="Currency"

select

{[Measures].[Sales Amount Quota],[Measures].[Personal Quota]} on 0,

DESCENDANTS([Employee].[Employees].[Amy E. Alberts],1,SELF_AND_BEFORE) on 1

from [Adventure Works];

	Sales Amount Quota	Personal Quota
Amy E. Alberts	$24,202,000.00	$1,124,400.00
Jae B. Pak	$12,547,100.00	$12,547,100.00
Rachel B. Valdez	$3,269,800.00	$3,269,800.00
Ranjit R. Varkey Chudukatil	$7,260,700.00	$7,260,700.00

Jae, Rachel, and Ranjit are referred to as leaf members of the hierarchy while Amy is referred to as a non-leaf member. Leaf members have no children while non-leaf members do. (At this point, you haven't actually proven Jae, Rachel, and Ranjit have no children, but you will in the next query.)

In a parent-child hierarchy it is common for both leaf and non-leaf members to have data in the fact table directly associated with them. The hierarchy's MembersWithData property determines how SSAS presents this data to you.

If the MembersWithData property is set to NonLeafMembersHidden, which is the default when designed in BIDS, you get the behavior we see above – the values directly attributed to a non-leaf member are simply presented as part of the total value for that member.

You've seen you can get to the value associated with the non-leaf member with the DataMember function. Still, SSAS has the ability to present this another way.

If you set the MembersWithData property to NonLeafMembersVisible, SSAS will present the value directly attributed to a non-leaf member as a child of that member. (Changing the value will require a re-deploy but not a re-process.) The easiest way to explain this is to simply demonstrate it:

with
member [Measures].[Personal Quota] as

([Employee].[Employees].DataMember, [Measures].[Sales Amount Quota]),

format="Currency"

member [Measures].[Member Level] as

[Employee].[Employees].CurrentMember.Level.Name

member [Measures].[Number of Children] as

[Employee].[Employees].CurrentMember.Children.Count

select

{[Measures].[Sales Amount Quota],

[Measures].[Personal Quota],

[Measures].[Member Level],

[Measures].[Number of Children]} on 0,

DESCENDANTS([Employee].[Employees].[Amy E. Alberts],1,SELF_AND_BEFORE) on 1

from [Adventure Works];

	Sales Amount Quota	Personal Quota	Member Level	Number of Children
Amy E. Alberts	$24,202,000.00	$1,124,400.00	Employee Level 04	4
Amy E. Alberts	$1,124,400.00	$1,124,400.00	Employee Level 05	0
Jae B. Pak	$12,547,100.00	$12,547,100.00	Employee Level 05	0
Rachel B. Valdez	$3,269,800.00	$3,269,800.00	Employee Level 05	0
Ranjit R. Varkey Chudukatil	$7,260,700.00	$7,260,700.00	Employee Level 05	0

Take a close look at the results. You've calculated the level name along with the number of children for each member. The number of children identifies non-leaf and leaf members. The member level helps you identify how these members are represented in the hierarchy.

With the MembersWithData property set to NonLeafMembersVisible, a leaf member representation of Amy is added to our results as a child of the Amy non-leaf member. The non-leaf Amy member is just as before and the leaf Amy member simply holds the quota value directly attributable to Amy.

Understanding the difference between these two representations of Amy without the benefit of a walk-through such as this is a little confusing. The MembersWithDataCaption property exists to address this problem.

The property accepts a string that serves as a template for the name of the SSAS-generated non-leaf member. An asterisk (*) in the string serves as a placeholder for the original member name. A common string used for MembersWithDataCaption property is * (data). Here are the results from the query above with the MembersWithDataCaption property set to this value:

	Sales Amount Quota	Personal Quota	Member Level	Number of Children
Amy E. Alberts	$24,202,000.00	$1,124,400.00	Employee Level 04	4
Amy E. Alberts (data)	$1,124,400.00	$1,124,400.00	Employee Level 05	0
Jae B. Pak	$12,547,100.00	$12,547,100.00	Employee Level 05	0
Rachel B. Valdez	$3,269,800.00	$3,269,800.00	Employee Level 05	0
Ranjit R. Varkey Chudukatil	$7,260,700.00	$7,260,700.00	Employee Level 05	0

If you remember back to my previous blog entry, I started out with a simple breakdown of these sales quotas. Using the MembersWithData and MembersWithDataCaption properties, we've come full circle having SSAS provide us the data in a similar structure:

Amy $24M Total

Amy (personal) $1M

Jae $13M

Rachel $3M
Ranjit $7M

So, what are the right settings for the MembersWithData and MembersWithDataCaption properties? It really depends on your requirements. I recommend fully understanding how data is presented in a parent-child hierarchy and then sitting down with your end-user representatives to discuss the options.

By their nature, parent-child hierarchies can be slightly difficult for users to wrap their heads around. Walking users through a common example of parent-child hierarchy, such as employees in an organization, helps to explain the basic concepts. Using sample data sets, such as the AdventureWorks DW database, to illustrate these concepts can also assist with their understanding.

It's also important to keep in mind these properties can be changed relatively quickly should you decide at a later date you would prefer your data presented another way. Remember that changing the MembersWithData property affects the number of children associated with a non-leaf member which can have implications for calculations across levels in the hierarchy. Thoroughly test any calculated members before and after changing this property, but also keep in mind calculated members can be re-deployed to your cube without re-processing.

Data Member, Part 1

2007-07-25T19:53:00.001-07:00

By Bryan C. Smith

Take a moment to think about a typical organization. You have an employee. That employee has a supervisor who is also an employee who has a supervisor (who is also an employee) and so on and so on. (This is a classic parent-child hierarchy.)

If these employees are in a sales organization, sales quota may have been assigned. Those employees that are supervisors are responsible for the sales quotas of the employees below them but may also have sales quotas of their own.

Let's say you have an employee in a sales organization named Amy. She supervises three other employees, Jae, Rachel, and Ranjit. Amy is responsible for making sure these guys make their sales quotas of $13M, $3M, and $7M, respectively. Amy also has a personal sales target of $1M. We can breakdown Amy's sales quota like this:

Amy $24M Total
- Amy (personal) $1M
- Jae $13M
- Rachel $3M
- Ranjit $7M

The $24M figure represents the total sales for which Amy has responsibility while she is only directly responsible for $1M of that. The remaining $23M represents the sales quotas she manages through her supervisees.

Let's now look at how SSAS 2005 handles all this. Open an MDX query window in SQL Server Management Studio, connect to the Adventure Works DW database, and execute the following query:

select

[Measures].[Sales Amount Quota] on 0,

DESCENDANTS([Employee].[Employees].[Amy E. Alberts], 1, SELF_AND_BEFORE) on 1

from [Adventure Works];

	Sales Amount Quota
Amy E. Alberts	$24,202,000.00
Jae B. Pak	$12,547,100.00
Rachel B. Valdez	$3,269,800.00
Ranjit R. Varkey Chudukatil	$7,260,700.00

Through the DESCENDANTS
function, you asked SSAS to return the Sales Amount Quota for those employees one level below Amy E. Alberts and Amy herself. If you total the values for Jae, Rachel, and Ranjit, we come up with $23,077,600.00. That's $1,124,400.00 short of the total.

From these results, you can deduce the approximately $1M deficit represents Amy's personal quota. But how do you get SSAS to explicitly present this? You do it with the DataMember function:

with
member [Measures].[Personal Quota] as

([Employee].[Employees].DataMember, [Measures].[Sales Amount Quota]),

format="Currency"

select

{[Measures].[Sales Amount Quota],[Measures].[Personal Quota]} on 0,

DESCENDANTS([Employee].[Employees].[Amy E. Alberts],1,SELF_AND_BEFORE) on 1

from [Adventure Works];

	Sales Amount Quota	Personal Quota
Amy E. Alberts	$24,202,000.00	$1,124,400.00
Jae B. Pak	$12,547,100.00	$12,547,100.00
Rachel B. Valdez	$3,269,800.00	$3,269,800.00
Ranjit R. Varkey Chudukatil	$7,260,700.00	$7,260,700.00

Your results now show definitively that Amy owns a $1M personal sales quota which contributes to the $24M overall quota she is responsible for delivering. You also see that Jae, Rachel, and Ranjit, Amy's supervisees, are solely responsible for their own quotas. You cannot say Jae, Rachel, and Ranjit have no supervisees of their own (without submitting additional queries), but you can say none of these three guys is managing quota values from any supervisees they may have.

So, when you request a value for a member in a parent-child hierarchy, such as the Employees hierarchy of the Employee dimension, you need to be aware SSAS combines those values directly tied to that member with values associated with that member's children. You need to use the DataMember function to get at the value directly tied to a member.

It needs to be pointed out that you can tell SSAS to display a member's directly attributable data a little differently when that member also has children with values. In my next blog entry, we'll revisit Amy's sales team and take a look at those settings and how they impact these results.

IgnoreUnrelatedDimensions

2007-07-25T19:34:00.001-07:00

By Bryan C. Smith

Imagine you have a highly simplified Adventure Works cube. In this cube, you have two measure groups, Reseller Sales and Internet Sales. Reseller Sales has relationships with the Geography and Date dimensions and has one measure, Reseller Sales Amount. Internet Sales has a relationship with just the Date dimension and a single measure, Internet Sales Amount. To simplify things even further, imagine the Geography dimension has a single attribute hierarchy, Country, and Date has three attribute hierarchies, Calendar Year, Calendar Quarter, and Date.

Every reference to data within this cube is based on a coordinate system. Each attribute hierarchy in the cube represents an axis in the coordinate. For our purposes, let's say the coordinates are in the format of ([Geography].[Country], [Date].[Calendar Year], [Date].[Calendar Quarter], [Date].[Date]). (For the sake of brevity, I've removed the attribute hierarchy from the Measures dimension.) Our MDX SELECT statements then assemble coordinates which SSAS resolve to return a cellset:

select

[Measures].[Reseller Sales Amount] on 0,

Geography.Country.Members
on 1

from [Adventure Works]

where ([Date].[Calendar Year].[CY 2003])

;

	Reseller Sales Amount		Coordinates
All Geographies	$32,202,669.43	?	('All Geographies', 'CY 2003', 'All Times', 'All Times')
Australia	$847,430.96	?	('Australia', 'CY 2003', 'All Times', 'All Times')
Canada	$5,651,305.43	?	('Canada', 'CY 2003', 'All Times', 'All Times')
France	$2,373,804.04	?	('France', 'CY 2003', 'All Times', 'All Times')
Germany	$1,098,866.68	?	('Germany', 'CY 2003', 'All Times', 'All Times')
United Kingdom	$2,160,145.83	?	('United Kingdom', 'CY 2003', 'All Times', 'All Times')
United States	$20,071,116.48	?	('United States', 'CY 2003', 'All Times', 'All Times')

As was mentioned at the top of this entry, the Internet Sales Amount measure group does not have a relationship with the Geography dimension. Still, the cube's coordinate structure, ([Geography].[Country], [Date].[Calendar Year], [Date].[Calendar Quarter], [Date].[Date]), must be employed. With the measure group's IgnoreUnrelatedDimensions property set to True, which is the default setting for this property, the All member is substituted in place of any specified members from unrelated dimensions:

select

[Measures].[Internet Sales Amount] on 0,

Geography.Country.Members
on 1

from [Adventure Works]

where ([Date].[Calendar Year].[CY 2003])

;

	Internet Sales Amount		Coordinates
All Geographies	$9,791,060.30	?	('All Geographies', 'CY 2003', , 'All Times', 'All Times')
Australia	$9,791,060.30	?	('All Geographies', 'CY 2003', , 'All Times', 'All Times')
Canada	$9,791,060.30	?	('All Geographies', 'CY 2003', , 'All Times', 'All Times')
France	$9,791,060.30	?	('All Geographies', 'CY 2003', , 'All Times', 'All Times')
Germany	$9,791,060.30	?	('All Geographies', 'CY 2003', , 'All Times', 'All Times')
United Kingdom	$9,791,060.30	?	('All Geographies', 'CY 2003', , 'All Times', 'All Times')
United States	$9,791,060.30	?	('All Geographies', 'CY 2003', , 'All Times', 'All Times')

When the IgnoreUnrelatedDimensions property is set to False, SSAS does not perform any substitutions:

select

[Measures].[Internet Sales Amount] on 0,

Geography.Country.Members
on 1

from [Adventure Works]

where ([Date].[Calendar Year].[CY 2003])

;

	Internet Sales Amount		Coordinates
All Geographies	$9,791,060.30	?	('All Geographies', 'CY 2003', , 'All Times', 'All Times')
Australia	(null)	?	('Australia', 'CY 2003', , 'All Times', 'All Times')
Canada	(null)	?	('Canada', 'CY 2003', , 'All Times', 'All Times')
France	(null)	?	('France', 'CY 2003', , 'All Times', 'All Times')
Germany	(null)	?	('Germany', 'CY 2003', , 'All Times', 'All Times')
United Kingdom	(null)	?	('United Kingdom', 'CY 2003', , 'All Times', 'All Times')
United States	(null)	?	('United States', 'CY 2003', , 'All Times', 'All Times')

So, what about the situation where a measure group is only partially related to the attribute hierarchies within a dimension? Let's expand this simple cube to include a Sales Targets measure group. The Sales Targets measure group has a single measure, Sales Amount Quota, and has a relationship to just the Date dimension. Unlike the other measure groups which are related to the Date dimension at the Date attribute level, the Sales Targets measure group is related at the Calendar Quarter attribute level.

With IgnoreUnrelatedDimensions set to False on the Sales Targets measure group, we see the following behavior. Note, the number of rows has been limited with the HEAD function for display purposes only:

select

[Measures].[Sales Amount Quota] on 0,

HEAD([Date].[Date].Members,10) on 1

from [Adventure Works]

where ([Date].[Calendar Year].[CY 2003])

;

	Sales Amount Quota
All Periods	$38,782,000.00
January 1, 2003	(null)
January 2, 2003	(null)
January 3, 2003	(null)
January 4, 2003	(null)
January 5, 2003	(null)
January 6, 2003	(null)
January 7, 2003	(null)
January 8, 2003	(null)
January 9, 2003	(null)

Executing the same query with IngoreUnrelatedDimensions set to True, returns the following:

	Sales Amount Quota
All Periods	$38,782,000.00
January 1, 2003	$38,782,000.00
January 2, 2003	$38,782,000.00
January 3, 2003	$38,782,000.00
January 4, 2003	$38,782,000.00
January 5, 2003	$38,782,000.00
January 6, 2003	$38,782,000.00
January 7, 2003	$38,782,000.00
January 8, 2003	$38,782,000.00
January 9, 2003	$38,782,000.00

Retrieving data at the Calendar Quarter level at which the relationship exists returns the following:

select

[Measures].[Sales Amount Quota] on 0,

HEAD([Date].[Calendar Quarter].Members,10) on 1

from [Adventure Works]

where ([Date].[Calendar Year].[CY 2003])

;

	Sales Amount Quota
All Periods	$38,782,000.00
Q1 CY 2003	$5,913,000.00
Q2 CY 2003	$8,039,000.00
Q3 CY 2003	$13,733,000.00
Q4 CY 2003	$11,097,000.00

What we can take from this is that when IgnoreUnrelatedDimensions is set to True, axis members associated with specific attribute hierarchies with which the measure group does not have a relationship are converted to the All member.

Greetings!

2007-07-23T16:54:00.001-07:00

We have had a blog at SQL Junkies for a few years, but have not posted for a long time. With Word 2007's new post-to-blog feature, we decided to move to a new blog space. Blogger is pretty simple, so we're moving here.

We've accumulated a number of topics we want to blog about, and hopefully this will make it easier for us to post more actively. If we can figure out an easy way to do it, we may re-post some of our favorite entries from the old SQL Junkies blog.

Watch for more soon!

The Hitachi Consulting Microsoft BI Blog Team

The 'Currency' number format style is Evil [Reed]

2007-07-04T05:18:00.000-07:00

I always used to use Currency formatting for currency values, but it's totally wrong in a database environment. Currency displays using the current locale's currency setting. But the values in your database have a pre-known, specific currency. If the values in your database are in Euros, then you don't want somebody from California to see them showing up as dollars. The origin of the Currency style was to simplify life for people using tools like Excel to input numbers. But when used with a database, they can cause rediculous restatements. Once I thought about it, Currency format is so rediculous that I can't believe I used to use it all the time. Hence the blog entry.

Even when doing currency conversions--you want to be in control of which currency format the values take. You NEVER want to be at the mercy of the current user's control panel whims.

Cheers. Happy 4th of July to all us Americans. [Reed]

More info on RS and AS [Reed]

2006-11-06T06:53:00.000-08:00

A couple more ways the RS MDX Builder is smarter than I thought. I always wondered about some of the complexity.

- When you use a parameter, there’s no way around using StrToSet to turn the parameter into an MDX set. That leaves an opening for inserting malicious code. The auto-generated MDX adds the ,CONSTRAINED flag to StrToSet, which says that the only acceptable contents of the string are literal MDX names, so it won’t run any MDX functions (such as .Children), and it won’t run any inserted code.
- In AS2005, anytime you use a subcube subquery to filter down to a single date, there’s a problem with date-relative calculations because the “current member” in the report is still the “All” member of the date hierarchy. This is a serious problem right now with Excel 2007 PivotTables and MDX. The RS MDX builder adds a funny-looking WHERE clause that basically says, if you single-selected a date for the parameter, make it the current member so that date-relative calculations will work properly.”

Reed

Combining Dimensions [Reed]

2006-10-13T07:47:00.000-07:00

The way AS2005 manages attributes in a dimension changes the design dynamic a bit: Which attributes should be combined into a single dimension? I've run into this issue from two sides.

On the one hand, we had a Project dimension with a lot of changing attributes. For example, the Project Manager would change frequently. To avoid dealing with creating lots of Type II instances of the Project, we decided to pull the Project Manager off as a separate key in the fact, making it a "Rapidly Changing Dimension". In 2000, this was often a very effective strategy. It's simple and easy to implement, but in 2005, we found that query performance was very bad. Even thougy the PM to Project relationship is very sparse, each crosstab query had to do a cartesian product and find the empty intersections. When we moved the PM back into the Project dimension, AutoExist suddenly made it really fast to find the projects for a given manager, or the managers for a given project.

Conversely, I had a client with lots of customers and lots of products, but where the relationship between customers and products was very stable and very sparse. It seemed bizarre, but we explored putting both customers and products into a single dimension, with the root key as the Customer/SKU combination. Again, the query performance gain was substantial, especially when looking for which customers buy which products, etc. In 2000, I would never have considered putting Customer and Product into a single dimension, but given the high cardinality, stable relationship, and inherent sparsity, it seems to be a really good idea in 2005.

- Reed

ByAccount aggregation bug workaround [Joe Kasprzak]

2006-10-13T06:45:00.000-07:00

When working with Account type dimensions and semi-additive Measures across a variety of account types with different aggregation functions, you can use the ByAccount aggregation function to set the proper aggregation behavior.

There is a bug that sometimes occurs to the finance measure where the ByAccount option doesn’t properly set various aggregation rules for each account type. This can be manually fixed by the following process:

* Right click the solution in the Solution Explorer window and click “Edit Database” to display the current settings for the finance measures by account type.

The ByAccount aggregation function should have set for each account type to either sum for additive or lastnonempty for accounts which are semi-additive.

* Click the Aggregation Function in the Account Type Mapping box and change the aggregation function from Sum to Lastnonempty for the Flow, Liability, Balance, Asset and Statistical account types.

- Joe

Don't declare false Attribute Relationships [Reed]

2006-09-01T05:03:00.000-07:00

It's really important to declare attribute relationships when they do exists. But what happens if you say A implies B when it isn't true. I somehow assumed that when you process the dimension, you'd get an error, but not so.

In the Adventure Works Date dimension, I created an attribute relationship from Day of Month to Day of Week. This is, of course, rediculous. Just because it's the 14th, that doesn't mean it's Wednesday. Just for fun, I then created a User Hierarchy of the same, with Day of Week drilling down to Day of Month. I got the nice, satisfying green triangle on the hierarchy. Then I deployed it.

As I said, I expected some kind of error or warning, but the days of the month just got a random weekday assigned. (I assume it has to do with the order the records were read from the dimension table.) So Sunday got associated with 12, 15, 18, and Monday with 1, 4, 21, 24, etc. This is actually the same behavior that AS2K had--if you created Day of Week as a member property of Day of Month, it would just pick one. I just thought 2005 would complain.

So, the moral is that you really really want to create attribute relationships when they are meaninful, but be careful not to create ones that are not, and don't trust silence as an indication of success.

-- Reed Jacobson

New Style and Old Style Calculated Member commands [Reed]

2006-08-31T09:04:00.000-07:00

Chris Webb posted an interesting observation about a new style for calculated members in scripts, where instead of

CREATE MEMBER CURRENTCUBE.[MEASURES].[Old Style] AS 1, VISIBLE = 1

you can just say

CREATE [New Style] = 1;

He points out a "bug" with the old style syntax. I added some comments relating to some experiments he inspired me to do. My conclustion is that it's too predictable a behavior to be a real bug, but if MS wants to call it a bug, that's OK; and in any event, it might be worth being aware of, especially if you mix script assignments with calculated members. Here's the link to his July 2006 blog page:

http://cwebbbi.spaces.live.com/?partqs=amonth%3d7%26ayear%3d2006&beid=cns!7B84B0F2C239489A!823&d=1&wa=wsignin1.0

-- Reed Jacobson

Dynamic Dimension Security in Analysis Services 2005 [Reed]

2006-08-29T06:42:00.000-07:00

Part I – The Measure Group Setup

The idea of dynamic dimension security is that with a single security role, each user gets a different list of members, based on an MDX expression that returns the permitted set. For example, User A may be allowed read access to different products than User B.

The MDX Username function returns the current user’s login information. If there is a many-to-many relationship between the User and the permitted products, you need a security map table, which is actually a “fact”, or bridging, table that contains one foreign key for the user and another for the product.

The SQL Server 2005 Adventure Works sample cube does not include a specific security map table, but it is possible to use the Internet Sales measure group as a surrogate: If a user (aka customer) purchased a product over the internet ([Internet Sales Amount]), then that user has the right to see the total sales for that product ([Sales Amount]). The structure is the same as if we used a regular security map table.

With a real security map table, you would create a measure group, and use a simple count of the rows as the measure, naming it something cool like [Security OK]. In this example, [Internet Sales Amount] is the surrogate for the [Security OK] measure.

Part II – The Username comparison

The format returned by the Username function is DOMAIN\User. In an ideal world, your dimension table would have an attribute in the exact same format. In the real world, you often have something a little different. For example, the name in the dimension may not include the domain. The closest thing to a user name in the Adventure Works Customer dimension is the Email Address attribute. Its format is User@adventureworks.com. This is not a bad approximation for a real-world user name attribute.

In the MDX security expression you compare the result of the Username function with the value of the [Email Address] attribute. Here’s an expression that will convert the Username to the right format for the Adventure Works [Email Address]:

Mid(Username,InStr(1,TestName,"\")+1) + "@adventure-works.com"

This expression will work even if users can be on multiple domains. If you know that all users will be on the same domain, you can make the expression evaluate much faster by replacing the (slow) InStr function with a hard-coded length. Assuming a constant domain name of DOMAIN, this formula is the simplified version of the previous one:

Mid(Username,7+1) + "@adventure-works.com"

For testing purposes (in case your current domain user name doesn’t happen to be in the Adventure Works Customer dimension), you can create a calculated member as a surrogate for the Username function:

Member Measures.TestName as "DOMAIN\anne14"

Then, during early testing, you simply replace the Username function with the TestName calculated member. This makes it easy to try out different “users”. Then, during final testing, switch it back to use the Username function.

Part III: The MDX Set Function

The general concept of the set function is to find all the products where the intersection of the product, the current user, and the [Security OK] measure is not empty. (I’m going to show three methods for doing the set. If you just want the “best” answer, skip to the third one.)

** Double-Filter Method (2000 and 2005)

Here’s the way I usually did this in 2000. You use an “inner filter”, to find the User whose email alias matches the user logon (there should be only one), and use Item(0).Item(0) to turn it into a member. Then you combine that member with the test measure, and check for whether that tuple is empty in an outer filter against the list of products. This technique does work in 2005, but it’s a bit slow. On my test box it was about 1 minute.

Filter( [Product].[Model Name].[Model Name].Members, not IsEmpty(
            ( Filter ( [Customer].[Email Address].[Email Address].Members
            , [Customer].[Email Address].CurrentMember.Name = TestNameX
            ).Item(0).Item(0) , [Measures].[Internet Sales Amount] ) ) )

Note: To test the set, you can run a simple query:

With

Member Measures.TestName as …

Member Measures.TestNameX as …

Set SecurityTest as …

Select [Measures].[ Sales Amount] on 0,

SecurityTest on 1

From [Adventure Works];

** NonEmpty Method (2005 only)

One way to simplify this is to use the NonEmpty function that is new in 2005. This basically puts the outer Filter and the IsEmpty test into a single function, but the logic is basically the same: Filter the list of products by testing it against the (single member) set of email members that match the current user. Note that you don’t need the .Item(0).Item(0) for this one, because NonEmpty is comparing two sets, so the result of the Filter function is fine as is. This is much faster—on my box, it’s about 3 seconds. Quite an improvement over 60 seconds.

NonEmpty ( [Product].[Model Name].[Model Name].Members
, ( Filter ( [Customer].[Email Address].[Email Address].Members
, [Customer].[Email Address].CurrentMember.Name = TestNameX

) , [Measures].[Internet Sales Amount] ) )

** NonEmptyCrossJoin Method (2000 and 2005)

Another way to do it—and this theoretically works in 2000 as well as 2005, but I don’t have a 2000 instance handy to test it on—is to use the NonEmptyCrossJoin function. Mosha says he hates the NECJ function (which is OK for him to say because he wrote it), and to use NonEmpty instead. But the NonEmpty function only takes two sets, and the NECJ function can take an indeterminate number. I have always had a great fondness for NECJ. The real goal is to find all the members of the Product dimension that actually exist in the Security measure group, when intersected with the specified member. This approach took < 1 second on my box, and I still like NECJ. Maybe there’s a better way in 2005 that can avoid NECJ, but I like this one. Plus, as an added bonus, it also exploits StrToMember which is another “no no” that I love to use.

NonEmptyCrossjoin(
            [Product].[Model Name].[Model Name].Members
            , StrToMember("[Customer].[Email Address].[Email Address].[" + TestNameX + "]")
            , [Measures].[Internet Sales Amount] , 1 )

-- Reed Jacobson

Designing legacy DTS packages from within SS2005 Management Studio [Michael B]

2006-08-16T09:47:00.000-07:00

Problem solved. Thanks to all who wrote with suggestions.

Here is the full story. Sorry if it is a bit much, but there were several false starts.

Chapter 1:

I had been running SS2000 and had a bunch of DTS packages on my laptop. I installed SS2005 Developer’s Edition on the machine, which wiped out the SS2000 Enterprise Manager.

When I tried to open any of the legacy DTS packages from within Management Studio, I got an error message box opened telling me that I needed to install SQL Server 2000 DTS Designer Components. Please note that I was not attempting to migrate the DTS packages to SSIS; I was merely trying to open the packages in the equivalence of the package designer used in the older Enterprise Manager.

I found the appropriate page on Microsoft’s site discussing the problem (http://support.microsoft.com/kb/905417) and the site to download the “fix” (http://www.microsoft.com/downloads/details.aspx?familyid=DF0BA5AA-B4BD-4705-AA0A-B477BA72A9CB&displaylang=en). I downloaded the recommended file (SQLServer2005_DTS.msi), installed it, and rebooted.

It was no help; symptoms were unchanged.

Chapter 2:

One of our colleagues suggested that I uninstall and reinstall SS2005. I was getting close to do this and was in the Add/Remove Programs utility when I noticed SS2005 Backward Compatibility Components item in the currently installed programs list for removal or repair. I ran the repair.

That didn’t fix it, but it changed the symptoms somewhat. Now when I tried to open a DTS package within SS2005, I got 3 separate error message boxes in succession (I believe the third was the same as the single error message I was originally getting (and oddly, sometimes I got just two messages -- the first and third. Also the package tried to open but only displayed some of the icons in the design window, usually just the connecting arrows.)

Chapter 3:

Two colleagues had sent a link to another Microsoft article (http://support.microsoft.com/?kbid=917406), (and one of those also suggested I install the Backward Compatibility Components which I had already done, as mentioned above) and now, with the 3 error message boxes, my symptoms matched what was discussed on the Microsoft page.

The support page gave two workaround methods. The second one was in effect to reinstall the client components from SS2000. The first method promised to allow the correct opening of DTS packages from within Management Studio, which was my preferred route.

It said to remove the following lines of code from the Sqlwb.exe.manifest file in the \Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE folder.

The way the lines actually display on the Microsoft page, line 3 (red line numbering added by me for reference) is one long line in a small, scrollable text box. In this message, it wraps.

1.
2.
3.
4.
5.

When I opened the file for editing I saw something like the following (once again, I’ve added line numbers) with lines 2 and 3 continuing on the right beyond the window width:

1.
2.
3. 4.

I searched for and found the opening tag and its closing tag mate and deleted those and everything in between as it seemed to instruct on the Microsoft page.

What I didn’t notice was that the line went way off to the right and that there were 3 assemblyIdentity tag groups on that line. Without realizing it, I had deleted all of them. (Of course, I had made a copy of the file before altering it.)

After deleting the lines, SS2005 Management Studio wouldn’t open at all giving a memory location error. When I restored the lines to the file, Management Studio would open again, but I once again, I still couldn’t properly open DTS packages.

After several additional attempts to edit the file, I finally noticed that there were 3 sets of assemblyIdentity tag groups, only one of which looked exactly like line 3 in the Microsoft text box. I reformatted the lines of the file so that I could more clearly see the logical tag groups. It now appeared in the Wordpad window something like the following (again, minus the line numbering and the bold font on line 7. And again, lines 5, 6, & 7 scroll off to the right, beyond the width of the editing window.) (BWT, I just noticed while writing this email, that lines 5 & 6 are identical, but that’s how they were in the original file.)

1.
2.
3.
4.
5. 6. 7. 8.
9.
10.

What I realized I needed to do was to delete only line 7, keeping lines 3 thru 6, and lines 8 thru 10. This indeed did the trick. I can now edit DTS legacy packages from within SS2005 Management Studio.

Conclusion:

1. Install or repair SS2005 Backward Compatibility Components using the Windows Add or Remove Programs utility in Control Panel.

2. Download and install SQL Server 2000 DTS Designer Components which is the SQLServer2005_DTS.msi file from http://www.microsoft.com/downloads/details.aspx?familyid=DF0BA5AA-B4BD-4705-AA0A-B477BA72A9CB&displaylang=en

3.Carefully edit \Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\ Sqlwb.exe.manifest file removing only the tag group that looks like line 7 immediately above.

I hope this is clear and is helpful.

Michael Bergelson
Architect
Hitachi Consulting

BTW, I also noticed that under SS2005 Management Studio, there is no option to view the DTS package run logs on a right click, nor have I found that function anywhere else as yet.

RS Aggregate and Manual MDX

2006-08-07T08:37:00.000-07:00

In a recent post, I said RS Report Designer is smarter than I thought. And it is. But it is also really, really weird when you try customizing your MDX. (Not that I ever do that.)

This might be a bug, but it seems to happen only when you customize the MDX. Here's a scenario:

1) Use the wizard to create a table report with Country, State-Region, and City levels. Use Sales Amount (additive) and Average Unit Price (non-additve) as measures, and include subtotals.
[At this stage, the automatic MDX includes just the City level]

2) Change the Sum function for Average Unit Price (and Sales Amount too, if you feel like it), to Aggregate. [At this stage, the MDX changes to include the Country and State-Region attributes.]

3) Delete the Country group from the report. [The auto-MDX changes to remove the Country attribute, and the report still works fine.]

BUT. If between step 2 and step 3, you go make an intsy-weentsy change to the MDX, then the auto-MDX does not remove the Country attribute. IN THIS CASE, none of the lower-level Aggregate functions work. They all return Null. If you have unused higher-level attributes in your MDX, lower-level Aggregate functions return Null.

The really wacko part is that if you never touch the MDX, and just let the Report Designer fiddle with it, everything seems to work OK. But if you do use the Aggregate function and you do customize the MDX statement, make sure that you have a grouping in the report for each attribute that has higher-level totals.

Incidentally, the place I ran into this big time was when I was decided to add an All level to a hierarchy in the cube. When I had the All level there, the report showed all blanks. When I removed the All level in the cube, the report worked. It was really strange. Why would a change like that in the cube definition make the report switch between showing correct values and showing nulls? Well, naturally, I was writing my own MDX, and I asked for [Hierarchy].Members, rather than explicitly cross-joining specific attributes. So having the All level in the hierarchy returned an extra level in the result set--a level that wasn't "used" in a grouping in the report, so all the Aggregate functions below that level returned Null. Removing the All level from the hierarchy made it so that all the levels in the result set were actually used in the report.

- Reed Jacobson

Happiness: RS Report Designer smarter than I thought

2006-08-04T07:19:00.000-07:00

Ever since the earliest Yukon beta's I've been frustrated with how RS report wizard handles AS as a data source. The problem has to do with non-additive measures--for example, a distinct count. In the cube, you create a measure or calculated member that gets the right answer at every point in the cube. But when you use the RS wizard to create the MDX and the report, it only queries for the lowest level used in the report, and then uses Sum for all the higher levels. This is fine when it is a Sum, but doesn't work at all for Distinct Count.

Brian Welcker kept telling me that they added the Aggregate function and fixed the problem, but every time I tried, I still saw the same leaf-level MDX and Sum function. And I know that you can't get the right answer with only leaf-level MDX. So I would change the MDX to return the multi-level values, and then change the report to use the Aggregate function instead of Sum, and it did work. But it really didn't make a good story.

Well, earlier this week, I discovered the secret. I had always fixed the MDX first, and then changed the Sum function to Aggregate. That was obvious to me, because how would Aggregate get the higher level value if the MDX is wrong? Well, I "accidentally" changed the Sum function to Aggregate first, and when I previewed the report, it showed the right answer. How could it do that? Switching to the data pane, the MDX was still the old detail level. So I used Profiler to capture the MDX, and the MDX was different--it included the additional levels.

The bottom line is that if you use the MDX designer to create the MDX, and then modify the report to use the Aggregate function instead of the Sum function (assuming you have non-additive measures), then the report designer changes the MDX to make it work. When I think about it, this really does make sense. If all you have is Sum formulas, it's more efficient to just add them up in RS. RS can't really know whether a measure is additive or not, so it does default to use the Sum function. But all you have to do as a user is build the report (with the wizard) and then go change the function for any non-additive measures to Aggregate. The report then adjusts the MDX to make it work.

It's a bit creepy for me to have it do all that "automatically" for me. (The windows on my car open with crank handles--or zippers--and don't even talk to me about automatic door locks.) But it actually does make sense and it does work. Maybe it's even documented in the new July BOL ;-).

- Reed Jacobson

Top 10 Plus Other Query

2006-08-03T08:44:00.000-07:00

On Teo Lachev's forum at Prologika, there was a question from gudjonv about how to do a RS chart that showed the top 10 members plus an "all other" member (thus, 11 slices of the pie). [See http://prologika.com/CS/forums/1465/ShowThread.aspx]. I didn't feel like registering so I could post a response, so I'll put the answer (converted to Adventure Works) here.

This is the general method I've used to do "Top 10 Plus Other" since OLAP 7.0. I can't think of any compelling functionality in 2005 that would do the job better. (Of course, in 2005, the Aggregate function will work even with a distinct count measure, so that makes this solution more functional, but it doesn't change the solution.)

WITH
SET [TCat] AS TopCount([Product].[Subcategory].[Subcategory],10,[Measures].[Sales Amount])
MEMBER [Product].[Subcategory].[Other] AS
Aggregate([Product].[Subcategory].[Subcategory] - TCat)
SELECT { [Measures].[Sales Amount] } ON COLUMNS,
TCat + [Other] ON ROWS
FROM [Adventure Works]
;

- Reed Jacobson

Sadness: Excel 2007, Subcube Subqueries, and Growth Formulas

2006-08-01T11:53:00.000-07:00

When I first saw a demo of Excel 2007 in the Beta 1 timeframe, I was told that the Slicers in Excel 2007 would still use the WHERE clause, not the new Subcube Subquery available in SQL2005. I was very disappointed (partly because in the beta timeframe for SQL 2005, the Where clause behaved so badly). But when I got my copy of Excel 2007 and looked at the actual MDX, it turns out that they do use the subcube subquery. At first I was happy, but then I discovered a strange consequence of using the subcube subquery (while, in a bit of irony, by RTM the relative benefit of a subcube subquery has decreased because they fixed the behavior of the Where clause).

Suppose you have a "Sales Growth" measure. In all likelihood, the MDX will include something like ([Measures].[Sales],[Time].[Calendar].CurrentMember.PrevMember). Now suppose you have a query with [Nov 2007] as the slicer. If the slicer goes into the WHERE clause, then the current member of Time.Calendar is [Nov 2007] and the prev member is [Oct 2007] and all is good. But if the slicer goes into a subcube subquery, then the Time.Calendar dimension gets collapsed to [All]-->[2007]-->[Q4]-->[Nov 2007], but the current member is [All], not [Nov 2007]. With the subquery, the value of [All] equals the value of [Nov 2007], so the basic measures look alright, but the prev member for [All] is Nothing, so the growth calculation screws up.

The only way I've been able to get the correct growth to show up is by explicitly putting the Time dimension onto the Row or Column axis, so there's a meaningful CurrentMember. It works, but it looks a bit stupid.

I was hoping that the Existing operator (that I recently discovered) would provide a workaround for the problem (because the formula could then use Existing Time.Calendar.Month.Members. But, sadly, the "current context" for the calculated member does not take into consideration the subcube subquery restriction, so you get the entire set of months, not just the "current" month. So this approach did not work.

If anyone has an idea for how to write a formula that properly calculates growth, even when the filtering is done by using a subcube subquery, I'd love to hear it.

- Reed Jacobson

New Existing operator

2006-07-28T10:25:00.000-07:00

I just found out about the Existing operator. You put it in front of a set and it filters the set by using all the current members in the current context. This simplifies using Attributes without User Hierarchies.

For example, suppose you need to do the equivalent of the LastChild aggregation function, but with the Standard Edition: Use the Existing keyword to find the last period. The issue is that without a multi-level time hierarchy, how do you know which is the "last" month in the current filtered space. So Existing Time.Month.Members is essentially equivalent to Descendants(Time.CurrentMember,Time.Month), except that it doesn't require a User Hierarchy. Thus Tail(Existing Time.Month.Members) give the last month in the current filter set. (Note that this doesn't give the Last non-empty member, just the Last Child.)

- Reed Jacobson

Visual Totals and Row Filters

2006-07-28T10:21:00.000-07:00

I was troubleshooting with a client about some slow queries using Excel (2003) as a client for AS2005.

We had a large (customer) dimension on rows, and measures on columns. We added a second customer attribute (region) to rows. The pivot table returned very fast. As soon as we filtered by a region member (one or more), the report slowed to a crawl. Putting the region into the slice (filter) area and then selecting one or more items was fine, but with region on rows, it was slow. The problem is that users want to see the region next to the customer name.

Turning off Visual Totals gave fast results as well, but that allowed unwanted customers to make it through the filter, and the grand total was not what we wanted. In effect, we wanted the meaning of Visual Totals (with Region included on rows), with the performance of Region in Slicer (or no Visual Totals).

The best workaround we could come up with was to create two instances of the Region attribute, so there's a regular Region attribute (which goes on rows) and a Filter Region attribute (which goes into slicer). Filtering by the one in the Slicer area allows the report to be fast, even with Visual Totals, but still lets the user see the regions in the body of the report. We had to create similar parallel attributes for any customer attribute the user might want to show on rows--while simultaneously using as a filter.

My speculation as to the reason for this is that if the region filter is in the Slicer, it's the core cube "pyramid" engine that does the operations, and it's fast. Whether Visual Totals is specified or not is irrelevant as long as the Region is in the Slicer, because no "total" is being re-calculated by MDX. If the Region filter is in rows, then the All member of the Region attribute (which is implicitly there for every customer) has to be recalculated as a Visual Total by MDX, and that's really slow. Any time the MDX has to calculate for each member of the Customer hierarchy (whether it eventually gets filtered away by Non Empty or not), the overall report is slow. If an MDX expression only has to calculate for the rows that end up in the final report, it's really fast. The moral for me is to keep the high-cardinality operations in the Pyramid engine and restrict the Calculation engine to relatively small scope.

- Reed Jacobson

MDX and NON EMPTY

2006-07-27T05:50:00.000-07:00

A few times over the past couple of months I have run into a situation where removing NON EMPTY from a slow MDX query radically improved the response time. The symptom was clear, but not predictable. Earlier this week, we were able to pin down at least one specific scenario that definitely triggered the NON EMPTY slowness.

We were using Excel (2003) as the client, and had a large set (customers) on rows. Excel's default NON EMPTY behavior was filtering away the uninvolved customers. Everything worked well until we added a second measure. The query went from 1 second to 10+ minutes (or so). The measures were both calculated members that did a direct reference to base measures, and they did have the NonEmptyBehavior property set to the base measure.

After some troubleshooting, we observed that the speed hit happened only when the measures were on the Rows axis. If we put the measures on the Columns axis (i.e., a different axis from the large set), it went back to being really fast. The reason adding a second measure slowed it down was because with one measure, Excel puts the measure into the Where clause, but with two, it defaults to put the "Data" on rows.

So the solution (aka workaround) is to simply add the multiple measures to the report before adding the large dimension. With two measures on the report, you can move the measures to columns and then add the large dimension. It's a bit of a pain, but the difference between 10 minutes and 1 second is worth it.

My speculation is that 2005 is trying to optimize NON EMPTY behavior so that it's similar to the speed benefits of NonEmptyCrossjoin. If it gets the optimization right (which happens when the measures are on a different axis), it's blazingly fast. If it gets the optimization wrong (which happens when the measures are on the same axis), then the calculated member is evaluated for each member of the large set and it's distressingly slow.

- Reed Jacobson

MDX Questions and Answers

2005-07-15T06:22:00.000-07:00

Q. When someone creates a subcube in an MDX statement, where does AS store the subcube internally. Is there something like the relational tempdb for AS?

A. The subcube subquery is a logical construct, so the data itself is not really "stored" anywhere. Of course, when you execute the query against the subcube, the values are stored in server memory, the same as any query. The definition of the subcube subquery in effect filters the data retrieved by the query. It is not at all like making an extract into a tempdb. One of the big differences between SQL and MDX is that SQL needs a rowset to group. MDX reports create a structure and then retrieve the value for each cell independently (more or less). [Reed Jacobson]

Q. When querying a subcube, is AS able to access and benefit from the underlying cube's aggregations?

A. Absolutely, because it is querying the underlying cube. [Reed Jacobson]

Is there a way to analyze the MDX query execution plan and optimize it like there is for relational queries. I think the answer is no, it is probably to hard to represent since so much depends on how aggregations happened to be created vs. pre-set indexes on the relational side

It's a totally different process than SQL. You can get clues for performance problems by using the system monitor or profiler, but the whole approach is backwards from SQL. [Reed Jacobson]

Q. One student has encountered difficulty in MDX writing a year-to-date comparison to the same previous year-to-date time period. For example, they know how to do a current year-to-date query that would take them from the beginning of the year through July 6th. However, is there an easy way to query for previous year information through previous year July 6th

A. It is a little tricky to write the formula. I typically don't like to use a flag for "the current day" because that doesn't let a user look at the number "as of last monday". But a combination of ParallelPeriod and PeriodsToDate is sufficient. Roughly, the formula is Sum(PeriodsToDate([Year],1,ParallelPeriod([Year],1,[Date].CurrentMember)). [I'm doing this from memory and don't swear by the syntax.]

My position is that MDX Queries are relatively simple (compared to SQL queries), but that MDX Formulas can be quite difficult. If anything, Yukon is likely to make formulas both more powerful and more difficult :-). [Reed Jacobson]

Drivers

2005-07-13T07:59:00.000-07:00

Someone in the class was told that drivers for other db's than SQL Server (i.e. Oracle, DB2) would not be supported at RTM. Someone in the class form MSFT said this is not true. Do you know the answer off-hand?

[Stacia Misner] It depends on the context, I think. For example, in Reporting Services, there are certain data sources supported out of the box – SQL Server, Oracle, ODBC, and Analysis Services. Report Builder though will only support SQL Server and Analysis Services. That leaves Integration Services – I remember thinking each build since Beta 2 had a different collection of adapters so I didn’t bother memorizing what – wanted to see it stabilize first. Traditionally, DTS supported Oracle natively – don’t recall if it tries to in SSIS. At the very least – you can use OLE DB or ODBC or write your own.

[Reed Jacobson] Oracle can be used as a data source from virtually anywhere. (I don't know anything about Report Builder, so if it will only access SQL Server, that's news to me--it would still be easy to create an AS UDM that models the ORACLE source--even as pure ROLAP--and then Report Builder should be able to get to it. The only place I know you can't use ORACLE is as the metadata databases for Reporting Services--those have to be stored in a SQL Server database.

AS Settings

2005-07-11T07:00:00.000-07:00

Are there any AS settings that affect whether cubes process query requests or cube processing using multiple processors. If not, how is AS in using multiple processors?

[Stacia Misner] The first place I’d look would be the server properties in SSMS. Right-click on the server name in the Object Explorer and take a look at the properties there.

[Reed Jacobson] For processing, it's an option in the Process dialog box (that you get when you use the explicit Process command, either from BIDS or from SSMS). You can choose whether to go parallel or sequential. I believe that if you choose parallel, it makes intelligent use of the number of available processors. Regarding queries, my guess would be the same as Stacias.

Questions and Answers

2005-06-27T02:35:00.000-07:00

As classes have completed, we've had more questions rolling in. We'll attempt over the next few posts to provide some answers from both Reed and Stacia to give a couple points of view.

Question: In the SSIS demos we associate a config file with our package for the one time we are running it. What is the mechanism for permanently associating the config with a package? I am guessing it is something in SQL Server Agent, but could not get my Agent Service to start.

[Stacia Misner] when you use DTEXECUI you can associate a config file by selecting it in a dialog box. You can then generate a command-line with arguments that uses DTEXEC that also references that config file. You basically copy/paste that string into a scheduler like SQL Server Agent. I think the final demo in Module 2 should step you through at least the DTEXECUI piece. So there is no such thing as permanently associating a config file with a package in my mind. It’s what you choose to specify at the time of execution – whether through BIDS, DTEXEC, or DTEXECUI. The beauty is being able to use the same package with different config files as needed.

[Reed Jacobson] What Stacia says about adding an argument to specify the config file is definitely true. But I am suspicious (but haven't tested) that if you put a config file in the same folder and enable configurations and don't include a run-time argument, then it will use the config file. This would be worth confirming. Also, In Beta 2 (when we wrote the course) DTEXECUI would crash on almost anything, so unless Stacia added a lot to the demo scripts, the demo may not go as far as it can now. In one class, we built the argument string (using DTEXECUI) and then opened a command window and ran DTEXEC, pasting in the argument string from DTEXCUI. It ran beautifully. The next step would be to put the DTEXEC + arguments into an Agent job. The only thing you have to watch out for then is what "user" is running the job and with what credentials.

New Questions Part 2

2005-06-24T07:03:00.000-07:00

1. On our training slide we say the OWC is not likely to be continued. Is this still the case? Or can people safely create OWC reports and not worry about the immediate future.

I wouldn't worry about the immediate future. It's still the best (& cheapest) for many embedded applications--and they use it in BI Dev Studio. You probably do want to figure out a way to implement Create Subcube to filter (to avoid strange behavior around Where clause), but that's not hard--just grab the connection from the OWC and execute a Create Subcube command. That's what the filter at the top of the OWC in BIDS does to con the OWC into working properly.

2. Why is OWC not a thin client. Do people need to have Excel on their machine to use it?

It's not thin because it needs a) an activeX control and b) PTS. That's about 4-5 MB of download. Users don't need Excel. If they don't have a 2003 licence, they can drill and slice, but not create new ones.

Here are some questions I was recently asked

2005-06-22T04:00:00.000-07:00

1. Can you describe an example of when a calculated cell would be used? What about an example having multiple passes?

In 2005, Scripts basically replace calculated cells (although calc cells are still supported). They are alternate mechanisms for accomplishing the same internal behavior. In 2000, one simple way in which I used calc cells was to have an IIF where value if true was a number and value if false was a string—I just made a calc cell for the subcube that needed the string and applied the string as the value. (In 2005, you can have different types for value if true than for value if false so this is no longer necessary, but you could definitely use a calc cell (or script command) to apply a special format (e.g., a background color) to a specific subcube (e.g., members of a heading level).

More typically scripts are intended for financial calculations—one example would be if you need a different formula for net income in the current year (where taxes are not known) than in the prior years where they are finalized. A script command could also apply a security layer, since scripts are applied before security. So, for example, you could use a script command to replace the actual salary at the leaf level with the average salary of the group (without affecting higher level totals), and end users would not be able to see the detail.

A classic example of multiple passes is when you need to calculate a tax amount based on net income—after tax. So you have a circularity that you need to iterate down to a negligible amount.

2. What does the flattened command in DMX do? Why does DM data need to be flattened?

I don’t know. I don’t write DMX, because the DTS Data Mining Query task can create it for you. It happens to put FLATTENED in, so I let it—in the same way that it puts in PREDICTION JOIN. I don’t have to understand DMX syntax in order to use Data Mining. And to me that’s a good thing. (But you have to put up with my soapbox that to use Data Mining effectively, you do have to understand at least enough statistics to know what would make a good source data set.)

3. What is the meaning of native vs. managed code? How is that relevant to SSIS work?

Managed code is .Net and uses the Common Language Runtime (CLR). It’s “managed” in the sense that code that uses the CLR exclusively can be marked as “safe”, which means it cannot damage other processes. Older code—VB6, C, C++, etc.—is compiled directly to the operating system machine code (at least indirectly), so it is “Native”. SSIS (and its libraries) was written in C++, which means the code generated is “native” or “not .Net”. In order to access the libraries from a .Net language, a “wrapper” is needed—and provided. In principle, “native” code is “faster” than “managed” code—because there’s a layer of overhead missing, and that’s the ostensible reason SSIS is written using native code. But Microsoft encourages managed code (partly for ease of writing and partly for security), and even if you write a custom transformation, you can do it using C# or VB.Net and it will be “almost” as fast as if you used native code.

My own opinion is that it’s easier to use the SSIS libraries if you keep in mind that they are not fundamentally .Net code, so you can set aside some expectations of how a typical .Net object model works when using the SSIS libraries. Most .Net developers I’ve talked with find the SSIS library functions confusing (more confusing, for example, than Analysis Services’ AMO libraries).

4. Why do you say that there is no SSIS server? What runs the packages?

When you write a SSIS custom transformation and are testing it in debug mode, the SSIS designer runs the package. You can debug an SSIS package without having the service running. But you can’t attach a DLL (your custom transformation) until the package is running. That’s why you need to put a breakpoint into the package and get it started before you can attach your DLL to the debug process. This is different from SSAS, where even during design time, you can’t browse data until you deploy (perhaps to a test server), and it’s the msmdsrv process that serves the data, so on the one hand, you can’t debug your SSAS User Defined Function DLL without starting the SSAS Service, but on the other hand, you can attach your DLL to the msmdsrv process without having to pre-set a breakpoint in the cube somewhere.

It was my understanding (in the Beta 2 timeframe) that the SSIS Server service did not need to be running in order to run the package (using DTEXEC or DTEXECUI), and that the service allowed you to a) kill running jobs if necessary and b) manage storage of packages either in MSDB or a “relative” file system. But back then, I had a hard time getting DTEXECUI to keep from crashing at all anyway. In the more recent CTP builds, where DTEXEC actually works, it appears that the service does need to be running in order to execute a package, but I’m not 100% sure of that.

5. In DM slides you discuss how statistics creates the model then looks for the statistics and in DM it is the reverse. Could you please provide a brief example so I can get a better sense of what this means.

In the statistics class I took as part of my MBA program, we had to build regression models. We had to first think of logical relationships, second create a mathematical model to represent the relationship, and finally test the model by running data through it. When we tried to shortcut the process and run regressions against the data in search of good correlation scores (and then made up a logical “reason” afterwards), the professor accused us of “data mining”. The problem with “data mining” was (and is) that you can find correlations in historical data that may not have predictive value. (As your mutual fund prospectus states: “Past performance is no indication of future results” or as the slide says, quoting my statistics professor “Correlation does not imply causation.”)

Data mining models today do exactly what we were told not to do back in the late Jurassic period when I was in B-school. They look for correlations in the data and create coefficients for the models based on that data. One big difference is that the models are themselves designed to look for specific types of correlation (note for example Association model for Market Basket analysis), but it is still important to test the model (perhaps by splitting the available historical data in half) and to think about the logical basis for the correlations.

6. In the old AS2K course we created ROLAP aggs and went out to the db to see them. This was great for helping people to understand what an aggregation is. I tried doing this in our AS Core demo in Yukon, but could not see the aggs. Do you know how to do this?

I couldn’t get ROLAP aggregation tables either, but I haven’t tried since Beta2. I agree that they were useful for instructional purposes, but not for production—so I would have mixed feelings if it really is not possible in RTM.

7. When discussing the idea of the UDM, a key characteristic mentioned is that it can decide whether of not to pull data from the cubes or the relational db. Does this decision process occur in any situations when proactive caching has not been enabled?

It really doesn’t have a lot to do with proactive caching. When you create a dimension or measure group, you can specify (in the “UDM”) whether storage should be ROLAP or MOLAP. That’s what makes the fundamental decision.

It is true that when you enable pro-active caching, it’s possible for even a query against a MOLAP measure group to get redirected to the relational source, but I can’t imagine that being desirable in very many situations. My fundamental belief about query speed is that consistency is even more important than speed. So if a query takes 2 seconds 95% of the time, but occasionally takes 20 minutes, that’s worse than taking 4 seconds 100% of the time. That’s one of the problems I have with (data driven) proactive caching on a measure group—if it increases the risk of fluctuation in query speed, I won’t want to use it (very often). If you can force it to keep using the old MOLAP cache until the new one gets completed, that would make it more acceptable to me. My informal surveys lead me to believe that it’s a relatively small percentage of applications that require the level of “real time” update that would require data driven proactive caching on measure groups.

8. Is the UDM really just a fancy\new term for a cube? Or is there something different between a cube and a UDM?

My understanding is that the goal in 2005 is to be able to put the entire data model into one logical cube, in which case cube = UDM. If, for some reason, you split the data into multiple cubes, I would think that the combination of cubes would = UDM. I have been trying to find a case where it is truly impossible to put an entire data model into a single cube. The only examples I’ve seen have been cases where it could have been put into a single cube. In the April CTP, Adventure Works (the SSAS model) has a separate cube for some data mining models. I don’t know whether that is permanent and/or necessary.

[This part is a total tangent from your question, but whatever…] The real situation where I think you would want to separate the data model into multiple cubes is one where a large enterprise wants to have regional servers with data specific to each region, and then a corporate server with high-level aggregations from all the regional servers. I don’t think this would be possible to model with a single cube or even with multiple cubes in a single UDM—because you don’t want all the data on a single server; you want it distributed. I think you would need to create two logical models ( = UDM = Cube), one for the region and one for the corporate, and create several physical instances of one (one for each region) and one instance for the corporate summary. But this would not allow for seamless browsing from a single client PivotTable. It might be interesting to think through whether this scenario would be possible with a single UDM, using multiple distributed partitions for the regions—but I haven’t gotten that far yet.

9. Is there a way for an attribute hierarchy to be excluded from the "real" cube. For some reason I thought I saw somewhere that there is, but I am not seeing how you could have an attribute hierarchy that is not in the cube.

I’m not quite sure what you mean. If you want to exclude a dimension from some measure groups while keeping it in other measure groups, you do that in Dimension Usage. If you want an attribute to be part of a dimension, but don’t want it to be a structural part (of any measure group), then you set AttributeHierarchyEnabled = False for that one attribute.

If a dimension is associated with a measure group, the granularity attribute and all attributes that are (directly or indirectly) member properties of the granularity attribute are associated with that measure group. You can make an attribute unavailable for physical storage (i.e, for inclusion in an aggregation), by setting the AggregationUsage property of the attribute (in the Cube Definition) to None—but that (unfortunately) applies to all instances of the attribute in the entire cube, not just a specific measure group (or better, a specific partition).

You could use Perspectives (or Security) to make an attribute hierarchy invisible, but that is logical, not physical, so it would still be part of the “real” cube. I think it’s better to think of dimensions

10. If there are 30 attribute hierarchies in a dimension, is that like having 30 dimensions in the old AS2K? Is there tremendous data explosion with something like that?

It’s more like having 30 levels in AS2K because (by default) in AS2K each level was included in the structure of the data partition and was available for inclusion in an aggregation. In AS2K, a cube with a lot of levels would require a lot of time for designing aggregations, even if the size of the final cube wasn’t that big, just because AS has to consider all the possible permutations when designing aggregations for a given amount of disk space. In 2005, it’s a lot easier to have tons of attributes than it was in 2K to have tons of levels, which is why so many attributes are left out of the right column of the aggregation design wizard—just to keep the number of permutations manageable. Data explosion is still limited by the aggregations you design (i.e., Max size of data limit in Aggregation Wizard). The place I can see a potential increase is in the size of a single data partition “row”. In AS2K, a data partition row included a key for each level in the cube. If the same is true in 2005, the row for a measure group data partition could be a lot bigger because there might be a lot more attribute hierarchies than there were levels in 2K. But there is no documentation (for either 2K or 2005) on the internal data structure, and I haven’t tried hacking the files to figure out the structure (yet) in 2005.

11. Why are min and max considered to be pseudo-additive measures?

My definition of pseudo-additive is that it has all the same benefits as Sum (i.e., can take advantage of strategic aggregations), but is not technically addition. Min and Max definitely can be included in strategic aggregation tables, but they are not technically addition. I just used to loosely talk about “additive measures” as including anything that could have strategic aggregations, but too many people would get confused about referring to Min, Max, weighted average, etc, as “additive” so I made up “pseudo-additive” as a way to say the same thing without being technically incorrect.

12. Can you please explain the purpose of the xml.defaults statement in the AMO code set. What is the outcome of setting this to true vs. false.

Do you mean the CaptureXML property? Setting that to True means that an Update statement generates the XML (as when creating an AS project in Visual Studio), while setting it to False means that an Update statement makes the change on the deployed database (as when attaching to a database from Visual Studio—which I think is generally a very bad idea.)

13. How would you explain why AS was completely re-written.

In one sense, the underlying conception is the same—the way strategic aggregations work. So to say it’s completely re-architected is a slight overstatement.

But to answer your question, AS2K dimensions were completely founded on levels in a hierarchy. That is a very rigid structure. One example I like is Calendar vs. Fiscal hierarchies. While the Year and Quarter levels may be different, often the Month and Day levels are identical between the two hierarchies. In AS2K, this was simply impossible to properly model. Another example is with non-orthogonal attributes—such as Size vs Color: both are attributes of a Product, but they don’t form any kind of natural hierarchy, so in AS2K, there was no way to recognize any relationships that might actually exists (for example, that size 44 exists only for Red and White, but not for Black or Gray).

In 2005, a dimension is founded on a leaf-level key with additional attributes of that key. Those attributes may or may not form hierarchical relationships. This is a very flexible structure. You can easily model both the Calendar/Fiscal and the Color/Size problems I mentioned earlier. With that flexibility comes some additional complexity, but based on the changes in Apr CTP vs Beta 2, many of those complexities are likely to be handled by default, rather than by manual work.

- Reed

SSIS vs Bulk Load

2005-05-10T02:23:00.000-07:00

Based on 2000 experience with DTS, a lot of people prefer set operations or bulk load over transformations. One big benefit of SSIS transformations is that you can execute several operations in one pass--which you can't do with set operations. And if you use the SQL Server Destination adapter (and native SQL connection manager), you get speeds much faster than OLEDB--in fact you can get virtually identical speed to bulk load. One thing to watch out for is that when using the SQL Server Destination, the package must run on same machine as destination database.

Reed

When is Ragged Right right for Fixed-length files?

2005-05-04T03:07:00.000-07:00

It's natural to expect that if you have a Fixed Length file, that you should choose the SSIS Fixed Length file type. But Fixed Length is really for binary files, where there are no CRLF characters at the end of the lines. If you have a fixed length text file, and want to eliminate the delimiter, use Ragged Right flat file, and for the final column, specify CRLF as the delimiter.

- Reed

Diagrams are Back!

2005-05-02T00:35:00.000-07:00

One of the most (ahem) active threads in the SQL 2005 beta newsgroups was discussion around the loss of the trusty database diagramming tool, formerly found in SQL Enterprise Manager. I am happy to report that as of the April Community Technical Preview build, diagrams are back in the SQL Management Studio. Even though Microsoft has stated that the primary emphasis for diagramming will be with both Whidbey and with Visio Enterprise Architect, it’s nice to have this often used functionality returned to the tool-set that many of us use day to day to create or understand Star or Snowflake Schemas. While there are many tools that offer more extensive diagramming feature sets, the integration and ubiquity of the Database Diagramming Tool makes it invaluable. Personally, I use it to visually understand the layout of Star and Snowflake schemas that I am becoming familiar with. I also use it to create prototype relational databases for proofs of concepts.

Feature-wise, it seems to be much the same as the previous version. I’ve noticed two differences worth mentioning (so far):

1) The procedure for adding related tables is slightly different. In the SQL 2000 version this was accomplished through the add-table dialogue; now it is accomplished by selecting a table in the diagram and clicking the “Add Related Tables toolbar icon”

2) The ability to open tables is no longer part of the diagramming tools. That functionality is available as part of SMS, just not directly from within the Diagramming Tool as in the SQL 2000 version.

Kudos to the SQL Server Development Team for incorporating our feedback around the Diagramming Tool.

- Tom Huguelet

DTS Service Benefit

2005-04-26T23:06:00.000-07:00

I've sometimes wondered what the real benefits of the DTS Service aulkre--aside from managing the server storage and monitoring jobs. I just found out that the DTS Service caches the enumeration of components. Turning on the service keeps the enumerations from having to happen each time you run the package. For some people this has made orders of magnitude difference in execution speed.

- Reed

Link to Unknown Member Blog

2005-04-06T21:12:00.000-07:00

Christian Wade from Conchango has an excellent blog entry on issues relative to the Unknown Member. He makes a compelling case for handling unknown members in the ETL whenever possible, and describes an excellent way of keeping clear which fact records need to be reprocessed.

http://blogs.conchango.com/christianwade/archive/2005/04/01/1228.aspx

- Reed

Hybrid MOLAP/ROLAP dimensions

2005-02-24T01:46:00.000-08:00

Some dimensions—if you push them to their physical extreme, something we never did in 2000—get very, very large at the bottom. As a simple example, you could have 50M customers. In 32-bit AS2K you would never dream of making that a dimension—or at least not a MOLAP dimension—and you wouldn’t want to make it into a ROLAP dimension either, because that would eliminate any MOLAP data storage anywhere in the cube. In 2005, I can easily imagine a situation where I would want the most aggregated attributes (Country, State, City) of a user hierarchy to be MOLAP—so that they can support good MOLAP aggregations—and the bottom attributes (such as Customer, or even Order ID) as ROLAP, so they don’t require MOLAP processing.

The developers imagined it as well, but apparently couldn’t get it to work in the Yukon time frame. So the whole dimension has to be either MOLAP or ROLAP. In the first place, my experience with large ROLAP dimensions used with a MOLAP cube is very limited (which is to say, not yet existent), so it may not be all that much of an issue.

If it turns out that a truly “hybrid” dimension is desirable, there are a couple of ways I can think of for creating the hybrid. One is to split the dimension in two, with the higher-level (MOLAP) one a reference dimension by way of the lower-level (ROLAP) one. Another is to create a many-to-many dimension to connect the two. It will be interesting to experiment with different alternatives in specific scenarios.

-- Reed

Drill Through and Fact Dimensions

2005-02-23T05:54:00.000-08:00

Fact Dimensions did not work in Beta 2. At the time I assumed that they were just “degenerate” dimensions, so that you could create dimensions directly from low-cardinality values stored directly in the fact table—such as whether a row was part of a promotion or something like that. I also assumed (without ever really looking at it closely) that Drill Through was basically the same as in AS 2000—a SQL Query retrieval of the rows that contribute to a specific cell.

In the Dec CTP, they fixed fact dimensions—and it turns out that they are completely different from what I had thought. A fact dimension is essentially using the fact table itself as a dimension—with multiple possible attributes—and it is the key to getting Drill Through to work.

Suppose that you had a PO number as a field in the fact table, but didn’t want to include that in any dimension. In 2000, you could retrieve the PO directly from the fact table via Drill Through and a SQL Query. In 2005, all Drill-Through requests go to the UDM (which actually seems internally consistent with the concept of the UDM). But that means that any column from the fact table that you want returned in the Drill Through result has to be part of a dimension. Hence the Fact dimension. First add a primary key to the fact table. (You can, in principle, use a combination of columns to get a unique key, but I think it’s good practice—and I’ve had better success—just adding an AutoNumber primary key column.) You then create a dimension using the fact table primary key as the key, and add any other columns from the fact table (such as the PO number) as attributes. Then make the dimension ROLAP storage. Once you add the dimension to the cube (and probably hide it from the users), you can include any of its attributes in the Drill Through Action definition. It actually worked quite well for us. Good luck.

--Reed

Proactive Caching

2005-02-22T03:24:00.000-08:00

Another question that came up at the Issaquah Ascend class was related to Proactive Caching. Specifically, where is it stored? In case you haven’t heard about it yet, this is one of the interesting new features of Analysis Services 2005 that really changes maintenance strategies from the way you currently keep cubes up-to-date in AS2000. It’s a huge step towards real-time OLAP without the traditional issues associated with ROLAP storage. With proactive caching, you have MOLAP storage just as you always did with AS2000, but now you also have relational storage. (So unlike AS2000, where in theory you can toss your source database and run solely off of the cube – assuming you never wanted to update it again – you must maintain a relationship to the relational source in 2005 in order for proactive caching to work).

As changes to the relational source occur, updates to the MOLAP cache also occur—eventually (based on how you configure triggering events and frequency). However, as with AS2000, you still need to provide a query for the partition that correctly identifies new rows in the relational source. In theory, you could have two versions of the MOLAP cache concurrently - the cache responding to queries and the cache that is being rebuilt to conform to the changes in the relational source. When the rebuild is complete, the obsolete cache goes away.

- Stacia

Gaining Perspective on Perspectives

2005-02-01T21:54:00.000-08:00

One of the questions that came up in the Ascend class held in (rainy) Issaquah in January: What happens if you have the same perspective name across multiple cubes in the same database? It seems logical that it should not be possible to create multiple perspectives with the same name inside the same cube, but would that also be true if the perspectives were in different cubes in the same database? We didn’t take the time to actually try this out in class, but I surmised that it wasn’t possible. Afterwards, I tested this out once in the BI Development Studio (BIDS). I also tested by running the Deployment Utility against the .asdatabase file after I modified the file manually to add two perspectives with the same name in different cubes. Whether using BIDS or the utility, I was politely informed that the perspective name that I tried to reuse was already in use and stopped from continuing.

This behavior seems reasonable and shouldn’t surprise anyone. But perhaps a more interesting question that stems from the original question: why would you want multiple cubes in the same database? In AS2000, it was not uncommon to have multiple cubes in the same database because there was a one-to-one relationship between a fact table and a cube. Now in AS2005, you can have one cube with multiple fact tables (using measure groups) and now the one-to-one relationship is between a fact table and a measure group. The vision is now to have a “one cube fits all” solution – something I would never have believed I would find myself saying, having reeducated a lot of clients about the perils of having too much in a single cube. To be honest, I haven’t pondered all of the ramifications of this new design approach, but as of yet can’t come up with an argument against having a single cube. I’m looking forward to following the certain debate to come regarding the pros and cons of One Cube.

So with everything in one cube, it can sure get confusing for an end user to navigate that cube. Enter perspectives to save the day. To my front-end tools of choice, the default perspective of the cube (which includes everything) appears side-by-side with any additional perspectives that were added to the cube. The idea of perspectives is to filter a cube to show only dimensions with measure groups that make sense together – whether oriented around subject matter or end user roles. It’s important to note that there is no security on perspectives - cube and dimension level security will be honored, but you can’t limit certain users/groups to certain perspectives. BOL explicitly states that perspectives are not intended to be part of the security architecture. By pointing this out, I provoked a stimulating conversation on the subject of security in the class. The bottom line is that the analogy between virtual cubes and perspectives falls apart when it comes to security. Be sure to consider the impact of this constraint when you plan the migration of your AS2000 cubes.

-- Stacia

Attributes queried separately

2005-01-27T05:12:00.000-08:00

You may notice that when AS2005 processes a dimension, it creates a separate query for each attribute. One reason for doing this is that it allow you to encorporate data from different data sources. It will, however, sometimes query for multiple attributes--particularly at the leaf level--and in a snowflaked dimension, that may result in SQL Join clauses.

What it is really doing is a creating single query for all columns "related" to one attribute. That would include the Key, the Name, and all Member Properties. In a generic (manually created) dimension, all attributes are defined as member properties of the key attribute. In the sample AdventureWork database Product dimension, you can see a join at the leaf level between Product and Subcategory. The Category member property, however is moved from the key attribute up to the SubCategory attribute, so you should see the join in the query for Subcategory.

The developers told me that one of the ways that IntelliCube (or whatever its name ends up being) will assign member properties is based on same-table of snowflake dimension (and, if I remember correctly, that portion of Intellicube already works in Beta 2). You can, of course, manually move the member properties as needed. I would think that if a member property causes a join in a properly snowflaked dimension, it would indicate that you should move the member property to a higher level--to the attribute that is the PK of the higher table.

-- Reed

Thin Client OLAP tool

2004-12-23T03:19:00.000-08:00

There's a new, free, thin client OLAP tool available for download from the microsoft.com site. It's developed by the Grape consortium in asia. I haven't had a chance to look at it very closely, and I don't think it works yet with Yukon, but still seems to warrant a look, particularly since it's now a Microsoft download. To find it, Google for "OLAP GrapeCity Microsoft" and look for the reference at microsoft.com.

- Reed

Using Scripts to Simulate High-level Write-Back

2004-12-20T03:35:00.000-08:00

Data write-back in Analysis Services has always been a bit frustrating because it's ultimately limited to leaf-level storage. (Not that leaf-level data write-back is not extremely useful--think of the millions of budgets that are input by using Excel spreadsheets--leaf-level data input without the benefits of an OLAP engine.) At any rate, Yukon write-back is still going to be ultimately leaf-level. One option for efficient high-level write-back is to "allocate" the values for each level to a single "dummy" member. But there's another option that we used occasionally in AS2K that would be even easier to implement in Yukon.

In AS2K, you can create Calculated Cell expressions that change the value of a single cell. So you can simulate data write-back by writing a calculated cell that assigns the target value to the specific single-cell subcube.

In Yukon, you get the same effect as a Calculated Cell by simply executing a script assignment. So, in a hypothetical cube with two non-measure attributes--account, and period, if the user enters the value 1000 for March Net Profit, you could "store" that value by using the simple assignment statement

([March], [Net Profit], [Amount]) = 1000;

Over the course of a session, your code collects these assignment statements, and at the "commit" portion, exports them to a process that parses, allocates and loads into a processable fact table.

-- Reed

Writing back descriptive data

2004-12-15T00:59:00.000-08:00

OLAP is fundamentally numeric. Through "official" data write-back, you can't write comment values to a cell. That doesn't mean your app can't do it on the side

Inside an app, you could capture the tuple of the cell that gets the comment, convert the tuple into a unique relational key, and store the comment with the tuple. You then do the reverse on query.

Emir pointed out that you could accomplish the same effect completely within the cube: create a "comments" dimension that begins essentially empty. Then use dimension write-back to create the unique key for a tuple and add it as a new dimension member, with the comment text as the "name" of the member.

You could then create an action on the cell that looks up the unique value--perhaps using a User Defined Function (UDF).

- Reed

CrossJoin vs SubCube

2004-11-04T22:49:00.000-08:00

In Yukon MDX, you can crossjoin two sets by simply enclosing them in parentheses. In a way, it's like using the CrossJoin function without using the word. In other words, you just list the sets as if they were parameters, but leave the function name off.

But that's not really what's happening. Parentheses form a subcube. A subcube is very similar to a crossjoin, but with slight differences. With the Crossjoin function, you can't have the same hierarchy appear more than once, but with a subcube (that is, with the parentheses alone), you can use a specific member of one or more hierarchies, and then use the Tree() function to fill in all the remaining attribute hierarchies.

Thus, the expression Crossjoin([Bikes],Tree([Product])) is invalid, because the Category attribute appears both in [Bikes] and in Tree([Product]), but the expression ([Bikes],Tree([Product])) is valid, because the subcube operator just "fills in" the unused hierarchies.

One interesting thing about the subcube notation is that it is identical to tuple notation, except that as a tuple, you use single-members instead of sets. The intriguing part about that is that a tuple really is the crossjoin of individual members, so the notational similarity really does match an underlying semantic similarity. Form follows function. The Bauhaus architects would be happy. (See, I learned something in Munich.)

- Reed

Chertsey is not London

2004-11-01T22:51:00.000-08:00

[This post has nothing to do with Yukon, and should in no way be interpreted as my having being the least bit disappointed by my experience in, uh, London]

If you ever go to a training class at Microsoft in London, check to see whether it is really in Chertsey, which is where their new training facility is. (Apparently, the facility came along with the Great Plains acquisition.) And if you go to Chertsey, be aware that you are going to a charming little town out in Surrey, and don't expect to pop down to the west end for a show in the evening. And if you stay at the Hilton Cobham (which really is a nice place), expect to be out in the middle of the forest at a popular "hideaway" where the only way out is by car or taxi--an easy hour to Chertsey during rush hour.

Use Hierarchies are Fake

2004-10-28T22:37:00.000-07:00

One of the strongest points I tried to make while teaching about the new world of Yukon Analysis Services is that User Hierarchies are completely devoid of meaning--except insofar as they map to attribute hierarchies. One of the students had an interesting scenario, where she wanted to calculate something only at the product level, never at any higher level of a user hierarchy. Adding IIF structures to test for every possible user hierarchy seemed daunting--and unsupportable. So we tried a simple test.

We put a set of User Hieararchy members on Rows in a report, and then created calculated members to show the name of the current member of the attribute hierarchies. So on rows, we had [User Hierarchy].Members, but on columns displayed [Attribute Hierarchy].CurrentMember.UniqueName, etc. In all cases, the current member of the attribute hierarchy was the member mapped to from the user hierarchy.

This was perfect. In the MDX, we could test for [attribute hierarchy].currentmember.level.ordinal <> 0, and it wouldn't matter where we were in whatever user hierarchy.

- Reed

DTS Programming

2004-10-26T22:03:00.000-07:00

Let's be honest. It's, er, challenging;to code against the DTS object model. But a lot of the reason is because DTS--oops, Integration Services--is not really a set of objects, but rather a development platform with two primary engines, the runtime engine (for control flow) and the pipeline engine (for data flow). All the tasks and transforms are "plug-ins" that somebody wrote to fit into one of the two main engines. Once you get that distinction clear, and pay close attention to when you are writing to the DTS engine, and when you are writing to the plug-in componen; much of the confusion goes away.

I started using a pseudo-hungarian naming convention where the last letter of the prefix was 'o' for the 'outer' or engine object, and 'i' for the 'inner' or plug-in object. Once I did that, I realized that there was a remarkable pattern going. All the communication with the 'outer' object is essentially the same, regardless of what you're creating, and the 'inner' object code is where the uniqueness of the plug-in reveals itself.

Perhaps that's why they decided to give DTS a fancy new name.

- Reed

SQL and MDX

2004-10-25T00:00:00.000-07:00

I just finished delivering the first two rounds of the BI Development course--in Munich and London.

One of the most popular sections was a comparison between SQL and MDX queries. In it, I try to clarify a) how the query structures are similar, b) how they way they are interpreted is different, and c) how MDX is very powerful and extensible for reporting--given the modular nature of set functions.

This is not particularly new for Yukon, but in fact the UDM is a major feature of Yukon, and access to the UDM requires MDX queries, so it becomes increasingly relevant in Yukon.

- Reed

When is Data Mining not Data Mining

2004-10-19T23:59:00.000-07:00

After I taught my nice, dry, boring exposition on Data Mining in Munich, Raman Kohli (the Ascend liason who accompanied me from Redmond to make sure I didn't screw up too badly), came in and suggested the idea that data mining should never be referred to as such. That you should talk only about the solutions--Market Basket Analysis, Trend Analysis, etc--and not even use the words 'data mining'. Now, I managed to ignore most of what Raman said to me, but this one actually clicked home. Each of the Data Mining models is actually built to solve a particular type of problem, or analysis, and 'data mining' just happens to be the engine or technique that populates the model with coefficients from actual data.

So, in London, I changed the title slide of the Data Mining section to 'Market Basket Analysis and other Cool Tools'. Raman thought I was making fun of him, which I wasn't (OK, maybe I was, but just a little).

-- Reed

Limiting Delimiters

2004-10-17T21:43:00.000-07:00

I had already noticed that Yukon MDX did not require braces around a single member set. So the fragment SELECT [Sales Amount] ON COLUMNS now works fine. What I didn't know until a student pointed it out today is that the MDX in a WITH MEMBER clause no longer needs apostrophes around the expression. So you can say With Member [Measures].[Test] as Sum(Product) instead of ... as 'Sum(Product)'. One appreciates those little things that make ones life simpler.

-- Reed

Conditional Strings and Numbers

2004-10-14T21:35:00.000-07:00

One of those minor but terribly annoying facts of MDX in AS2K was that the IIF function was really two functions, one of which could return a number and another that could return a string. Thus, you could never return "OK" if True and 2.134 if False.

It is hard to describe how euphoric I got just from discovering that you can now mix and match strings and numbers in an IIF function. Maybe I just need more sleep.

-- Reed

Inverse Sets

2004-10-10T22:49:00.000-07:00

In an earlier entry “Getting something out of nothing”, Scott explained his solution for using a separate measure group to copy only the desired values from a cube, leaving the rest null.

I had an idea that might be even simpler in some situations. Rather than create a measure group, create a new dimension--similar to a Scenario dimension--where all the values in the fact table have one value. Then you can simply scope on the second member, assigning the selected values from the first.

- Reed

Willkommen!

2004-10-07T05:20:00.000-07:00

Greetings from Munich. I have been teaching the first full delivery of the BI Development course. A few rough spots, but overall I think it's gone fairly well.

I managed to spend my whole first day here touring the city desperately searching for a US-German power socket adapter. I managed to make full use of trains, subways, trams, busses, and foot, all over the city, only to find what I needed back at the main train station. But it was a fun way to explore the city. Even funner was going with the elegant receptionist at the hotel down to the workshop in the basement to hack-saw up the adapter to allow my cell phone charger to fit.

-- Reed

Getting something out of nothing

2004-10-05T22:17:00.000-07:00

I was working on a cube that where I wanted values visible for some cells and all other cells to be null. I wanted to define a subcube that would have visible values and apply any needed MDX. Then I would define the complement of this subcube and set the cell values to null. The problem was that while the subcube where I wanted values was easy to define, the complement of that subcube was very difficult to define

I next tried to create a calculated member that would be null and then use a SCOPE statement and an assignment statement to make the calculated member have values where I wanted to see data. The problem with this is that you can only use assignment statements to assign values on the physical space of the cube. You can not assign a calculated member a new value.

My solution was to create a new measure group and measure. In the measure group SQL statement I had “WHERE 1=2”. This means that the measure group will always be empty. But because the measure is a physical part of the cube, I can use a SCOPE statement and an assignment statement to apply MDX to the cells that I want to have values, setting the measure in the empty measure group equal to the measure in the measure group that contains data. The cells that I want null are already null in this new measure group. The final step is the set Visible=False in my original measure.

Scott

Hurricanes

2004-10-03T22:44:00.000-07:00

I just finished up the Reporting Services pre-conference, and the regular session at PASS before flying to Germany to do the first full-week delivery of the Ascend BI course.

Unfortunately, because Jeanne swept through Orlando, the airport was shut down from Saturday noon until Monday noon, so none of us were able to get to the pre-pre-conference session on Yukon. I flew to Pensacola on Sunday, thinking it would be easier to get to Orlando from there, only to find that my flight to Orlando was cancelled at the last minute because Jeanne had closed down Atlanta, preventing the crew from arriving.

So to avoid cancelling the Tuesday pre-conference session, I caught the 5:20 early flight out, arriving in Orlando with 20 minutes to spare before my pre-conference session began. The PASS organizers were a little nervous. The session was on Reporting Services--mostly 2000--and went quite well.

Hopefully, tomorrow I can make it over to see a bit of the closing day of Oktoberfest.

-- Reed

UDM, MDX, and SQL

2004-07-11T11:45:00.000-07:00

One of the major new features in Yukon BI is the Unified Dimensional Model, or UDM, and one of the big benefits of the UDM is the seamless integration of relational and multi-dimensional (MOLAP) data. The idea is that you define the data logically and then decide separately how parts should be physically implement--using purely relational storage, by adding explicit MOLAP aggregations, or by some cool new combinations. It's a great concept.

Most people I've talked to who have heard about the UDM in Yukon have interpreted the idea of “seamless integration“ to mean that they could arbitrarily write either SQL or MDX queries against the UDM. I have to admit that I am one of those who made that assumption. The reality is that to use the UDX you always (for all practical purposes) use MDX, regardless of whether the data is stored relationally or multi-dimensionally. When I first found out that accessing the UDM requires MDX, I was a bit, shall we say, disappointed. I'm a big fan of MDX, and know that there are a lot of upsides to using MDX for reporting, but it has always been plagued by a few serious “downsides“ that made it unusable in some situations, so it was hard to envision building all reporting queries using MDX.

But most of the downsides of MDX have actually been limitations of Analysis Services 2000. For example, it's easy in SQL to create a query to group by, say, Invoice Number, even if there are 10 million invoices. (Not smart, necessarily, but easy.) In AS2K (at least 32 bit), it was simply impossible to create that kind of query. But the limitation was actually how big the dimension could be, not the MDX query per se.

Other limitations that were part of MDX have been removed in Yukon. For example, in SQL, if the filter is WHERE Type In (1,2,3), but there is no type 2, the result set simply does not include item 2. In AS2K MDX, if you request Types 1,2,3 on rows, and there is no type 2, the query fail. This is simply fixed in Yukon.

Some other time, I'll wax eloquent about the upsides of MDX as a reporting language.

Reed