MS SQL Server OLAP - how to get a properties of another dimension?

Author

how to get a properties of another dimension?

tong via SQLMonster.com

2005-08-27, 9:23 am

WITH
MEMBER [Measures].[Store Type] AS '[Store].CURRENTMEMBER.PROPERTIES
("Store Type")'

SELECT
{ [Measures].[Store Type] , [Measures].[Employee Salary] } ON COLUMNS,
{ [Employees].[All Employees].[Sheri Nowmer].[Derrick Whelply].[Beverly
Baker].[Shauna Wyro].[Bunny McCown].CHILDREN } ON ROWS
FROM [HR]

hello:
i want to get a "Store Type" property of every employee, upon is my code ,
but the result look like this:
Store Type Employee
Salary
---------------------------------------------------------------------
Wanda Hollar #ERR 39.94
Nancy Miler #ERR 70.20

how can i get the right "Store Type" of every employee?

mike

2005-08-29, 11:23 am

you need to have the store set included in your select: this is not exactly
what you want but you can run it and hopefully this will get you closer to
what you want.

WITH
MEMBER [Measures].[Store Type] AS
'[Store].CURRENTMEMBER.PROPERTIES("Store Type")'

SELECT
non empty { [Measures].[Store Type] , [Measures].[Employee Salary] } ON
COLUMNS,
{ nonemptycrossjoin({Descendants([Employees].[All Employees].[Sheri
Nowmer].[Derrick Whelply].[Beverly Baker].[Shauna Wyro].[Bunny McCown],1)} ,
{[Store Type].Members}) } ON ROWS
FROM [HR]

"tong via webservertalk.com" wrote:

> WITH
> MEMBER [Measures].[Store Type] AS '[Store].CURRENTMEMBER.PROPERTIES
> ("Store Type")'
>
> SELECT
> { [Measures].[Store Type] , [Measures].[Employee Salary] } ON COLUMNS,
> { [Employees].[All Employees].[Sheri Nowmer].[Derrick Whelply].[Beverly
> Baker].[Shauna Wyro].[Bunny McCown].CHILDREN } ON ROWS
> FROM [HR]
>
>
> hello:
> i want to get a "Store Type" property of every employee, upon is my code ,
> but the result look like this:
> Store Type Employee
> Salary
> ---------------------------------------------------------------------
> Wanda Hollar #ERR 39.94
> Nancy Miler #ERR 70.20
>
> how can i get the right "Store Type" of every employee?
>

Deepak Puri

2005-08-29, 11:23 am

Looking at the schema of the Foodmart HR Cube, Store is really a
"Reference" dimension in AS 2005 terms, derived from the Employees
dimension. But in AS 2000 it is simply configured as a standard
dimension. One way to infer the relationship between an employee and the
corresponding store
is via the cube itself, using NonEmptyCrossJoin(),
since data for a
given employee will only "exist" for that store:
[color=darkred]
WITH
MEMBER [Measures].[Store Type] AS
'Tail(NonEmptyCrossJ
oin(
[Store].[Store Name].Members, {[Employees].CurrentMember}, 1))
.Item(0).PROPERTIES("Store Type")'

SELECT
{ [Measures].[Store Type] , [Measures].[Employee Salary] } ON
COLUMNS,
{ [Employees].[All Employees].[Sheri Nowmer].[Derrick
Whelply].[Beverly Baker].[Shauna Wyro].[Bunny McCown].CHILDREN } ON
ROWS
FROM & #91;HR][color=darkre
d]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.droptable.com ***

Sponsored Links