.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

Loading Dimension & Fact tables

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

Hello Experts,

I am new to SSAS & I need to load Dim & Fact tables for a data warehouse. I've the basic idea to load them, but i dont have the exact picture to load a fact table. For eg. if we have 3 dimension tables like Dim_Time / Dim_Geography / Dim_Product and one Fact Table Fact_Product as described in most of the online examples. Then how to load them. I know for fact Tables we need to do aggregations. But how to apply those aggregations on what criteria.  In the said example if we need to load Fact_Product then we should be able to see sales by product, sales for a given point in time & sales for a given geographic location. Then how to do that. Do we need to apply aggregate for all the dimension tables

I know i am not much clear, but i hope you guys can understand. If not please let me know i will try to explain it more. Please clear my doubts.

Thanks & Regards,


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        

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?


Delta loading on 400+ tables, pls help.

Previously, we IT team developed an ETL package to create and load tables that exist in source database(oracle) but not in target database(sql server). As it may come to your mind, in the ETL package we use ForeachLoop and Oracle's sysobject to generate 'create table..' scripts to build the tables in Sql server then use script task to load data into the tables which have different structure through bulkcopy. We did not perform delta loading. Just in a drop-create-load route. Now, a performance issue comes. It takes almost 30 hours to generate and load all these more than 400 tables. The number and data volume are increasing, so we have to think about delta loading. But how to do the delta maintenance on 400+ tables? please provide ideas.   Appreciate your help. (Oracle10, SS2005, BIDS2005) Zen

Occasional Errors loading SQL tables using MDX

Hi, I have been asked to investigate an issue of error 7321 while executing MDX to populate SQL tables using openquery. The problem is most of the time this query runs fine but at times it fails with the error 7321 error. Since it runs most of the time, i guess syntax isn't a problem here. Yes, there are some With set statements; but we are using SQL 2008 and believe that issue was fixed in 2005 itself. Re-running the SP again however gives the desired results Currently we are extracting the first 2000 characters from the error message which starts as and that gives a truncated version of the query, so first we are trying to increase that so that we get to see the entire query that is being parsed when it fails. The last error message read as "An error occurred while preparing the query "MDX .... Any other pointers on how to approach this problem would be appreciated.  

SSAS 2008 Dimension root values changes after incrementally loading the dimension with 'process_up

The Dimension root  members (below 'ALL') changes after incrementally loading the dimension with 'process_update'.  How do I prevent that from happening ? There was no changes to data of the underlying table of the dimension.   

Getting counts by 2nd Date Dimension Attribute with Snapshot Style Fact Table

  I have an MDX question finding hard to solve.  I have a Snapshot Fact Table with a snapshot of the records in the source system for each batch date.  All records in the fact table are assigned the batch date with the batch date key.  There are many records for each day and each batch date is an entire copy of the source records.  So, the grain of the fact table is one record for each batch date that exists in the source system.  These facts rows have another date in them for when the record was entered.  This date is different from the batch date in that the batch date is based on the day the batch was processed and the entered date is based on when the record was entered.  If a record was entered many days before, its batch date will be today but its entered date will be several days ago.  Therefore each day a copy of all the records entered the previous batch date and all the records added on today's batch date are present. Fact Table : FactSnaphshotKey (surrogate for easier administration) BatchDateKey (link to batch date dimension – date dimension, first in dimension list so it is used for semi aggregate measures) EnteredDateKey (link to entered date dimension – date dimension) Facts Count – measure for fact table - default measure from Analysis Services cube 2 Dim

Non parent-child dimension with fact data at different levels... How!

Hi all, Quite new to SSAS, wondering if anyone could help with the following... I've got a dimension with attributes that indicate geographical location based upon UK postal boundaries - so ~1.8m Postcodes > ~10000 Postcode Sectors (PCDS) > ~3000 Postcode Districts (PCD) > ~100 Postcode Areas (PCA). The problem I have is that the address information is not of the highest quality. I have matched 50% of my facts to postcodes, of the remaining 50% i've mapped them to PCDS, PCD or PCA, where possible, leaving the lower-levels as NULL in those cases. My attribute hierarchy works fine where I have complete records all the way down the hierarchy, but I only have a single UNKNOWN member at the top (GOR) level. I want an UNKNOWN member at each level. It makes sense to me how to do this using a Parent-Child dimension, but i'm keen to avoid that as the performance is terrible. I'm hoping there's a method of configuring this. Any help massively appreciated.

Avoiding a SELECT distinct query generated by SSAS when using dimension derived from fact table

Hi, I am using a dimension derived out ot a fact table and the factt able primary key is dimension key. Issue is, there are large number of rows and so many attributes. SSAS issues distinct query and it takes large amount of time. Without the distinct statement, query takes only 3 min for 4 million rows. With the distinct, it takes 20 min. Becuase the fact primary key is the dimension key there is no need of a distinct statement. I know there is a option in the dimension to say "By Table" to avoid this. But unfortuantely, i breach the 4 GB limit for strings. Any suggestions for optimization? Thanks,  Sambath

Need multiple distinct counts, have 1 fact and 1 dimension

I am using SSAS 2005. I have 1 fact table and 1 dimension. I would like to create multiple distinct counts in 1 Measure group, at least I would like them to appear as in 1 measure group to end-user. I have tried role-playing dimensions, and a roll your own approach that work in limitation but didn't scale. Any help and advice would be great.

Join two fact tables




I have the following two fact tables, upon which a cube was built. As you can see, these two tables have most of the common (shared) dimensions except for ProductTypeID (product dimension) on fct_productsales table. The relationship between these two fact tables is 1(fct_loansales) to Many (fct_productsales).

When I browse these measures from the cube, they look fine (and break down perfectly) through all the shared dimensions.

However, when I try to filter or browse through product dimension (along with other dimensions), the measure values from fct_productsales will filter down correctly, but the values from fct_loansales does not change.  I tryied to create degenerate dimension from fct_loansales, but no luck. Any help is appreciated.



AccountNumber                       char

Bridge Table dimension or fact? updating from snapshots



Scenario: Bank Accounts and Customers. One Account can have many customers and many customers can have one joint Account. so its Many to Many relationship.

Special Scenaio: Bank provide us daily snapshot of all thier dimensions and facts, every night thier ETL run, and newsnapshot is available, previous is gone.

I am using SCD Transformation to update the dimensions, Type 1 for all the columns.

Tables1: DimAccounts (AccountsID(PK))

Table2:DimCustomers (CustomerID(PK))

Table3:DimBridge (AccountID (FK), CustomerID(FK), RelationShip (varchar10))

Question1: Are we supposed to treat bridge tables as Dimensions or Facts?

Question2:If it is to be treated as Dimension, How would I apply SCD Wizard to it, Since there are two business keys involved?

Question3: Do i need surrogate key in Bridge Table, like i have in other dimensions?

Thank You


Save Dimension member only when this have lines in Fact Table


I have one dimension table with over 600 item, but in my fact table only 40 of this member have lines (my fact have a filter for the 2 laste year)


How save on the dimension only this 40 members?


Best regards




steps for loading data into a dimension table


Hi Experts,

Can anyone give me the steps for loading data into a dimension table from a SQL database table? An explanation as to why the objects are used will be very helpful.

Thanks in advance

Dimension key attribute changes in fact table



How do I need to handle a case in which the fields in a fact table that represent the foreign keys to the dimension tables might change? What kind of process do I have to do to the cube?



Map the start and end datetime fields of an historical dimension to the time dimension and the fact



I have a dimension with some historical attributes and I use start and end datetime fields to save the outdated records. I assume that when the cube is processed, each record in the fact tables is mapped to the matching record in the dimension by using the combination of the key attribute with the start and end datetime fields. My question is how to configure the mechanism so that it will know which field is the datetime field and use it to do the comparisons with the start and end fields?


How and if to create a (degenerate) Fact Dimension


Hi, need to know how to fully create a degenerate Fact Dimension. In a cube with the following Fact table:


TranxID (PK)

RepArea (PK)

RepCostCentre (PK)

RepOpsType (PK)

FinYear (PK, data is 2010, 2011 etc - Fact Dim?)

FinMonth (PK, data is 1, 2, 3 ... 12 - Fact Dim?)


AssetNo (poss another Fact Dim for Drillthrough)
TranxCost (Measure)


...the Primary Key fields have their own Dimension Tables apart from the Financial Year and Month - it seems overkill to have additional Dimension Tables for these because the data is already set as per shown (and the key values are small enough to act as part of the composite primary key) and the users do not need to see any lookup (ie. integer key value to lookup the month name, they would be happy with 3 for March). So, it seems quickest to just use them as degenerate Fact Dimensions. Same could also be said of the AssetNo, where a browsable fact item is a common requirement I beleive (although I appreciate that normally any item with repeated items, ie. one-to-many not one-to-one,  should be dimensioned through a table so it can be diced up properly).

Question: How do you set a new Fact Dimension so you can Browse by it? Folk here point to BOL but 2005 merely identifies the relationship without showing how to create one, and 2008R2 BOL says even less! My (2005

Fact Dimension not working in Excel




I have ItemSales fact table. I have fact dimension Item Sale Transaction Information. Key is EntryNo, it also has attributes DocumentDate, DocumentNo, Description.


The problem is, that while this dimension works fine in browser, it's not possible to add it to excel. Whenever I try to drag and drop it to rows or columns, it tries to execute query, but then it disappears again. If I try to check the checkbox next to it, it shortly appears checked, but then disappears again. Oh, and this only happens for EntryNo, that is Key attribute. Adding any other attribute works as expected.

Strange thing is, I have another measure group with similar dimension, and it works fine there.

I've even tried recreating the dimension..

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