.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

Aggregation in many to many dimension -Fact relationship case

Posted By:      Posted Date: December 04, 2010    Points: 0   Category :Sql Server

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 ?


View Complete Post

More Related Resource Links

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?

No Aggregation with parent-child dimension



I am trying to build a very simple datacube without dynamic aggregations. I only have one fact table and one dimension table. The dimension is a parent child dimension. In the fact table I have a value for each leaf or non-leaf from the dimension (except of one where I wanted to test if it shows null then). I first built the dimension which worked fine and then the cube with the only one measure with "aggregate function" = None. But when I processed the cube and went into the Browser tab and used the dimension I was not able to the see the hierarchy. It only says "Level 2" and "Grand total" and when I double click it I get an error message "Cannot drill into a total member". I went through some posts here where it was said that the dimension cannot have "MemberWithData" = NonLeafDataHidden which I had, so I changed it to visible. I also read that there cannot be a "Calculate" calculation when using no aggretion so I deleted that. Nothing changed the fact that I did not have the hierarchy in the dimension and didnt see any values from my fact table.

I think I need to something with the Calculation but i dont know what. I would be much appreciated if you could help me.

Thanks for your help,


Will processUpdate of a dimension invalidate all paritions' aggregation even though some partitions


I have measure groups partitions by year. I have fact tables and dimension tables updated every day and only current year's fact data and related dimension members will be updated(insert/update/delete dimension members). For measure group partition, I will only update the current year partition. However, if I use processUpdate to process the dimension, will the aggregates on previous years' partitions be invalidated? Actually, the dimension update does not change data in previous years' partitions. Will the aggregates in these partitions be invalidated or not?

Thanks a lot.

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.


Process Update does not reflect change in case of dimension member in SSAS 2008.


Process Update does not reflect change in case of dimension member in SSAS 2008. We can replicated this issue in Adventure Works 2008 with below steps.


1.  Customer Name Aaron Allen is in Title case both in Adventure Works SQL 2008 Data Mart as well as in SSAS 2008.


2.  Change Customer Name Aaron Allen to Upper case in Adventure Works SQL Data Mart.


Need help on dimension relationship


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

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).

Visual Case Tool - UML Tutorial

The Class Diagram

The class diagram is core to object-oriented design. It describes the types of objects in the system and the static relationships between them.

Use Case Diagram

The use case diagram shows the functionality of the system from an outside-in viewpoint.

Actors (stick men) are anything outside the system that interacts with the system.

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?



Hello, i need help changing the following code into a switch...case statementUndecided

<script runat="server">
void Page_Load()
    if (Page.IsPostBack)
    public String toSring()
    int intcomputerChoice=1;
       if (Page.IsPostBack)

Editor's Note: In Case You Hadn't Noticed .


Find out what's new for MSDN Magazine, including a print redesign and the introduction of virtual labs on our web site so you can experiment with our code.

Howard Dierking

MSDN Magazine June 2008

Inside MSDN: Building the MSDN Aggregation System


Have you visited msdn2. microsoft. com? It's the new online face of the MSDN® Developer Tools and Enterprise Server documentation. The infrastructure behind it includes a system developed by my team at Microsoft for aggregating information related to our content.

John Mollman

MSDN Magazine July 2006

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

.NET Migration Case Study: Using ASP.NET to Build the beta.visualstudio.net Web Site


When the Web site used to collect customer feedback about the Visual Studio .NET beta needed an update, the Visual Studio team saw a good opportunity to implement, deploy, and showcase a real-world site using .NET technologies. This article covers the migration of the Web site, beta.visualstudio.net, from components written in Visual Basic 6.0 and ASP hosted on Microsoft Internet Information Services (IIS) 5.0 to ASP.NET and the .NET Framework. The choices of technologies to be incorporated, along with issues such as validation, security, and authentication, are all discussed. Both existing components and the new components written in JScript and ASP.NET that were used to migrate the site are also explained.

Jay Schmelzer

MSDN Magazine September 2001

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