Mapping two measure groups with different dimension usage

Hello.

I am looking for a solution for the following situation:

I have a measure group for my business facts and a measure group for tolerances and targets.

The first measure group uses a date dimension, a site dimension, a project dimension and an employee dimension.

The second group shares all dimensions except the employee dimension.

Lets say I have the business fact sales per hour.
The target value for this fact is stored in the second measure group without the employee info. The targets are valid for all employees at that site, project and day.

When i browse the cube i want see the targets next to the actual data - even on employee level.

Therefore i have to set IgnoreUnrelatedDimensions in the properties of the second measure group to True.
But when i do this i get an entry for every employee present in the employee dimension, showing the tolerance and target.

I am lost at the moment and belive that there must be an elegant solution. Hopefully someone has an idea?

Thanks,
ThomasFrom your description, it's not clear in which cases the IgnoreUnrelatedDimensions doesn't meet your needs - could you describe some scenarios? You could selectively use the MDX ValidMeasure() function in calculations, instead of applying IgnoreUnrelatedDimensions.|||Hello Deepak.

Thanks for your reply.

I'll try to explain my problem better with an example.

Data in my Tolerance FactTable:

DateProjectSiteTolerance_Sales_per_Hour20070320Upsell1Hamburg220070321Upsell1Hamburg220070322Upsell1Hamburg3

Data in my Sales FactTable:

DateProjectSiteEmployeeSales_Per_Hour20070320Upsell1HamburgBob1.520070320Upsell1HamburgMike2.120070321Upsell1HamburgBob2.720070321Upsell1HamburgMike3.320070322Upsell1HamburgBob1.2

When I select 20070322 as Date in the Browser, i get a row for Mike, although he dind't work on 22th.

DateProjectSiteEmployeeSales_Per_HourTolerance_Sales_per_Hour20070322Upsell1HamburgBob1.2320070322Upsell1HamburgMike3

Thanks to a tip from Markus i am using Scope-Statements at the moment

SCOPE([Measures].[Tolerance_Sales_Per_Hour]);
THIS=IIF (ISEMPTY([Measures].[Sales_Per_Hour]),NULL,[Tolerance_Sales_Per_Hour]);
END SCOPE;

But we are not sure, if this is the best solution.

Best regards,
Thomas

Many-to-Many relationship with Time dimension?

Is it possible to use a Time dimension as the intermediate dimension in a many-to-many relationship? So far, I have not been able to make it work. When I attempt to setup the relationship, I get a warning on the Define Relationship window that says:

This relationship cannot be defined because no intermediate measure group or dimension table exists.

However, the intermediate measure group and dimension tables do exist. The New Cube... wizard picks up the tables but creates all the relationships as Regular, in effect I have two separate measure groups. Do I have to do something special to define a measure group as intermediate?

The business problem I'm trying to model here is special time periods that can overlap - think college and university semesters. I can have a semester where any given day (the granularity of my time dimension) can exist in multiple semesters. So I am trying to create a many-to-many relationship from my "semesters" to an intermediate mesaure group to time dimension to real measure group.

Any help or guidance is much appreciated!

Hello. Check for intermediate dimensions in BooksOnLine. This is what I think you are trying do do. Many-to-many dimensions are between a normal fact table and a many-to-many artificial fact table with the many-to-many relation with a dimension table.

A good example is an account that is shared between two persons. You have the single account in the dimension and the many-to-many relation between the persons and the account in the artificial fact table.

Here is a link to a Blog about the new dimension types in SSAS2005: http://blogs.conchango.com/christianwade/archive/2005/04/07/1255.aspx

An intermediate dimension is when you do not have a direct relation between a fact table and a dimension. Normally you have a direct relation.

The use for this type is more limited since that you can always enter more dimenion keys into a fact table.

I would think about two new attributes in the time dimension like:

-CollegeSemesterDayFlag

-UniversitySemesterDayFlag

HTH

Thomas Ivarsson

|||

Thanks for the response Thomas. I do want a many-to-many dimension, I just didn't explain my business problem clearly. I have potentially multiple, overlapping, arbitrary time spans (again, think college/university semesters), where any given day can exist in multiple semesters. This is very similar to the example you gave above with the accounts shared between multiple people. My problem is that I could have a semester called FALL from Sep1-Dec31 and a semester called FALL_SPECIAL Oct1-Oct31, where the date range Oct1-Oct31 falls into two semesters. Also, from year to year, there will be different semesters - some years won't have a FALL_SPECIAL, but have a WINTER_SPECIAL, etc.

Anyway, I solved my problem and it was because the New Cube... wizard didn't find all the relationships. I am using my time dimension as the intermediate dimension and it is also acting as a role playing dimension. The New Cube... wizard didn't pick up all those relationships, so I had to manually setup to the many-to-many relationship for all five of my role playing time dimensions, only then it recognized the second fact table as an intermediate fact table. I also had to delete a time dimension that showed up in the Dimension Usage tab that didn't belong but the New Cube... wizard put in there.

I haven't explored all the data to make sure it's aggregating correctly, but it appears to be doing what I want and I'm quite impressed I might add! I just need to verify that the ParallelPeriod() and Cousin() functions behave properly.

|||

Great that you have solved the problem.

I think that this is no more than standard many-to-many modelling.

You have several colleges and universities on top with their own semester schedule and this means that it will be a many-to many relation with the time dimension.

This means an education institution-semester dimension on top of the time dimension.

Regards

Thomas

sql

many-to-many parent-child relationship in the dimension hierarchy

This novice would like your assistance to solve a problem. The goal is to build a Data mart for use in a cube. The cube would have a Service Fact related to the Dimension of Personnel and Assets. The Measures would be:

Provision of Assets by Departments

Availability of Assets by Departments or Service

Volume of Support Calls by Departments

The model has one major problem: a many-to-many parent-child relationship in the hierarchy of the Asset Dimension. The hierarchy within the Dimension is

Bundle

Service

Assets

And the many-to-many relationship resides between Assets & Service.I wish to know how best to handle this situation; create a bridge table or create a Service Dimension. All advice is welcomed.

What is the business process you are modeling in your fact table? The description of the measures is a little confusing to me.

Regarding the many-to-many relationship, you are saying that the fact table has a relationship to an asset, the asset belongs to multiple services, and a service belongs to a single bundle. Is that correct?

Also, what is the relationship between the fact and the service? Does a single fact record associate to a single service or does it associated to multiple services through the asset?

Thanks,
Bryan

|||The main objective is to create a cube on IT Support for performance reporting against SLAs, with a secondary objective being to expose the use of IT within the organisation. The high level categories of measures are: provisioning, availability, volume, and minutes. These would translate into measurement exposing the aggregated number of Assets provisioned; the percentage an Asset was available; the aggregated number of support calls received for an Asset; the minutes taken to close a support call on an Assest. Basically, sliced, diced and rolled up and down by organisation section, business unit, IT bundle and service. This is datamart design based on this information:

Code Snippet

Assets Dim -> Support Fact <- Personnel Dim
AssetID AssetID PersonnelID
Class PersonnelID Job
Service %Available Business Unit
Bundle Provisioned Assets Section
Call volume
Call minutes

The Dimensions have been model to the lowest level of granularity. The Personnel Dimension offers no problems with the hierarchy; a staff member only belongs to one job, business unit & section. This is not the case for Assets. All Assets are assigned to a member of staff. An Asset is a member of a class --mobile, pc, server,...--, one or many services and those services are member of one bundle.

"Regarding the many-to-many relationship, you are saying that the fact table has a relationship to an asset, the asset belongs to multiple services, and a service belongs to a single bundle. Is that correct?"

Yes. The IT Business Unit offers 5 bundles of services. Each bundle is comprise of mutually exclusive services. An example would be the End User Computing Bundle comprising of Desk Support, Printing & Fax, etc.

"Also, what is the relationship between the fact and the service? Does a single fact record associate to a single service or does it associated to multiple services through the asset?"

One or multiple through the asset. Each Asset is tired to at lest one service. Assets such as servers offer more than one service.

I appreciate your response and am happy to supply additional information.
|||

According to Kimball methodology, a fact table should represent a single business process. So in this data mart, you would have a fact table for the handling of service tickets, another one for provisioning, another one for asset uptime/availability, and so on and so on. You might bring all this data together in a report using conformed dimensions between the fact tables. I'd recommend taking a look into that. It will make your maintanance of the data much, much easier. (You can easily tie all this data together in your olap cube so your users won't have to jump between fact tables aka measure groups.)

When you do that, I think you will find that some facts relate directly to assets while others relate directly to the services.

Still, you will have assets providing services and need to model that relationship. I'd recommend building a service group table that ties together the services being offered. Then associate an asset with a service group. Here's a rough example:

Asset (AssetID, ServiceGroupID, ..., StartDate, EndDate)

ServiceGroup (ServiceGroupID, ....)

ServiceGroupServiceJunction (ServiceGroupID, ServiceID, ....)

Service (ServiceID, ....)

So, if you had a fact associated with an asset, you would be able to identify what services are provided by that asset. If you are working with Asset as a Type 2 slowly changing dimension, there would be different records for an asset in the Asset table based on the ServiceGroups it was associated with across time.

If you have a copy of "The Microsoft Data Warehouse Toolkit", this is covered on page 60. (I think this is covered in "The Data Warehouse Toolkit" as well but someone has borrowed by copy.)

Hope that helps,
Bryan

|||Bryan,

Thanks for your suggestions and insight. I will have a read of the books mentioned and most likely return with a few questions...hopefully your generosity will continue to provide answers.

Ian
|||

Assuming that you're using AS 2005, the many-many relationship between Assets and Services could be modelled, if separate Asset and Service dimensions are set up. There is 1 measure that isn't just additive, and hence may need further fields in the fact table: "the percentage an Asset was available". Generally, availability is a ratio like available to total time. Even in the simplest scenario, the numerator and denominator would be separately summed (say across Assets in this case), then their ratio taken. So, if the fact table has either "%Available" or "AvailableTime", and "TotalTime", the aggregate "%Available" could be computed.

The schema for AS 2005 could then be something like:

PersonnelDim SupportFact AssetDim Asset<->ServiceBridge ServiceDim

AssetID --> AssetID <-- AssetID

PersonnelID <-- PersonnelID Asset ServiceID --> ServiceID

Job AvailTime Class Service

Business Unit TotalTime Bundle

Section Provisioned

Call Volume

Call Minutes

There would be a Measure Group on SupportFact, with "sum" measures: AvailTime, TotalTime, Provisioned, Call Volume, Call Minutes.

["%Available] could then be defined as: AvailTime / TotalTime.

The Personnel and Asset dimensions would have a regular relation to the SupportFact Measure Group.

An intermediate Measure Group is then defined on the Asset<->ServiceBridge table, to which Asset and Service dimensions are related.

This would allow the Service dimension to have a many-many relation to the SupportFact Measure Group, via this intermediate MG.

Many-to-Many MDX

I have a measure called MyMeasure.

There is a dimension called Account

There is a dimension called AccountGroup that groups many accounts so it references the cube through a Many-to-Many relationship. The account group has a field called Group Level. If for example, I want to know the number of accounts for a region I would do something like this:

SELECT {[Account Group].[Account Group].[New South Wales] } ON ROWS,

{[Measures].[MyMeasure]} ON COLUMNS

FROM [MyCube]

WHERE ([Account Group].[Group Level].[Region])

This will give me:

--

| Account Group | MyMeasure |

--

| New South Wales | 35 |

--

I need the MDX query to get MyMeasure by grade which is another Account Group with Group Level 'Grade' for a particular region (Another Account Group with Group Level 'Region'). The result I am looking for, will look something like this

--

| Account Group | Account Group | MyMeasure |

--

| New South Wales | Grade A | 15 |

| New South Wales | Grade B | 4 |

| New South Wales | Grade C | 6 |

| New South Wales | Grade D | 10 |

-

It is like getting the measures for all accounts that belong to both group levels (one with a known name) grouping by accounts. I appreciate any help ...

Any ideas?...|||

Hi Mario,

Could you explain the AccountGroup and the intermediate measure group source data a bit more - I still can't understand the purpose of this many-many dimension, from a data modelling perspective?

|||

In the DW: every fact table has an AccountKey:

DimAccount (AccountKey, Name,...)

BridgeAccountToAccountGroup (AccountKey, AccountGroupKey)

DimAccountGroup (AccountGroupKey, Name,...)

In the cube, I have DimAccountGroup as a dimension but "behind the scenes" it is referencing DimAccount as a Many-to-Many dimensions through the BridgeAccountToAccountGroup "Measure Group".

|||

That's the technical detail, but from your example, it seemed like the members of AccountGroup were so diverse (eg: New South Wales, Grade A) that they normally would belong in different dimensions. Any insight or reason for that?

An easier option might be to translate your requirement into Adventure Works - for example, using the SalesReason dimension, which has many-many relations. You did mention this in your new post, but can you give a more detailed scenario, using actual data from Adventure Works?

|||

Deepak,

I know attributes like region and all that should be part of the Account dimension, the thing is that some accounts can belong to many region groups or grades. Groups are created by users in the OLTP system so we don't have any control over these, this is why I had to create a Many-to-Many relationship.

For example the DimAccountGroup has the following attributes.

DimAccountGroup (AccountGroupKey, Name, Level)

This dimension could have the following attributes:

1, New South Wales, Region

2, Queensland, Region

3, Grade A, Grade

4, Grade B, Grade

The bridge table will tell me the relationships between Accounts and Account Groups. So what I am trying to do is to get some measures for a set of accounts that belong to two or more (intersection?) predefined account groups but I don't want to see the measures by account, just by account group.

If I wanted to do the same thing with different dimensions it would be very easy:

SELECT
NON EMPTY {
[Product Group].[Product Group].[All].Children
} ON ROWS,
{
[Measures].[Sales]
} ON COLUMNS
FROM [MyCube]
WHERE (
[Account Group].[Group Level Identifier].[Region],
[Account Group].[Account Group].[New South Wales],
[Product Group].[Group Level Identifier].[Brand]
)

The problem is I am using the same dimension twice and it doesn't have a direct relation to the fact table, it is indirectly referenced through the bridge table and the account table (which is another dimension). It is like trying to do something like this (I know this query is wrong):

SELECT
NON EMPTY {
[Account Group].[Account Group].[All].Children //Account group "2"
} ON ROWS,
{
[Measures].[Sales]
} ON COLUMNS
FROM [MyCube]
WHERE (
[Account Group].[Group Level Identifier].[Region], //Account Group "1"
[Account Group].[Account Group].[New South Wales], //Account Group "1"
[Account Group].[Group Level Identifier].[Grade] //Account Group "2"
)

|||

Mario,

Here are sample SQL and MDX queries from Adventure Works, using the SalesReason many-many dimension. These compute Internet Sales Amount for all orders which have both a [Quality] and a [Manufacturer] reason. Hopefully, it is similar to what you want to do with [AccountGroup]:

SQL:

>>

select count(*) as SalesOrders,

sum(fs.SalesAmount) as SalesAmount

from dbo.FactInternetSalesReason fr1

join dbo.FactInternetSalesReason fr2

on fr1.SalesOrderNumber = fr2.SalesOrderNumber

and fr1.SalesOrderLineNumber = fr2.SalesOrderLineNumber

join dbo.FactInternetSales fs

on fr1.SalesOrderNumber = fs.SalesOrderNumber

and fr1.SalesOrderLineNumber = fs.SalesOrderLineNumber

where fr1.SalesReasonKey = 5

and fr2.SalesReasonKey = 9

-

1551 5549896.77

>>

MDX:

>>

select {[Measures].[Internet Sales Amount],
[Measures].[Internet Order Quantity]} on 0
from [Adventure Works]
where Intersect(Exists(
[Internet Sales Order Details].[Internet Sales Orders].[Internet Sales Order],
{[Sales Reason].[Sales Reason].[Quality]}, "Internet Sales"),
Exists(
[Internet Sales Order Details].[Internet Sales Orders].[Internet Sales Order],
{[Sales Reason].[Sales Reason].[Manufacturer]}, "Internet Sales"))

Internet Sales Amount Internet Order Quantity
$5,549,896.77 1,551

>>

|||

Thank you very much Deepak,

I knew I had to use Intersect but I had no idea where to start. Here is the query I needed:

SELECT
NON EMPTY {
[Account Group].[Account Group].[All].Children
} ON ROWS,
{
[Measures].[MyMeasure]
} ON COLUMNS
FROM [MyCube]
WHERE
(
[Date].[Calendar Month Year].[Jul 2005],
Intersect
(
Exists
(
[Account].[Account].[Account],
{
(
[Account Group].[Account Group].[New South Wales],
[Account Group].[Group Level Identifier].[Region]
)
},
"Account To Account Group"
)
,
Exists
(
[Account].[Account].[Account],
{
(
[Account Group].[Account Group].[All],
[Account Group].[Group Level Identifier].[Grade]
)
},
"Account To Account Group"
)
),
[Account Group].[Group Level Identifier].[Grade]
)

In the DW the Bridge table is called BridgeAccountToAccountGroup which is a measure group in the cube called "Account to Account Group".

|||

Mario, I'm glad you found a solution - just wanted to add a nested approach, which might be more efficient:

SELECT
NON EMPTY {
[Account Group].[Account Group].[All].Children
} ON ROWS,
{
[Measures].[MyMeasure]
} ON COLUMNS
FROM [MyCube]
WHERE
(
[Date].[Calendar Month Year].[Jul 2005],
Exists
(
Exists
(
[Account].[Account].[Account],
{
(
[Account Group].[Account Group].[New South Wales],
[Account Group].[Group Level Identifier].[Region]
)
},
"Account To Account Group"
),
{
(
[Account Group].[Account Group].[All],
[Account Group].[Group Level Identifier].[Grade]
)
},
"Account To Account Group"
),
[Account Group].[Group Level Identifier].[Grade]
)

Many-to-many dimension processing performance

Hello,

I have a problem regarding M2M dimension processing performance that I would like some help with please.

We are using SSAS 2005 SP1 Entreprise Edition x64 bit
The main fact table has over a 100 milliion rows.
The entire SSAS database, both dims and facts, are MOLAP.

We have several many-to-many (M2M) dimensions connected to the main fact table via an intermediate measure group (IMG).
The M2M dim and the IMG are using the same database object as their source and are thus linked as fact relationship. This shared object is a 'normal' view not a table or indexed view.

We need to be able to process the M2M dim on a regular basis as user defined members change - sometimes every few minutes.

To do this we need to
1. Process Update the M2M dim
2. Full (or incremental) process the IMG
3. Process Indexes on the main fact

Steps 1 and 2 seem quite quick and arent currently thought to be a problem
Step 3, index processing, is taking too long, a few minutes or more, which our user base wont accept - we need to reduce this time.

Question
-
How can we reduce the time to process the indexes? Can we remove this step altogether in some way? Are there properties I can set to remove the process index necessity?

Please help if you can

--
Thanks in advance
Mgale1

You can try and skip step 3 all together and enable Lazy processing. This allows you to let users query the cube right after step 2. Analysis Server will be processing indexes in the background for you while users query.

This has obvious peroformance impact on the cube, users will be getting slower performance while indexes are not there and lazy processing is still going.

Another way to minimize time for building indexes is to patition you cube and let build index for several partitions to run in parallel. This should shorten processing times.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Edward,

Lazy Processing is enabled on the server; it is the default.setting.
I have also noticed that each fact or dim has a 'processing mode' available from the properties window.
I am confused as to where I should set this.
Do I need to set it on the M2M dimension, the Intermediate Measue Group, the 'local' dimension or the main fact table?

Please help if you can
Thanks
Mgale1

|||

As far as I understand the main time in your schenario goes for processing indexes for the partitions in the main fact measure group. These partitions the candidates for setting processing mode to LazyAggregations.

The rest of the objects should be relatively fast to process.

Edward Melomed.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Many to Many Relationships Fact->Attribute->Leaf in AS2005

I have a dimension with two natural hierarchies climbing up from the leaf.
One hierarchy is quite deep, and is the one I'm interested in. The other
hierarchy has only one level (it's a simple Attribute Hierarchy). The fact
table joins to the simple attribute hierarchy, but I want to analyse using
the deeper one.
There is in effect a many to many relationship between the fact table and
the leaves of the dimension. This information can be implied because we're
descending a natural hierarchy to get from where the fact table joins to the
leaf. Leaf to Attribute is a Many to One relationship, so Attribute to Leaf
is One to Many and Fact to Attribute is Many to One. Many-One-Many =
Many-Many.
What I'd like is for Analysis Services to realise that and give me Many to
Many navigation when I try to analyse the facts using the other hierarchy.
Instead the analysis seems to fail, and I just get the grand total in all
fields at all levels when drilling down the other hierarchy.
To make things work, I'm having to set up the Many-Many relationship
explicitely by creating a Measure Group using the dimension table, along wit
h
two Dimensions (one containing just the joining attribute, the other the rea
l
leaf, the joining attribute and the deep hierarchy). The result is as
expected with correct summations, but it is not as neat a solution as just
having AS work this out from the single dimension.
I notice that attribute relationships in the February CTP version have two
properties - Cardinality and Relationship Type. I can't find any
documentation on these, but wonder if they'd help. I've been treating every
relationship as strict - so if I know a value for an attribute I can always
resolve a single value for any member properties of that attribute. Attribut
e
to Member Property is a Many to One relationship.
As an aside, this introduces subtelty as it means I must be always sure I
can uniquely identify an attribute by its key - a risk where an attribute is
identified by name and the name can appear in multiple places with different
meaning in different parts of the natural hierarchy (two towns in the UK
called Leeds for example). I am currently working around this by using each
parent level as part of the composite key so "Yorkshire, Leeds" is different
to "Kent, Leeds". The keys can get big in a deep hierarchy, but I see at the
moment no other way to do this based on the basic principle that an attribut
e
must be uniquely identified in order to allow AS to uniquely identify its
member properties and satisfy the Many-One relationship.
Thanks
- RichardIf there is a many-to-many from the fact table to the leaf level; then you
need to create an intermediate junction table which represents that M:M
relationship. You create a measure group for that M:M junction table -- and
establish relationships from there. See the AdventureWorksDW for an example.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Richard Corfield" <RichardCorfield@.discussions.microsoft.com> wrote in
message news:F952BDFC-CFBC-49C2-BA86-64BAD15F4F47@.microsoft.com...
> I have a dimension with two natural hierarchies climbing up from the leaf.
> One hierarchy is quite deep, and is the one I'm interested in. The other
> hierarchy has only one level (it's a simple Attribute Hierarchy). The fact
> table joins to the simple attribute hierarchy, but I want to analyse using
> the deeper one.
> There is in effect a many to many relationship between the fact table and
> the leaves of the dimension. This information can be implied because we're
> descending a natural hierarchy to get from where the fact table joins to
the
> leaf. Leaf to Attribute is a Many to One relationship, so Attribute to
Leaf
> is One to Many and Fact to Attribute is Many to One. Many-One-Many =
> Many-Many.
> What I'd like is for Analysis Services to realise that and give me Many to
> Many navigation when I try to analyse the facts using the other hierarchy.
> Instead the analysis seems to fail, and I just get the grand total in all
> fields at all levels when drilling down the other hierarchy.
> To make things work, I'm having to set up the Many-Many relationship
> explicitely by creating a Measure Group using the dimension table, along
with
> two Dimensions (one containing just the joining attribute, the other the
real
> leaf, the joining attribute and the deep hierarchy). The result is as
> expected with correct summations, but it is not as neat a solution as just
> having AS work this out from the single dimension.
> I notice that attribute relationships in the February CTP version have two
> properties - Cardinality and Relationship Type. I can't find any
> documentation on these, but wonder if they'd help. I've been treating
every
> relationship as strict - so if I know a value for an attribute I can
always
> resolve a single value for any member properties of that attribute.
Attribute
> to Member Property is a Many to One relationship.
> As an aside, this introduces subtelty as it means I must be always sure I
> can uniquely identify an attribute by its key - a risk where an attribute
is
> identified by name and the name can appear in multiple places with
different
> meaning in different parts of the natural hierarchy (two towns in the UK
> called Leeds for example). I am currently working around this by using
each
> parent level as part of the composite key so "Yorkshire, Leeds" is
different
> to "Kent, Leeds". The keys can get big in a deep hierarchy, but I see at
the
> moment no other way to do this based on the basic principle that an
attribute
> must be uniquely identified in order to allow AS to uniquely identify its
> member properties and satisfy the Many-One relationship.
> Thanks
> - Richard|||> If there is a many-to-many from the fact table to the leaf level; then you[vbcol=seagreen]
> need to create an intermediate junction table which represents that M:M
> relationship. You create a measure group for that M:M junction table -- an
d
> establish relationships from there. See the AdventureWorksDW for an example.[/vbco
l]
I have this working thanks. I've hidden the new measure (a count measure on
the join) so as not to confuse the end user.
While'st here - could you explain the Cardinality and Type
(strict/otherwise) properties on the Attribute Relationships and check that
my understanding of having to ensure an attribute key uniquely identifies an
instance of that attribute is correct. I'm working on the basis that it can'
t
infer that two instances of an attribute with the same key are different
because they appear at different points in a hierarchy or have different
member properties. AS2000 could do this but its model was completely
different.
Thanks
- Richard|||With SQK2K5, you *must* have a unique key for every attribute. If it isn't
unique, then you need to make it unique. For example, if you have a Time
dimension with Quarters and Months, then in SQL2K you could have the keys
be:
Month: 1 to 12
Quarter: 1 to 4
And tell the system that member keys were not unique across the dimension.
In this case, the system automatically used the hierarchy to create an
internal unique key by walking up the hierarchy.
With SQL2K5, attributes are first-class citizens and as such they must have
a unique key across the entire dimension. This means that it is now your job
to create a unique concatenated key. In the case above, you need to click on
the key field and you will see an option to create a key "collection" --
i.e. what we call a concatenated key. So now your attribute keys need to be:
Month: year + 1 through 12
Quarter: year + 1 through 4
If you don't do this, then the dimension won't be rendered properly and
members will disappear or appear under the wrong parent.
This is a frequent issue that new users (particular SQL2K users coming over
to SQL2K5) run into. Most new folks know what a concatenated key is -- and
just take it as it was relational terms. The folks who struggle with this is
those of us coming over from SQL2K :-)
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Richard Corfield" <RichardCorfield@.discussions.microsoft.com> wrote in
message news:5BC8D21F-D8E4-4D94-A597-1544A1F24793@.microsoft.com...
you[vbcol=seagreen]
and[vbcol=seagreen]
example.[vbcol=seagreen]
> I have this working thanks. I've hidden the new measure (a count measure
on
> the join) so as not to confuse the end user.
> While'st here - could you explain the Cardinality and Type
> (strict/otherwise) properties on the Attribute Relationships and check
that
> my understanding of having to ensure an attribute key uniquely identifies
an
> instance of that attribute is correct. I'm working on the basis that it
can't
> infer that two instances of an attribute with the same key are different
> because they appear at different points in a hierarchy or have different
> member properties. AS2000 could do this but its model was completely
> different.
> Thanks
> - Richardsql

Many to many relationships

Hi,

I have a many-to-many relationship scenario and I had used a group dimension instead of the bridge table concept.

I had referred "Data Warehouse Lifecycle Toolkit" by Kimball and others.

For ex:
An Athlete belonging to multiple athletic teams and with the possiblity of changing every year.

So I had created an Athlete Team dimension, with a group key and the team code.

Athelete Group Key,
Athelete Team Code,
Athelete Team Name

with Athlete Group Key and Athlete Team Code as the key for the table.

And in the fact/measure group, referring to the Group Key.

Now, in SSAS, I had created the Athlete Team dimension with Group Key as the key atrribute and others as dimension attributeS.

***********************
Athlete dimension table
--

Group Key, Athlete Code, Athlete Name,
1 , FTB , Football
1 , TEN , Tennis
2 , TEN , Tennis
2 , VOL , Volleyball

Fact/Measure group

Athlete ID, Year, Group Key
123 , 2001, 1
234 , 2001, 2

************************

When processed and browsed the cube, for the number of students for each type of team, the result is considering only the first team for the count (it is ordered alphabetically).

In the sample data example above, the count of athletes for team Football, Tennis and Volleyball are 1, 2 and 1 respectively.

But in SSAS, the count of athletes for the team Football, Tennis and Volleyball are 1, 1 and 0 respectively.

For the same requirement, the SQL query on the SQLDB, is fetching me the correct result, but this is required for adhoc reporting as well and hence have to be done using the cube itself.

Comments on this would be really helpful.

Thanks,
Vivek C.

Vivek,

I might just be getting stuck on the example, but this sounds like a Type 2 changing dimension and not a many-to-many scenario.

So, you have this Athlete dimension. It's primary key is AthleteID and contains a reference to the TeamID. Here is the structure of the table:

create table Athlete (

AthleteID int not null identity(1,1),

Name varchar(200) not null,

TeamID int not null,

RowStartDate datetime not null default (getdate()),

RowEndDate datetime null,

IsRowCurrent bit not null default (1)

)

alter table Athlete add

constraint PK_Athlete primary key (athleteid),

constraint AK_Athlete unique (name, rowstartdate),

constraint FK_Athlete_TeamID foreign key (teamid) references Team (TeamID)

create table Team (

TeamID int not null identity(1,1),

Name varchar(200) not null

)

alter table Team add

constraint PK_Team primary key (teamid),

constraint AK_Team unique (teamid)

Every time an athlete changes a team, a new record is created for the record. The previous record gets end dated and the IsRowCurrent flag gets properly set. In your cube, you can use these two tables in a single dimension or do two dimensions and create a referenced relationship.

If an athlete could belong to multiple teams simultaneously, you would then have a many-to-many relationship. In this scenario, you would have Athlete and Team dimension tables. The Athlete table would not have a reference to the Team dimension table. Instead, you would have a AthleteTeam table which contains a reference to the Athlete dimension table and another to the Team dimension table. This is your bridge table concept.

Let's say a single record in the fact table pointed to multiple athletes, such as athletes involved in a play (think baseball). This would be the group table you mentioned. You would have an Athlete table like described above that points to Team. You would build a table that assigns a single group ID to the list of players involved, and a Group table that represents that list. The fact table would point to the Group table.

create table AthleteGroup (

AthleteGroupID int not null identity(1,1),

NumberOfAthletes int not null

)

alter table AthleteGroup add constraint PK_AthleteGroup primary key (athletegroupid)

create table AthleteGroupAthlete (

AthleteGroupID int not null,

AthleteID int not null

)

alter table AthleteGroupAthlete add

constraint PK_AthleteGroupAthlete primary key (AthleteGroupID, AthleteID),

constraint FK_AthleteGroupAthlete_AthleteGroupID foreign key (athletegroupid) references AthleteGroup (AthleteGroupID),

constraint FK_AthleteGroupAthlete_AthleteID foreign key (athleteid) references Athlete (AthleteID)

The AthleteGroupAthlete table would then be used to create a measure group with a single measure (using a COUNT aggregation). This becomes the intermediate measure group in the many-to-many relationship.

Good luck,
Bryan

|||

Hi Bryan,

Thanks for the reply.

My requirement is for an athlete belonging to multiple teams simultaneously. And the way you have explained in the second part, would have been the right way to move ahead.

But currently, the way it is implemented is as explained my first post. Is there a way to make it work with some settings in Analysis Services Solution? As said earlier the concept of the grouping works, when we work on the relational database based data warehouse.

Thanks,

Vivek C.

|||

Sorry for not replying sooner. For some reason I'm not getting alerts regularly.

Regarding the Athelete dimension, I'm afraid I don't see a way to implement it the way you describe. Is changing the model an option for you?

Thanks,
Bryan

|||

Changing the data model would be difficult, as we have already done with the ETL work and certain set of reports.

Anyway thanks for the inputs, let me see whether I can talk others into changing the model.

Thanks,

Vivek

|||The simplest representation seems to be a fact table reperesenting membership in a team and then a Team dimension, an Athlete dimension, and a Time dimension (with a granularity of year). Then the fact table simply contains a row for each team an athlete is on for each year the athelete is on that team. Then your measure group can contain a single measure that is a count of rows which you can filter based on any combination of team, athlete, or year. If I recall correctly, Kimball calls this type of design a "factless fact table".|||

Hi Matt,

Thanks for the inputs. The reason for not implementing using the factless fact (this was our first option), was data explosion.

Consider the case where the Athlete can belong to different team simultaneously and this changing every quarter. In this case the number of records would keep increasing periodically (quarterly). So we thought we would go the grouping of athlete teams.

Though the grouping option works with the data warehouse, we are not able to get it work properly using SSAS.

My guess is that SSAS handles many to many relationships using the factless fact table.

Thanks,

Vivek

|||

You'd be surprised at how many records you can handle if you keep them smalll by using int or smallint for your foreign keys. Assuming 3 int foreign keys, each row would cost you 3*4=12 Bytes without any indexes and SSAS doesn't need indexes. 1000 athlethes on an average of 4 teams over 100 years at the quarter level granularity would need 1000*4*100*4=19,2000,000 rows. This sounds like a lot, but at 12 bytes per row this is only 18.3MB which is tiny.

I must admint, I don't fully understand the design you originally described. It sounds like two dimensions and one fact table with no many-to-many relationships in AS terms (that is, both dimensions are regular dimensions for the measure group.) Based on this, I suspect the problem you are currently seeing is caused by dimension keys not being unique for the Athlete Team table since Team Code will occur multiple times in the Ahtlete Team table. I think what you may want to do in this design is to break out Group Key from the Ahtlete Team table and create a GroupToTeam table contain Group Key and Team code. This table would be a fact table in AS which would then be used to create a many-to-many relationship from your other Fact table to the Athlete Team table.

|||

Matt,

The way you have explanied of breaking the Group Key from the Athelete Team table/dimension is the best thing to do.

Anyway, we have gone ahead and changed our data model to the way exactly SSAS handles, viz. by factless fact tables.

Thanks for the inputs.

Vivek

Many to many relationships

Hi,

I have a many-to-many relationship scenario and I had used a group dimension instead of the bridge table concept.

I had referred "Data Warehouse Lifecycle Toolkit" by Kimball and others.

For ex:
An Athlete belonging to multiple athletic teams and with the possiblity of changing every year.

So I had created an Athlete Team dimension, with a group key and the team code.

Athelete Group Key,
Athelete Team Code,
Athelete Team Name

with Athlete Group Key and Athlete Team Code as the key for the table.

And in the fact/measure group, referring to the Group Key.

Now, in SSAS, I had created the Athlete Team dimension with Group Key as the key atrribute and others as dimension attributeS.

***********************
Athlete dimension table
--

Group Key, Athlete Code, Athlete Name,
1 , FTB , Football
1 , TEN , Tennis
2 , TEN , Tennis
2 , VOL , Volleyball

Fact/Measure group

Athlete ID, Year, Group Key
123 , 2001, 1
234 , 2001, 2

************************

When processed and browsed the cube, for the number of students for each type of team, the result is considering only the first team for the count (it is ordered alphabetically).

In the sample data example above, the count of athletes for team Football, Tennis and Volleyball are 1, 2 and 1 respectively.

But in SSAS, the count of athletes for the team Football, Tennis and Volleyball are 1, 1 and 0 respectively.

For the same requirement, the SQL query on the SQLDB, is fetching me the correct result, but this is required for adhoc reporting as well and hence have to be done using the cube itself.

Comments on this would be really helpful.

Thanks,
Vivek C.

Vivek,

I might just be getting stuck on the example, but this sounds like a Type 2 changing dimension and not a many-to-many scenario.

So, you have this Athlete dimension. It's primary key is AthleteID and contains a reference to the TeamID. Here is the structure of the table:

create table Athlete (

AthleteID int not null identity(1,1),

Name varchar(200) not null,

TeamID int not null,

RowStartDate datetime not null default (getdate()),

RowEndDate datetime null,

IsRowCurrent bit not null default (1)

)

alter table Athlete add

constraint PK_Athlete primary key (athleteid),

constraint AK_Athlete unique (name, rowstartdate),

constraint FK_Athlete_TeamID foreign key (teamid) references Team (TeamID)

create table Team (

TeamID int not null identity(1,1),

Name varchar(200) not null

)

alter table Team add

constraint PK_Team primary key (teamid),

constraint AK_Team unique (teamid)

Every time an athlete changes a team, a new record is created for the record. The previous record gets end dated and the IsRowCurrent flag gets properly set. In your cube, you can use these two tables in a single dimension or do two dimensions and create a referenced relationship.

If an athlete could belong to multiple teams simultaneously, you would then have a many-to-many relationship. In this scenario, you would have Athlete and Team dimension tables. The Athlete table would not have a reference to the Team dimension table. Instead, you would have a AthleteTeam table which contains a reference to the Athlete dimension table and another to the Team dimension table. This is your bridge table concept.

Let's say a single record in the fact table pointed to multiple athletes, such as athletes involved in a play (think baseball). This would be the group table you mentioned. You would have an Athlete table like described above that points to Team. You would build a table that assigns a single group ID to the list of players involved, and a Group table that represents that list. The fact table would point to the Group table.

create table AthleteGroup (

AthleteGroupID int not null identity(1,1),

NumberOfAthletes int not null

)

alter table AthleteGroup add constraint PK_AthleteGroup primary key (athletegroupid)

create table AthleteGroupAthlete (

AthleteGroupID int not null,

AthleteID int not null

)

alter table AthleteGroupAthlete add

constraint PK_AthleteGroupAthlete primary key (AthleteGroupID, AthleteID),

constraint FK_AthleteGroupAthlete_AthleteGroupID foreign key (athletegroupid) references AthleteGroup (AthleteGroupID),

constraint FK_AthleteGroupAthlete_AthleteID foreign key (athleteid) references Athlete (AthleteID)

The AthleteGroupAthlete table would then be used to create a measure group with a single measure (using a COUNT aggregation). This becomes the intermediate measure group in the many-to-many relationship.

Good luck,
Bryan

|||

Hi Bryan,

Thanks for the reply.

My requirement is for an athlete belonging to multiple teams simultaneously. And the way you have explained in the second part, would have been the right way to move ahead.

But currently, the way it is implemented is as explained my first post. Is there a way to make it work with some settings in Analysis Services Solution? As said earlier the concept of the grouping works, when we work on the relational database based data warehouse.

Thanks,

Vivek C.

|||

Sorry for not replying sooner. For some reason I'm not getting alerts regularly.

Regarding the Athelete dimension, I'm afraid I don't see a way to implement it the way you describe. Is changing the model an option for you?

Thanks,
Bryan

|||

Changing the data model would be difficult, as we have already done with the ETL work and certain set of reports.

Anyway thanks for the inputs, let me see whether I can talk others into changing the model.

Thanks,

Vivek

|||The simplest representation seems to be a fact table reperesenting membership in a team and then a Team dimension, an Athlete dimension, and a Time dimension (with a granularity of year). Then the fact table simply contains a row for each team an athlete is on for each year the athelete is on that team. Then your measure group can contain a single measure that is a count of rows which you can filter based on any combination of team, athlete, or year. If I recall correctly, Kimball calls this type of design a "factless fact table".|||

Hi Matt,

Thanks for the inputs. The reason for not implementing using the factless fact (this was our first option), was data explosion.

Consider the case where the Athlete can belong to different team simultaneously and this changing every quarter. In this case the number of records would keep increasing periodically (quarterly). So we thought we would go the grouping of athlete teams.

Though the grouping option works with the data warehouse, we are not able to get it work properly using SSAS.

My guess is that SSAS handles many to many relationships using the factless fact table.

Thanks,

Vivek

|||

You'd be surprised at how many records you can handle if you keep them smalll by using int or smallint for your foreign keys. Assuming 3 int foreign keys, each row would cost you 3*4=12 Bytes without any indexes and SSAS doesn't need indexes. 1000 athlethes on an average of 4 teams over 100 years at the quarter level granularity would need 1000*4*100*4=19,2000,000 rows. This sounds like a lot, but at 12 bytes per row this is only 18.3MB which is tiny.

I must admint, I don't fully understand the design you originally described. It sounds like two dimensions and one fact table with no many-to-many relationships in AS terms (that is, both dimensions are regular dimensions for the measure group.) Based on this, I suspect the problem you are currently seeing is caused by dimension keys not being unique for the Athlete Team table since Team Code will occur multiple times in the Ahtlete Team table. I think what you may want to do in this design is to break out Group Key from the Ahtlete Team table and create a GroupToTeam table contain Group Key and Team code. This table would be a fact table in AS which would then be used to create a many-to-many relationship from your other Fact table to the Athlete Team table.

|||

Matt,

The way you have explanied of breaking the Group Key from the Athelete Team table/dimension is the best thing to do.

Anyway, we have gone ahead and changed our data model to the way exactly SSAS handles, viz. by factless fact tables.

Thanks for the inputs.

Vivek

Many to many relationship in MSAS

Hi,

I ve got a fact table called project_fact and a dimension table called technology_dim.

project_fact:

proj_id time_id tech_id revenue cost

technology_dim:

tech_id tech_name

Now there is a many to many relation between fact and dimension table. one project can be done on many technologies. How to resolve this, do i need to create a third table?

Please tell me in details...

Thanks in advance,

Siddharth Rai.

Create a table that represents the combination of technologies exploited by your project. Let's call this the TechGroup table:

create table TechGroup (

TechGroupID int not null identity(1,1)

)

alter table TechGroup add

constraint PK_TechGroup primary key (techgroupid)

Create another tables that relates the TechGroup to the Technologies that comprise it:

create table TechGroupTechnologyBridge (

TechGroupID int not null,

TechnologyID int not null

)

alter table TechGroupTechnologyBridge add

constraint PK_TechGroupTechnologyBridge primary key (techgroupid, technologyid),

constraint FK_TechGroupTechnologyBridge_TechGroupID foreign key (techgroupid) references TechGroup (techgroupid),

constraint FK_TechGroupTechnologyBridge_TechnologyID foreign key (technologyid) references Technology (technologyid)

In your fact table, reference the TechGroup table.

When you set up the cube, create a TechGroup dimension. Add the TechGroupTechnologyBridge as a measure group with a single measure (using COUNT). Make the measure not visible (which will hide the bridge measure group).

In your dimension usage tab, set the relationships between TechGroupTechnologyBridge measure group and TechGroup and Technology dimensions. Relate your fact table measure group to the TechGroup dimension as well.

Now, set a many-to-many relationship between your fact table and the Technology dimension using the TechGroupTechnologyBridge measure group as the intermediate measure group.

You will probably want to make the TechGroup dimension not visible within your cube.

Good luck,
Bryan

|||

Hey thanks Bryan...sql

Many to many relationship in MSAS

Hi,

I ve got a fact table called project_fact and a dimension table called technology_dim.

project_fact:

proj_id time_id tech_id revenue cost

technology_dim:

tech_id tech_name

Now there is a many to many relation between fact and dimension table. one project can be done on many technologies. How to resolve this, do i need to create a third table?

Please tell me in details...

Thanks in advance,

Siddharth Rai.

Create a table that represents the combination of technologies exploited by your project. Let's call this the TechGroup table:

create table TechGroup (

TechGroupID int not null identity(1,1)

)

alter table TechGroup add

constraint PK_TechGroup primary key (techgroupid)

Create another tables that relates the TechGroup to the Technologies that comprise it:

create table TechGroupTechnologyBridge (

TechGroupID int not null,

TechnologyID int not null

)

alter table TechGroupTechnologyBridge add

constraint PK_TechGroupTechnologyBridge primary key (techgroupid, technologyid),

constraint FK_TechGroupTechnologyBridge_TechGroupID foreign key (techgroupid) references TechGroup (techgroupid),

constraint FK_TechGroupTechnologyBridge_TechnologyID foreign key (technologyid) references Technology (technologyid)

In your fact table, reference the TechGroup table.

When you set up the cube, create a TechGroup dimension. Add the TechGroupTechnologyBridge as a measure group with a single measure (using COUNT). Make the measure not visible (which will hide the bridge measure group).

In your dimension usage tab, set the relationships between TechGroupTechnologyBridge measure group and TechGroup and Technology dimensions. Relate your fact table measure group to the TechGroup dimension as well.

Now, set a many-to-many relationship between your fact table and the Technology dimension using the TechGroupTechnologyBridge measure group as the intermediate measure group.

You will probably want to make the TechGroup dimension not visible within your cube.

Good luck,
Bryan

|||Hey thanks Bryan...

Many to many or not ?

Hello I have a question regarding many to many diemensions.

I have a fact table with the following structure :

- fact_id

- act_cat

- amount

The dimension table 'PL structure' has the following structure :

- pl id

- report_view

- act_cat

- name

One line from the fact table relates to two lines from the dimension table.

Example:

the act_cat 'Holiday' frol the fact table relates to the act_cat 'Holiday' in the dimension table. But in the dimension table the act_cat 'Holdiay' exists two times, one time with report_view 'View 1' and one time with report_view 'View 2'

The report_view will become a parameter on the reports. So the end-user must always select one report_view.

Now is the question. how can I solve this problem in Analysis Services. Can I do this only with many to many dimensions, or is there another solution.

The name "Report_view" suggest me that you may face this scenario in a different way, but assuming that you really have to handle this situation, the many-to-many approach should be working (below I suggest you how).

But please let me explain one thing: it is always strange when you have a dimension (PL structure) that has an ID (pl id) that is not referenced into the fact table. You don't have the star schema, and when your relational model is not star-schema based, you always have some hidden issue that was not solved in the relationa design.

Anyway, if for whatever reason you model is the best one (or is the only you can use...) then this is a possible solution.

You have to define one named query (or a view) that I name Factless_ActCat_PL_Id:

SELECT pl_id, act_cat FROM [PL Structure]

Then you have to define another named query (or view) that I name Dim_Act_Cat:

SELECT DISTINCT act_cat FROM [PL Structure]

At this point you have two fact tables and two dimensions.

You define the Data Source View with these 4 tables/views and you have to define this logical primary key by hand if the wizard doesn't find them:

act_cat must be the primary key for Dim_Act_Cat.

Then you define 2 dimension (PL structure and Dim_Act_Cat) and two measure groups (original fact table and Factless_Act_Cat_PL_Id). Your fact table has a regular relationship with Dim_Act_Cat and a many-to-many relationship with PL Structure - to build that, the Factles_Act_Cat_PL_Id measure group must have two regular relationships with PL structure and Dim_Act_Cat dimensions.

Please read my paper on many-to-many dimensions if you are in trouble with these concepts:
http://www.sqlbi.eu/manytomany.aspx

Let me know if it works as you expected.

Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi

Many to many or not ?

Hello I have a question regarding many to many diemensions.

I have a fact table with the following structure :

- fact_id

- act_cat

- amount

The dimension table 'PL structure' has the following structure :

- pl id

- report_view

- act_cat

- name

One line from the fact table relates to two lines from the dimension table.

Example:

the act_cat 'Holiday' frol the fact table relates to the act_cat 'Holiday' in the dimension table. But in the dimension table the act_cat 'Holdiay' exists two times, one time with report_view 'View 1' and one time with report_view 'View 2'

The report_view will become a parameter on the reports. So the end-user must always select one report_view.

Now is the question. how can I solve this problem in Analysis Services. Can I do this only with many to many dimensions, or is there another solution.

The name "Report_view" suggest me that you may face this scenario in a different way, but assuming that you really have to handle this situation, the many-to-many approach should be working (below I suggest you how).

But please let me explain one thing: it is always strange when you have a dimension (PL structure) that has an ID (pl id) that is not referenced into the fact table. You don't have the star schema, and when your relational model is not star-schema based, you always have some hidden issue that was not solved in the relationa design.

Anyway, if for whatever reason you model is the best one (or is the only you can use...) then this is a possible solution.

You have to define one named query (or a view) that I name Factless_ActCat_PL_Id:

SELECT pl_id, act_cat FROM [PL Structure]

Then you have to define another named query (or view) that I name Dim_Act_Cat:

SELECT DISTINCT act_cat FROM [PL Structure]

At this point you have two fact tables and two dimensions.

You define the Data Source View with these 4 tables/views and you have to define this logical primary key by hand if the wizard doesn't find them:

act_cat must be the primary key for Dim_Act_Cat.

Then you define 2 dimension (PL structure and Dim_Act_Cat) and two measure groups (original fact table and Factless_Act_Cat_PL_Id). Your fact table has a regular relationship with Dim_Act_Cat and a many-to-many relationship with PL Structure - to build that, the Factles_Act_Cat_PL_Id measure group must have two regular relationships with PL structure and Dim_Act_Cat dimensions.

Please read my paper on many-to-many dimensions if you are in trouble with these concepts:
http://www.sqlbi.eu/manytomany.aspx

Let me know if it works as you expected.

Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi

many to many dimension

Hi all,

Is there a way to create a dimension property based on fact table used to connect a dimension to another measure group. I found a example where such an attibute is used for a measure expression(http://msdn2.microsoft.com/en-us/library/ms345139.aspx) but I really need a property that I could display to the end user.

thx

Fred

Hi again...just to be a bit more clear on what I'm trying to do...if you look at the example I mentioned....how could the value of the 'ownership' be displayed instead of used in a calculation?

Fred

|||Well, in this example, the user could browse the value of the 'ownership' measure in the intermediate measure group, for a given selection of Store and Organization. If that's not adequate, you could define a fact dimension for the intermediate measure group fact table, and add 'ownership' as an attribute (with the appropriate type).|||

Thanks Deepak! The fact dimension will do the trick

Fred

Managing Hierarchies in Dimension Tables

I am currently looking at the capabilities in SSIS from the point of view of an ETL developer who has worked with other products eg. Informatica, Cognos DecisionStream and one of things I note is a lack of support for dimensional hierarchies.

It appears that MS have assumed that SSIS users will automatically use SSAS.

We use Hyperion Essbase. Other sites have Cognos or Business Objects for their OLAP/BI.

I would like to be able build multi-level dimension hierarchies directly from within SSIS.

Has MS considered this for future versions?

We don't have any support for building hierarchies in these products simply becuase we do not connect directly to their metadata. However, it is possible to load hierarchies in SSIS - although again without direct metadata support.

Could you outline some features that you think would be useful? Or perhaps some issues you are currently facing in loading hierarchies?

Thanks

Donald Farmer

|||

Hi Donald,

Many enterprises are moving towards the concept of Master Data Management. This is the function of maintaining the reporting dimensions externally to any DW/BI system. There are specialised tools to do this but it can be managed in spreadsheets and fed into a dimension repository. This may be as simple as a few tables which store the attributes and parent/child relationships. The data in this repository has been validated.

As an ETL developer, when I wish to build dimensional tables, I can use the Master Data to source the hierarchies etc and then build the appropriate dimensional tables for either a star or snowflake schema.

Cognos 8 Data Integration (formerly the DecisionStream ETL tool) allows a developer to define a hierarchy. The GUI allows them to select the parent, child, description, and Top Parent Node of the hierarchy. It then generates a table containing chiild, description, parent, and level. From this, I can build a star schema dimension. One of the best features is that it checks the hierarchy for errors. The Developer can also view the hierarchy tree.

With SSIS, and I am a relative newbie, the only way to do the above is to too build a dimension in SSAS and then call that component from SSIS.