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

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

Dimension Design Question

Posted By:      Posted Date: October 25, 2010    Points: 0   Category :Sql Server

Hello All,

a quick question on dimension design...
We have a project detail page a asp.net based web page where admins enter the project details
There is a section in this page for Project Team where we can assign users to different role (like Project manager, Partner etc..)
There can me more than one user for the same role.

Right now we have total of 10 roles defined in the system.

Now the design question is while doing the dimension design do we need to create ten different dimensions? like DimPM, DimPart etc...
or there is any other way to do this by just creating one single dimension.

Basically from the cube we should be able to see who are the PM's, PART's etc for a project

I have two approaches right now in my mind...
First Approach
a) Need ten dimensions for ten roles ex DimPM(PMKey, PMUserID, PMUserName). DimPart etc...
b) Create a Factless table with columns (FctLesskey, ProjectKey, PMKey, PRATKEY etc...)
c) Fact table - ProjectKey, FctLessKey, Amount etc...

Second approach -

a) Create a Role dimension (fill all the roles) and will have columns RoleKey, RoleName
b) Assuming there is a User dimension (Userkey, UserName etc...)
c) FactLess table - FactLessKey, UserKey, RoleKey
d) Fact Table - ProjectKey, FactLessKey, Amount etc...

Please let me which

View Complete Post

More Related Resource Links

Design question for large fact dimension/many to many dimension


I have a pretty typical orders fact table that is aggregated to 1 line per order.

Each order may have between 0-x errors

I implemented this as a many to many dimension with one field in my intermediate fact table being the error number and the other field being the order id

this requires that i have a fact dimension on the orderid(int32) column.

Right now there are about 10m orders per month, putting my annual count around 120m which doesn't leave a lot of room for growth to stay under the ~150m key member (4GB) size limit for a dimension in SSAS.

I wanted to see if anyone had any thoughts on this. I have found 2 different strategies that might work, including using ROLAP For the order dimension or breaking the order dimension into 3 or 4 separate dimensions discussed here



database interface design question


For handy database interface, is it good to use SqldataSources exclusively intead of EntLib functions?

Thanks for any feedback.

I need a workflow triggered by a workflow...how do I design this? Beginner question

I've added the Approval workflow to a forms library and it generates the necessary tasks and emails for 2 levels (serial) of Approval on the form. When the form has been approved the 2nd time, I want it to trigger another workflow that notifies a 3rd person of the existence of the form and assigns her a separate non-approval task. I'm creating it in SharePoint Designer, and I can do most of it, but I'm not sure what field I'm looking for, at the beginning in order to start the workflow. Currently, there is no column for Approval Status in the form library, only a column that shows the status of the workflow (In Progress or Approved).  I tried If Workflow Status equals Approved then email.... but that didn't work. Any help is greatly appreciated.  I'm looking for the data source field and the list item field and value, I guess.  

WCF and Entity Framework Design question

Should we be using Entity Framework entities as data contracts across the WCF Layer? If yes when why have the POCO support and code only been added to the Entity Framework? I was assuming it was with the purpose to remove the dependancy that the client will have on the database if the client depends on the Entities. If No, then does that mean we need to create Business Objects for each entity that exists in the entity framework model? Please advise. Thanks.Nilotpal

Dimension design: Key column of non-key Dimension Attribute

Assume I have a product dimension where key dimension attribute is product code - this is a very large dimension with more than 1 million members, the key column for this attribute is productID (integer). There will be other attributes in this dimension related to key attribute.   My question is about the key columns to be defined for these other attributes. As it is related to key attribute, it has to include ProductID as part of key and hence forming a composite key - e.g. Product inception date - the key column for this has to be ProductID + date as many products can have same date. This design will violate the best practice recommendation from MS to have only numeric key columns for very large dimension attributes. But I can't find a way around this and assume this will be case for all non-key attributes in very large dimension. Is my understanding right? I assume this will be an issue for everyone? Any standard way to get around this? Or best to leave it as such? Thanks in advance.

Pipeline Data Cube Design Question



The Analysis team wants to analyze sales pipeline data. Our CRM system(Source system) send the Data Warehouse team transaction as the Product moves through the different stages.


To mention a few: Creation, CallMade,LeadEngaged,CreditCheck,PapersReceived,Closed.There are more than 100 tracking statuses like mentioned above.


Sample transactions fact table:


Question of replacement of if..else conditions with design pattern



I am working on an application which contains code in lot of places where certain objects are created based on certain condition. The code is something like the following:

if(affectedObject == "Customer")
     Customer c  = new Customer();
else if(affectedObject == "Salesman")
     SalesMan s = new SalesMan();

// or 

     case "Manager":
          // Do some processing.
     case "Employee":
          // Do another processing.


I have gone through design patterns once but I am not able to figure out how I can use a pattern to replace my code.

Can any one please help me with this? Any practical code sample would be of great help (code without refactoring and refactored code) so that I can understand it better.

Thanks in advance.

Design Question - Child Class Reference


There has to be a better way to design the following:

public class Member {
  public int stateID {get; set;}

  public State state {
    get {
      if (this.stateID > 0) 
        state = State.GetByID(this.stateID);

public class State {
  public string name {get; set; }

  public static State GetByID(int id) {
    //populates state from db

Now if I loop through a list of members and need to display the state name for each member and I call "myMember.State.name" - I'll get the name of the state but it costs me an extra query everytime I call that - even though some of the states may be the same.

Is there a way somehow keep some sort of cache of state name from the db for access?


Worker & Location Database design question


Hello to forums :)


I have a simple question on design.

A company has several 'Worklocations'. Each location employs several workers. Every worker can be associated with multiple locations.

So, I have decided to create below tables. please letme know if there is a better way to design it

worker table
wid (primary)

location table
lid (primary)

workerlocations (WID+LID => primary key)

Is this sounds right for you guys? let me know

Class Design Question



I have a question about class design. Currently I am working with following tables:

| Forms
| FormID (PK)
| CommitmentID (FK)
| ...

| Commitments
| CommitmentID (PK)
| CommitDesc
| ...

| Pricings
| PricingID (PK)
| Desc
| CommitmentID (FK)
| ...

| PricingTiers
| TierID (PK)
| PricingID (FK)
| Desc
| ...

Forms <-one to one-> Commitment
Commitment <-one to many-> Pricing
Pricing <-one to many-> PricingTiers

When designing the classes what would be considered a best practice ... for instance at the moment I have 4 classes "Form", "Commitment", "Pricing", and "PricingTier" respectively.

Form class has a public property Commitment which represents an instance of the Commitment class.

Commitment class has a public property List<Pricing> which represents the one to many (Commitment - Pricing) relat

Design question in Report Manager?

When you view/launch a report by clicking on it in Report Manager you get a "double arrow" on the right side of the window with the help text "Full screen". If you push that button the top area with links to for example "Site settings" disapears. Is there any way to make that the default setting when you view a report?

I cant find anything about this in my books, can anyone help me?


Multiple users system design question.



I am designing a web application that will allow users to accept jobs.  Once accepted, the job is locked until completed or released my an admin. 

I have researched this pretty heavily, everything seems to suggest this.  While I think this might be part of the solution, I am still wondering how to reflect the change on the screen. 

The solution we have come up with is to do polling with ajax, but the case we come back to is even if we check ever second...there can be ( and this is very likely ) that two or more users will click the same job at the same time.

Maybe this is as easy as this:

if( job.isLocked() ) {

    // Notify the user, and refresh grid.
    WarningLabel.Text = "Job is taken!!!!";


Our users use IE7 if this matters.  If anybody can give me a confirmation if this is the 'right' way to go about this, or propose other options I would greatly appreicate it.

Role playing dimension and member naming question.


I have a fact table with invoice information that has multiple date columns.

I had originaly only needed to join my time dimension to this fact table on it's create date, but I have now added a role-playing dimension to join to the invoice date.

When I had 1 date dimension all of it's members where called 'week','year', 'day', etc.
Now that I have the role-playing dimension I have two dimensions with member names like 'Date.week', 'Date.year', 'Date.day', 'Invoice Date.week', 'Invoice Date.year', 'Invoice Date.day'.

So many queries I had written to reference the original date dimension no longer work because of the extra 'Date.' prefix. Is there a way to hide this prefix for my original date dimension?

Thanks in advance.

WCF - Basic Design Question Regarding No. Of Services vs No. of Tables



Let's say you have N tables, and you want to write CRUD as per table not in an OO fashio. 

How many WCF services are required?

is it near 5 * N (C,U,D, R and List with different criteria)? 

Or would you have 1 service that takes care of all the CRUD?

What would be the interface (parameters) passed to such a service.

The number does not really matter, in fact the only thing I care about is the architecture.

If you are not able to provide detailed answers, please point me to where I can learn more about this.

Thank you.



Design/Concept Question involving External Source


I have a design/concept question. I’m currently in the midst of developing a WPF Application which is a basic “data massager”. However the data is originating from a piece of equipment. This piece of equipment can be configured to send results/data to an external source, for example a traditional ASP page where the data would be encapsulated in the URL as a parameter. Normally I would develop a quick ASP page to capture the data in the URL and import into a table. However due to process flow it’s best to have the data captured in my WPF Application prior to a table insert.


Is it possible to have a WPF Application get called (similar to a URL) and receive data via parameter? I’m look for a point in the right direction. Maybe an online resource?


Thanks in advance!

Simple question but very new to Replication - Design question


Hi All,


I have a requirement of desiging a good system.


I have 5-6 servers. and i want to keep 2 servers up to date.

Server List

1) Read

2) Write

3) Backup

4) Staging


Read connects to Application.

Application is getting data from Read Server,. but Writing data to Write server.

Write server is being written by Application and it writes and keep the Read Server upto date.


Backup server takes backup of Read, Write Servers.

Also, from Staging server, everything should be written to write Server. and then write server makes Read Server upto date.


SQL Server 2008 will be used in this design.


Please help. I'm very very new to admin advanced side.


Thanks in Advance.

Question on WorkflowApplication class design ( Func Properties)





I found out that the Workflow Application class offers Func<T> properties to inject code for example when a error occurs.

whats the reason behind the Design Decission of giving the WorkFlow Foundation class Func<T> properties instead of event?


For Example


 Func<WorkflowApplicationUnhandledExceptionEventArgs, UnhandledExceptionAction> OnUnhandledException { get

; set

; }





 Action<WorkflowApplicationCompletedEventArgs> Completed { get

; set

; }


Is it just to prevent the multiple receipients ?

Or is it related to memory mangement due to fix pins caused by eventhandlers ? To be honest i must confess that i'm not sure wether Func<T> will created a fix pin in memory :-(

I'm not sure about if this is good or not, but it would be very nice to get knowledge of the reason why this class is event

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