I am just starting to explore using Analysis Services and started building a
cube using the data I am familiar with and have hit two problems (or
misunderstandings of how I should be using AS).
One is when an attribute has both a code and a description. For example a
department in a business may be call "The Very Big Department" but everyone
knows it by its code "VBD". What I want to do is have department as an
attribute of an Employee and allow the users to select if they want to have
the long or short name displayed. From my exploring so far it seems I have
to define two attributes "DepartmentShortName" and "DepartmentLongName". Is
this right? (It seems odd to me to have two attributes when I know they
mean the same thing and are just different descriptions).

My second question is when I have what I call a dimension of a dimension.
Imagine you have a business structure dimension. e.g. Country > City >
Branch. and every employee is under one point in this structure. Now
imagine a entity that has two employee related to it. Say a sale that has a
salesman employee and a technician employee. You may want to drill down the
business structure from the salesman or technician viewpoint.

I can do this (very easily too I was happy to find) in AS2005 as it creates
multiple structure duimensions for me due to my relationships. The problem
I am having is that if I select the "Salesman - Structure" dimension I can
only drill down to the Branch. It does not then drill on down into the
Employee (giving me a list of salesman in the branch). Why is this? I have
discovered I could then drag on the employee dimension but that seems
redundant to me. Have I missed something?

Any help is greatly appreciated. Thanks.

Hi David.

One of the more fundamental aspects of dimensional modeling is to get the
business users to agree to call things by the same name. If however, you
can't get them to do that, then create another hiearchy within the dimension
that gives the users their choice of how they want things called.

For example, create a hiearchy called LongNamedBusiness and another called
ShortNamedBusiness or you could combine the abbreviation with the
description such as "Very Big Department - (VBD)" which would then allow the
business users from the different camps to refer to the same departments.
You are seeking clarity in the dimensions and cubes.

Your "dimension of a dimension" I think is what is called a Dimension
Hiearchy. It's not redundant to do what you did. The wizard will do a best
guess on hiearchies for you. Redundancy is your friend in the dimensional
world.

HTH
=Tim


"David Kelly" <dmkelly10 (AT) REMOVEMEhotmail (DOT) com> wrote

Tim,

Thanks for your input.

I am not sure I explained my "Dimension of a Dimension" very well. Try the
following case:

You always have the same way of looking at geography (Country> State > City
A Customer has a post code and so does a Distributor.

The Cube wizard would build Customer, Distributor, Customer - Geography and
Distributor - Geography Dimensions.

My main problem is that if I drill down one of these Geography dimensions
then it stops at post code and does not drill on to the customer (or
distributor as appropriate) dimension. I can drag on the customer dimension
below this but I thought I would not need to. Is there a way I could avoid
this?

I can see that I could define the Customer Geography Dimension as a
hierarchy inside the Customer dimension but this means maintaining the way
we drill down geography in two places, in both the customer and the
distributor dimensions.

I hope this explains it better. In actual case the problem expands out to
effect 6 or seven dimensions.

Thanks


Dimension

"Tim Dot NoSpam" <Tim (AT) MindYourSpammy (DOT) spam> wrote

Hi David. Would you be willing to share your dimensional model with me?
You can email me at tim&dot;nospam&at;hughes&dot;net. I hate spiders. <g>

I'm curious what your dimensional model looks like.

To answer your question though, the wizard does not do an outstanding job of
building hierarchies for you. In most cases, I've had to remove the
hierarchies and redo them manually.

I fought with the whole redundancy issue my first few BI projects since I
come from the relational world where redundancy is bad, but ultimately, more
redundancy typically yields better performance and adaptability to changing
business.


"David Kelly" <dmkelly10 (AT) REMOVEMEhotmail (DOT) com> wrote

Did you receive my email? I am not sure if I decoded your email address
correctly.