ColumnName Property on an Attribute

I have a Matter dimension with an OrgUnit attribute, the ColumnKey is Matter.OrgUnitKey and the ColumnName is OrgUnit.Name:

This is the select generated when processing the cube is:

SELECT DISTINCT[Foundation_Matter].[OrgUnit1Key] AS [Foundation_MatterOrgUnit1Key0_0],[Foundation_OrgUnit1].[Name] AS [Foundation_OrgUnit1Name1_0]

FROM [Foundation].[Matter] AS [Foundation_Matter],[Foundation].[OrgUnit1] AS [Foundation_OrgUnit1],[Foundation].[Person] AS [Foundation_Person]

WHERE (([Foundation_Person].[PrimaryOrgUnit1Key] = [Foundation_OrgUnit1].[Key])

AND([Foundation_Matter].[BillingPersonKey]= [Foundation_Person].[Key]))

Foundation_MatterOrgUnit1Key0_0

Foundation_OrgUnit1Name1_0

-1

(Unknown)

2

Firm 1

2

Firm 2

2

Firm 3

3

Firm 1

3

Firm 2

3

Firm 3

4

Firm 1

4

Firm 2

4

Firm 3

This is more or less the select I was expecting:

SELECT DISTINCT[Foundation_Matter].[OrgUnit1Key] AS [Foundation_MatterOrgUnit1Key0_0],[Foundation_OrgUnit1].[Name] AS [Foundation_OrgUnit1Name1_0]

FROM [Foundation].[Matter] AS [Foundation_Matter],[Foundation].[OrgUnit1] AS [Foundation_OrgUnit1]

WHERE ([Foundation_Matter].[OrgUnit1Key] = [Foundation_OrgUnit1].[Key])

Foundation_MatterOrgUnit1Key0_0

Foundation_OrgUnit1Name1_0

-1

(Unknown)

2

Firm 1

3

Firm 2

4

Firm 3

The question is how is the SQL statement build?

I have defined the following relationships:

Matter.OrgUnitKey = OrgUnit.Key

Matter.BillingPersonKey = Person.Key

Peson.OrgUnitKey = OrgUnit.Key

I can define a select in the DSV to get the Name with the Key but like to avoid it if I can,

Please Help

It looks like you have a loop in your relationships and SSAS has chosen to go through the person table to get to the OrgUnit. Do you have any attributes in your Matter dimension that reference the Person table? If not, you might need to create a named query in the DSV for the OrgUnit table so that there are two instances of it. In this way you could break the realtionship loop into 2 pieces so SSAS would not be able to choose the wrong path.

|||

There is a relationship Matter to Person and Person to OrgUnit.I was trying to avoid going to the DVS, but it seems unavoidable.There is more that one Dimension that will have this problem.

Thank you for you quick response.

Color

Is color an attribute or an entity?Is brush a noun or a verb?
"Earl" wrote:

> Is color an attribute or an entity?
>
>|||lol.
In other words, how are you using it?
If you want a limited list of colors, create a color table to hold the valid
colors (with RGB values if appropriate). In this table, color will be an
entity.
If you reference the colors table as a FK in another table, it will be an
attribute.
i.e. (disclaimer: this is nto the optimal way to store colors, just a quick
example):
Create table Colors
(
Color varchar(20) primary key not null
, RedVal integer not null
, BlueVal integer not null
, GreenVal integer not null
)
-- in the above color is an entity
-- redval, blueval, greenval are attributes of color
Create table Wigs
(
WigID integer primary key not null
, Material integer not null --(FK to materials table)
, Color varchar(20) not null --(FK to Colors table)
, Length integer not null
, style integer not null --(FK to Styles table)
)
-- wig is an entity
-- color, material, length, and style are attributes of a wig
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:E69B6AEB-9568-4713-91E1-948BDA924131@.microsoft.com...
> Is brush a noun or a verb?
> "Earl" wrote:
>|||Jim.. dude.. u got patience :)
I started blogging from today.. Just taking up the usual questions that we
get and putting it there.. check out when you get time and let me know your
comments.
http://omnibuzz-sql.blogspot.com/|||SQL Garbage Collector, hehehe
I was going to leave a comment but it looks like you disabled comments
Good luck with the blog
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Omnibuzz wrote:
> Jim.. dude.. u got patience :)
> I started blogging from today.. Just taking up the usual questions that we
> get and putting it there.. check out when you get time and let me know you
r
> comments.
> http://omnibuzz-sql.blogspot.com/|||Exactly. Creates some interesting decisions far beyond the usual
"widgets+material+color..." scenario.
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:E69B6AEB-9568-4713-91E1-948BDA924131@.microsoft.com...
> Is brush a noun or a verb?
> "Earl" wrote:
>|||Oops.. Truth is I am new to blogging... will enable it...|||Actually what spurred the one line question was a modification of some
history structure in a pricing database. For the purpose of this particular
project, "color" is originally an attribute of color swatches, along with
the swatch image and the color type. The swatch color is then an attribute
of the Colors table as an exterior choice, an interior choice, whether it is
active, and the applicable brand, etc. It is also an attribute of color
costs, along with whether a setup charge is applicable and its cost
attribute (in this case, an FK back to the Costs table). Where the line
starts to blur is on the history side. While a color can be an attribute of
a widget, an historical color has its own attributes, particularly when it
was last revised and the value of its historical cost.
"Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
news:%23fRM2aKiGHA.3904@.TK2MSFTNGP02.phx.gbl...
> lol.
> In other words, how are you using it?
> If you want a limited list of colors, create a color table to hold the
> valid
> colors (with RGB values if appropriate). In this table, color will be an
> entity.
> If you reference the colors table as a FK in another table, it will be an
> attribute.
> i.e. (disclaimer: this is nto the optimal way to store colors, just a
> quick
> example):
> Create table Colors
> (
> Color varchar(20) primary key not null
> , RedVal integer not null
> , BlueVal integer not null
> , GreenVal integer not null
> )
> -- in the above color is an entity
> -- redval, blueval, greenval are attributes of color
> Create table Wigs
> (
> WigID integer primary key not null
> , Material integer not null --(FK to materials table)
> , Color varchar(20) not null --(FK to Colors table)
> , Length integer not null
> , style integer not null --(FK to Styles table)
> )
> -- wig is an entity
> -- color, material, length, and style are attributes of a wig
> "Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
> news:E69B6AEB-9568-4713-91E1-948BDA924131@.microsoft.com...
>|||Could you post some simplified DDL for this?
Just to satisfy my curiosity.
"Earl" <brikshoe@.newsgroups.nospam> wrote in message
news:%23XqJ53KiGHA.3572@.TK2MSFTNGP04.phx.gbl...
> Actually what spurred the one line question was a modification of some
> history structure in a pricing database. For the purpose of this
particular
> project, "color" is originally an attribute of color swatches, along with
> the swatch image and the color type. The swatch color is then an attribute
> of the Colors table as an exterior choice, an interior choice, whether it
is
> active, and the applicable brand, etc. It is also an attribute of color
> costs, along with whether a setup charge is applicable and its cost
> attribute (in this case, an FK back to the Costs table). Where the line
> starts to blur is on the history side. While a color can be an attribute
of
> a widget, an historical color has its own attributes, particularly when it
> was last revised and the value of its historical cost.
> "Jim Underwood" <james.underwoodATfallonclinic.com> wrote in message
> news:%23fRM2aKiGHA.3904@.TK2MSFTNGP02.phx.gbl...
an
an
>|||Hah.. at last got that comments working :)
And thanks for the wishes
"SQL Menace" wrote:

> SQL Garbage Collector, hehehe
> I was going to leave a comment but it looks like you disabled comments
> Good luck with the blog
>
> Denis the SQL Menace
> http://sqlservercode.blogspot.com/
>
> Omnibuzz wrote:
>