Hi All

I am using SQL AS 2005 to build a cube containing amongst others two
dimensions [Site] and [Site Collection]. The first is like a customer type
dimension and the second is a list of "things" that can be found at a site.
For my simple example, I want to create a new attribute on [Site] that
contains the value of the [Site]'s [Bakery Type] which is on Site Collection.
In pseudo code this would be
Site.Site Collection.Bakery Type. I am sure this can be done in MDX so that
when I browse the attributes of Site, a new attribute called Bakery Type
would appear and this would give me the bakery type of the site that is
stored on the site collection dimension. Both dimensions are linked via a
fact table.

Not sure that I fully understand the relation between [Site] and [Site
Collection] - is there a fact table linking them because each site can
have multiple [Site Collection] members associated with it? And how is
the set of types, like [Bakery Type], defined - is there a field in a
table with the type of a member? In any case, it's not clear how new
dimension attributes would dynamically be created via MDX - could you
elaborate on what you have in mind?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Hi Deepak

Thanks for your response. I will attempt to explain what I am trying to do:

1. Site and Site Collection are dimensions linked via a fact table. The main
fact field is an item count
2. One site has many site collection items
3. Site Collection items have a three level hierarchy Class, Type and Item
4. Bakery Type in the Site Collection is represented in the Site Collection
Hierarchy as [APO].[Bakery].[Corrner Bakery]. Another item might be
[Facility].[ATM].[Standard Bank]
5. I want to have an attribute available to the users called Bakery Type
(and ATM Brand for the second example above so that you can have all the
sites listed together with their bakery types and atm brands.
6. I am using Office Web Components 11 for end user access to the data for
analysis and Reporting Services for reports.
7. I have managed to define calculated fields based on the item count
measure (e.g. No of Bakery, No of ATM using the SUM([Site Collection
Type].[APO].[Bakery], [Measures].[Items]) logic) but am battling to do the
same when there are no measure fields involved.
8. In a similar way I want to be able to combine a dimension attribute
together with a measure to get a calculated field. The one example is where
square metres is an attribute of one of my dimensions and sales is a measure
and I want to define a calculation for sales per square metre in the form of
[Measures].[Sales] / [Dimension].[Square Metres attribute].

Hope this is enough info for you

Many thanks

"Deepak Puri" wrote:

Not sure whether you're using AS 2000 or AS 2005 - below are some ideas
for a few of the items, in AS 2000 syntax:

For Items #5/7, if you're trying to list the Type and Item for each
Site, based on the record in the fact table:

With
Member [Measures].[ItemName] as
'[Site Collection].CurrentMember.Name'
Member [Measures].[TypeName] as
'[Site Collection].Parent.Name'

select {[Measures].[TypeName], [Measures].[ItemName],
[Measures].[Items]} on 0,
NonEmptyCrossJoin([Site].[Site].Members,
[Site Collection].[Item].Members,
{[Measures].[Items]}, 2) on 1
from [SiteCube]

For item #8, here's a sample Foodmart query which calculates Sales per
square foot of a store:

Member [Measures].[Store Area] as
'Sum(Descendants([Store].CurrentMember, [Store].[Store Name]),
CInt([Store].Properties("Store Sqft")))'
Member [Measures].[SalesPerSqft] as
'[Measures].[Store Sales] / [Measures].[Store Area]',
FORMAT_STRING = 'Currency'
select {[Measures].[Store Sales], [Measures].[Store Area],
[Measures].[SalesPerSqft]} on 0,
Descendants([Store].[All Stores].[USA].[OR]) on 1
from Sales

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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