Microstrategy vs SSAS

Question

A sample Dimension table structure is given below for reference.

CREATE TABLE [dbo].[DimDoctor]
(
    [skDoctorKey] [int] IDENTITY(1,1) NOT NULL,
    [DocCode] [char](9) NOT NULL,
    [FirstName] [varchar](30) NULL,
    [lastName] [varchar](30) NULL,
    [MiddleName] [varchar](30) NULL
)

About table: Here, skDoctorKey is an identity column. The primary key is DocCode 3 name columns.

Microstrategy: If we are using this table in Microstrategy, we will use like, [DocCode] is the ATTRIBUTE and [FirstName],[lastName] and [MiddleName] are three qualifiers of the attribute DocCode. The end result from this table is a single attribute with three qualifiers.

SSAS: I added DocCode as 1 attribute, with keyColumn as [DocCode]. The three columns [FirstName],[lastName] and [MiddleName] need to be added as seperate attributes. For all of them, The keyColumn is same which is [DocCode]. The only way I found was drag [FirstName],[lastName] and [MiddleName] into the attributes pane and then change key column to [DocCode] for all 3. I need to do this one by one.

Forgive me if i made a mistake in the SSAS or Microstrategy concept..

Question:

----------
In SSAS, is this the right way of adding multiple attributes with same key?, 
Or can we associate [FirstName],[lastName] and [MiddleName] 
with the 1st attribute DocCode in any better way?
----------

An SSAS dimension must have a single Key attribute - which is it? — Mike Honey, Apr 26 '14 at 00:14
@MikeHoney , either [skDoctorKey] or [DocCode] can act as key. Did u mean this? — Jithin Shaji, Apr 27 '14 at 10:49

score 1 · Answer 1 · answered Apr 27 '14 at 19:44

The basic answer to your question will be "Yes".

But, I'm not sure that your end users want to slice by [FirstName],[lastName] or [MiddleName]. Personnaly, in this kind of situation, I'd prefer to create a new calculated field "FullName" with the concatenation of [FirstName],[lastName] or [MiddleName], add this as an attribute (with key equal to DocCode) and then create 3 properties of this "Name" attribute.

To create a property, the first thing we need to do is move the fields [FirstName],[lastName] and [MiddleName] as attributes. Then set the AttributeHierarchyEnabled property to False for the each of them attribute. Go to the Attribute Relationships tab in the dimension designer. Right-click the FullName and click New Attribute Relationship. Then in the Create Attribute Relationship window, find FirstName in the Related Attribute drop down list. Click OK.

With your client, you want be able able to slice and dice with your FirstName property but when displaying the FullName, you'll be able to display all its properties.

I think, I used a wrong table as example. Here I am not planning to slice based on Name columns. I want to call these name columns as a descriptions than to attributes. I thought of using these names in reports etc, like top 10 employees. But I got the answer from the 2nd paragraph. Thanks. — Jithin Shaji, Apr 28 '14 at 06:55

score 1 · Answer 2 · answered Apr 27 '14 at 23:18

I would set the Dimension Key attribute as skDoctorKey (Usage = Key), with the NameColumn property set to DocCode. I would name this attribute as Doctor Code or similar.

Then I would add the other columns as separate attributes, with no NameColumn setting.

In most client tools, this design will let you access the Name-related attributes as Properties of the Dimension Key attribute, e.g. in Excel right-click on a Row or Column label cell and choose Show Properties In Report.

I would leave the Name-related attributes visible (as they are by default), but you can hide them by setting AttributeHierarchyVisible = False.

I presume the skDoctorKey has no analytic/reporting value so does not need to be visible to client tools.

Microstrategy vs SSAS

2 Answers2