.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Need help on dimension relationship

Posted By:      Posted Date: April 14, 2011    Points: 0   Category :

Hi All,

I need to some help in desiging the dimensions for the cube. Here is the situation

We have a Region attibute (RegionCode and RegionName), Office (OfficeCode and OfficeName), Organization (OrganizationID and OrganizationName), OrgCategory(CategoryCode and CategoryDesc)

A region has multiple offices under it. A offices has multiple organizations under it. And a organization falls under one of the Org Category (There are two types of Org Categoty defined).

Here is an example

Region - Central

Office - CHI, DAL

Organizations under CHI - CHISALES, CHIENG

CHISALES comes under Org Category SALES and CHIENG comes under ENG category.

Please let me know how we showuld design the cube structure on this.

1. Do we need separate dimensions for Region, Office, Organization and Org Category?

2. OR we can have one Region Dimension and other tables (Office, Organization, Org Category) should be linked with it? and create a hierarchy.

Please let me know your thoughts.



Thanks, Gaurav

View Complete Post

More Related Resource Links

Relationship between a dimension - two fact tables

I have 3 fact tables and about 10 dimensions. I want to relate both "Fact_Pop1" and "Fact_pop2" to Dim_AgeGroup. But Fact_pop1 has single age group and Fact_pop2 has no single age group (5 years age group). I spent about a day on this but I could't solve it.   Fact_Pop1 Age        Race_key            Gender_key      Geography_key              Pop_size 10           White                    M                                            CA                          10000    10           White                    F        

Parent-Child relationship on a Type 2 (SCD) Dimension

For a Type 2 slowly changing dimension (SCD) such as Employee with a Parent-Child relationship, how do you handle the explosion of new rows when a high ranking employee has an attribute change?   It will require terminating the high ranking employee row and adding a new current row with a new surrogate key. Then every employee row below this employee will also have to be terminated and a new current row added with the new surrogate key of the parent.   This will have to be performed 1 level at a time so the subordinate rows will have the new surrogate key of their immediate parent.   For a large organization such as 20,000 employees, this will result in a dimension with hundreds of thousands rows in a relatively short time.   Is there a better way to model this?

Creating Local cube on Cube using Many-to-Many dimension relationship


Hi All,

I have got a cube that uses many-to-many dimensions relationship. I also have a calculated measure (A*B) in the measure group using a unary operator.

I tried creating the local cube by using CREATE GLOBAL CUBE statement and included the intermediate measure group and one measure from the group. I have also included all the dimensions that are linked to the intermediate measure group.

The local cube is being created but I don't see the correct values of the measures. I just get the value of A coming for the value of the calculated measures. There are also some performance issues while browsing the local cube which I assumed should be very fast.

I am not sure if this is a known issue and there's a solution available for this but I tried and didn't find anything on the forum. 

Please help me with this. Thanks in Advance.


PK to PK relationship between Fact and Dimension tables


Hi guys,

Is it possible to define a relationship between a Fact Table and Dimension Table using a PK to PK relationship rather than a PK FK relationship?

The reason I ask is that I'm working on building a cube, in which the tables I think I need to use as dimension tables only have matching Primary Keys in the current RDBMS, not a FK.

Is there any reason why I shouldn't use a PK to PK relationship?


One-to-many relationship between Fact and Dimension - Help in Modelling


Hy from Portugal,

Well, here's my case to see if you can help me:
We are trying to build a DW in our Healthcare institution, however we are facing a challenge and we are not solving it using many-to-many dimension ( The technology is Microsoft).
Relational tables:
Fact Patient Hospitalization -  Each patient hospitalization generates an EPISODE of hospitalization (EPISODE_ID) WHICH IS UNIQUE ....in the relational fact table we have the EPISODE ID COLUMN and also a TOTAL_EPISODES column wich is always equal to "1" because, after dimensional modelling, we will have a measure that is "sum of episodes".
EPISODE_ID is a foreign key to the EPISODE_ID field of Patient Hospitalization Dimension 
Patient Hospitalization Dimensio
-> EPISODE_ID (primary key)
-> DESCRIPTION ( day or night - describes if the episode ocurred during the day or during the night)
Fact Patient Claims (this fact will be the basis for the measure "sum of claims")
This table has the fields
 -> CLAIM_ID ( it is always unique (p

How to specify ForeignKeyName for the relationship between fact table and dimension table?


The ForeignKeyName attribtute of a relationship is greyed and empty when I edit a relationship of a data source view in SQL Server 2008 BIDS.  How can I edit it?

Aggregation in many to many dimension -Fact relationship case


I have a Dimension A and Two fact Table F1,F2. Dimension A has many to many relationship with  fact table F1 Through intermediate Fact table F2.I want to have aggregation on Dimension A for Fact A 's related Measure .

How should I design  aggregation in such case ?


An Entity Relationship Diagram Example

An Entity Relationship Diagram conveys a lot of information with a very concise notation. The important part to keep in mind is to limit what you're reading using the following technique:

Choose two entities (e.g. Company and Employee)
Pick one that you're interested in (e.g. how a single Company relates to employees)
Read the notation on the second entity (e.g. the crow's feet with the O above it next to the Employee entity).

Good way to do a Many-to-Many relationship?


I've used the automatic  Many-to-Many generation as described here: http://msdn.microsoft.com/en-us/library/dd742359.aspx

But that doesn't seem to allow a quantity to be attached to orders (in the example it would be courses). I have many customers and many products, I would like a customer to be able to order more than one product. Right now I get a grid of checkboxes. I would like instead a field where I could enter a number. 

Is this possible?

Command Management: Use Design Patterns to Simplify the Relationship Between Menus and Form Elements


In Windows Forms applications, similar commands, such as those in a menu and their counterparts on a toolbar, are not automatically related. They don't fire the same event or run the same handler routine. Yet code that allows the same or similar user commands to fire the same code simplifies development.This article describes the principles of command management and why it's important to have functional commands that are not exclusive to any one UI element. In order to provide an MFC-like command management infrastructure, a design pattern is developed and applied as a series of C# classes.

Michael Foster and Gilberto Araya

MSDN Magazine October 2002

Time Dimension Enhancement with Business intelligence Issue

Hi all, I want to add a year over year growth using the BI wizard (Time diemsion enhancement) but when I try to add this enhancement via the wizard then this last one has the button next disabled with a waning that says   A time dimension is required to enable this functionality. Ensure that you have a dimension of type Time, that contains at least one hierarchy with a level flagged as a time period. Inspite of the fact that I added that time dimsension with one hierarchy Time hierarchy Calendar Year Calendar Semester Calendar Quarter Time Key(With namecolumn defined as a named calculation that repsents the day with this format  yyyy, dd mm ) Me personaly I have a doubt about the last condition of the warning (with a level flagged as a time period) but I dont know exactly 1. If my doubt is right 2. What shoud I do to enhance the cube in this context using the time dimension enhancement The complexity resides in the simplicity

display non measure, non dimension fields in drill down

Hi All, I created my fact table with more fields than just the foreign keys linking dimensions and the field(s) to be used as mesures.  I did this hoping that on drill down I would be able to see the extra fields so that the user would have access to detail information on the records making up the measure amount.  The extra fields do not appear on drill down.  How can I make them appear, or am I on the wrong track? Thanks for any help

Dynamic Dimension with Aggregate Values

Hi, I have an specific requirement to make the measure value as an dimension. Let me explain my problem in brief. I have a fact table with dimensions like Time, Products etc and having single fact table with two measures. I have to create a calculated measure which shows the average of Measure 1 (here used calculated measure because there are couple of other calculations involved). And other two calculated measures. when I drill down with Products dimension for Calculate measure 1, it shows the average value for each products. Now, I want this calculated measure values (includes Product dimesnion drill down) as a Dimension and based on this value, I need to show the value of other two measures. For example: when the dimension products is used for drill down the values displayed will be like this and in this I need CM1 to be another dimension Products CM1 CM2 CM3 P1 0.10% 20 1 P2 0.20% 40 2 P3 0.30% 80 3 P4 0.40% 70 4 P5 0.50% 30 5 P6 0.60% 110 6 P7 0.70% 120 7 P8 0.80% 130 8 P9 0.90% 86 9 P10 1.00% 65 10 when CM1 is used as a dimension it should show the value like this CM1 CM2 CM3 0.10% 20 1 0.20% 40 2 0.30% 80 3 0.40% 70 4 0.50% 30 5 0.60% 110 6 0.70% 120 7 0.80% 130 8 0.90% 86 9 1.00% 65 10 How can we create the dynamic dimension with the aggregated values? Any assistance will be greatly apprec

Create a dimension based on 2 fields

Hello I have a table as follow: No   Placestart    Placeend 1      DK                USA 2      UK                USA 3      USA              DK Now, I want a dimension called Country, which selects either of the rows where a value exist In SQL it would be ex (SELECT * FROM table WHERE placestart = 'USA' OR placeend = 'USA) So, when I select USA in the dimension all 3 rows are listed, as USA is included either in placestart or placeend. If I select DK row 1 and 3 is selected etc... Is this somehow possible?      

Other group with VisualTotals and dynamic dimension

I All, I’m trying to create a query that dynamically display a dimension attribute on rows. I want display only first N member of that dimension attribute and aggregate the others in ‘Others’ member   (this query should be used in Reportig services and data displayed in both Crosstab and Pie Chart). So I’ve 2 parameters:   @TopN: Value: 5 @RowDimension:  Value: [Product].[Subcategory]   And the code look like this:   WITH SET [DynamicDimension] as strtoSet(@RowDimension + '.MEMBERS') MEMBER [Measures].[RowCaption] as strtoMember(@RowDimension).MEMBER_CAPTION   SET [TopItem] as TopCount(DynamicDimension,@TopN,[Measures].[Order Count]) SET [BottomItem] as {[DynamicDimension]} - {[TopItem]}   MEMBER [Product].[Subcategory].[Other ] as aggregate(BottomItem)   SELECT {[Measures].[Order Count]} ON COLUMNS , Non Empty {      [TopItem] ,[Product].[Subcategory].[Other]     } ON ROWS FROM [Adventure Works]   Unfortunately this solution doesn’t work when I change the value of parameter @RowDimension (ex with [Customer].[Customer Name]). This is because calculated member “ [Product].[Subcategory].[Other]” is related to Product dimension and is not possible parametrize the name of a member. Any suggestions?   The alternative solution that I&r

MDX Query - Get (Parent-Child) Dimension member regarding another Parent-Child Dimension, then, get

Hi there, I got the following MDX issue. My cube structure looks like that : DIMENSION CATEGORY (Parent-Child) --> (Many to many) FCTLESS_CategoryNode <-- DIMENSION NODE (Parent-Child) ___ DIMENSION NODE (Parent-Child) --> (Many to many) FCTLESS_NodeVariable <-- DIMENSION VARIABLE ___ DIMENSION VARIABLE <-- FACT (Supposed with only one value called VALUE) I'd like aggregate for N nodes linked to a specific category. For example, with the next content : DIMENSION CATEGORY - COUNTRY - SITE - BUILDING DIMENSION NODE US (Category Country)     - Site US_A (Category Site)     - Building US_A_A  (Category Building) FR (Category Country)     - Site FR_A (Category Site)     - Building FR_A_A  (Category Building)                                       - Site FR_A (Category Site)     - Building FR_A_B  (Category Building)                                       - Site FR_B (Category Site) 

How to return the results of a 1 to Many Relationship

A co-worker of mine just came back from a SQL Server Reporting Services class (we are using SQL Server 2005). In that class they were told that if they had a one to many relationship that they would have to flatten the results into one row of the resultset in order to show the 'many' portion of the relationship.  For example, my company has two tables: Jobs Notes/Memo's For each Job we can have 0 or more Notes/Memo's. According to my co-worker the Report Server must have each of the Notes/Memo's in one row of the resultset.  That we therefore have to flatten the list of Notes into Note1, Note2, Note3...etc fields.  Is this correct?  How would I handle a situation where I didn't know how many Notes there were? Thanks so much.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend