.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

Dimension key attribute changes in fact table

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


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?



View Complete Post

More Related Resource Links

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

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

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




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?

Getting list of attribute values used by fact table?


I am trying to build a custom BI report with filter parameters.  One thing I need to do is get the list of valid parameters for each dimension attribute.  For example, the report includes the County attribute as one of the grouping attributes and users need to be able to filter by county.  When I generate the list of county checkbox/dropdown options in my ASP.NET page, I need to only list those counties which are referenced in the fact table.  There is no reason to list a county as a filter option if there are no associated facts. Essentially I am doing a inner join between a single dimension and the fact table, and then grouping by the attribute that I want to present as parameter choices.  The below options are what I've tried. 

I am hoping for suggestions on a better way to do this or maybe improve the performance of the option using an MDX statement.  I feel like I should be querying SSAS for the attribute information instead of querying the underlying tables directly.  If anything in my SSAS cube changes that would affect the attributes used in the report, then I will have to be careful that I update views as necessary to make sure the right columns/keys are queried.

1) I tried using a group by LINQ against Entity Framework model, but that is very slow because of the way Linq uses subqueries and distinct to imple

Why shouldn't I snowflake a date dimension to a table in my data source view that's not a fact?


This is quite difficult for me to communicate so please bear with me.

I have created my data source view in my solution. I have a central fact table surrounded by the dimensions. Several of the dimensions have dates which I converted to integers. Thinking that I would need separate date dimension tables so the date integers could find their dates I created a data dimension table "snowflaked" to the dimensions with dates.

An example is my dimPolicy table has dates of creation and ending.

I converted these to integers.

A table called dimPolicycalendar was made in the DSV and then linked. A dimension snowflaked to a dimension.

When I went to create a dimension for Policy it auto attached the dimPolicyCalendar. Then all my dates disappeared. And the generic values in the dimPolicyCalendar appeared.


How do I get my values back?

If I wasn't supposed to add a calendar table to the dimension how will I convert those integer values to the date values I want?

I am panicking a little because I can not find any helpful resources.

You are my last hope before we are pushed to an Oracle solution. Yuck!



BJ Gordon

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        

Table-Layout attribute and MSDN claims re capabilities

I am developing a CompositeDataBoundControl that is basically a 'table' inside a 'div', in order to limit the size of the Control and use the scrolling capabilities of the 'div' tag ('RenderBeginTag("div"). So essentially the control is; <div style="overflow:auto;width:400px;height:300px;"> <table style="table-layout:fixed;">   My problem/question has to do with the capabilities claimed by MSDN of the CSS Attribute 'Table-Layout'. See; http://msdn.microsoft.com/en-us/library/ms531161(v=VS.85).aspx   One of the essential goals of my Control is to limit the width and height of 'Cells' (<td>'s) in the table and let the content be clipped.  MSDN says one can do this by setting the 'width' property of each Cell in the first row of the table. I have no problem with this and it works just fine.   My problem is that MSDN also claims that one can set the height property of each row and that any wrapping of text is 'clipped' and the row height is maintained. See Remarks section; "If the row height is specified, wrapped text is clipped when it exceeds the set height".  I can't make this happen. Any time whitespace occurs in the cell content, and the content exceeds the cell width, the content is wrapped to a new line and the row/cell height is adjusted automatically to fit all the content. In other words it appears that MSD

Fact table in DSV vs partitions pointed to a different table

I am seeing an issue in my cube for a partition that is based on a separate table than the Fact table in the DSV. I have 8 partitions all from different physical tables. In the DSV I used 1 of those 8 partition tables as the "source" of the DSV so I could model the relationships between the fact and the dimensions. On 1 of the 8 it loads over 1 million rows from the partition into the cube, but when I use the browser to show the count in that particular partition it shows the exact same number of records that are in the table that was used in the DSV. The strange thing is all the other partitions work fine except this 1. I have deleted the partition and added it back multiple times and cant get it to work right. Has someone seen this problem before?   I have run into this a couple times, one way of fixing it was to recreate the entire project in a new project, copy all objects from the old projects and rebuild. I cant seem to figure out another way of fixing this.Craig

Building Fact and Dim table

How to build Fact and Dim tables for below requirement. Fact records (approx 800k) has to be analyzed every day w.r.t AgeGroup's Every record in fact will have a DOBSID and age/agegroup should be calculated every day based on getdate()

Record is already exists in Dimension table but still Kimball SCD Component is Identifying it as a n

Hi I have loaded Dimension table. Now even if the record already exists in my dimension table , every time I run package Kimball method SCD component is identifying as a new record. Please advise. Thanks, Anuja

Update an accumlating shapshot fact table

This is my first time implmenting an accumulating snapshot fact table and I require some guidance. Accumulating snapshot fact tables show the status at any given moment. It is useful to track items with certain life time, for example: status of order lines.eg everytime there is new piece of information about a particular purchase, we update the fact table record. We only insert a new record in the fact table when there is a new purchase requisition. What I really need to know is how best to handle the updates.  This really feels very similar to managing SCD-1's in dimension processing! Anyone able to advise? thanks in advance Here is a perfect example we can use  http://blog.oaktonsoftware.com/2007/03/accumulating-snapshot-use-accumulating.html Figure 1, below, shows an accumulating snapshot for the mortgage application process. The grain of this fact table is an application. Each application will be represented by a single row in the fact table. The major milestones are represented by multiple foreign key references to the Day dimension—the date of submission, the date approved by mortgage officer, the date all supporting documentation was complete, the date approved by an underwriter, and the date of closing.

Analysis Service Process Dimension Cause : OLE DB error: OLE DB or ODBC error: ORA-00942: table or v

Dear Gurus, I'd face a "STRANGE" problem. I'm using SQL2008 connect to Oracle Data Source (OracleOldDB.1) When I process a snow-flake dimension . There is a ORA-00942 problem. But if I past the SQL Statement to a Oracle Tool . It worked without any problem. Does any body ever face this "STRANGE" problem ? And I'd try to remove this table from data-source-view and re-join it . Still same problem !!  Wilson

daily complete cube rebuild four dimensions and fact table including remapping of all surrogate keys

Hi SSIS Engineers: Forgive me if this is a multi-forum question. Our primary activity in the next week is to automate the processing in SSIS, where I led the team to create complete processing flows for Full and Add in the order of Dimension, Measure Group, Partition, Cube, Database. These work. The problem occurs in a complete refresh of the ERP database that caused me manual effort inside SSAS, which I plan to find a way to automate in SSIS. I performed a complete refresh of our cube from the ERP source from a time perspective. We are automating this process in SSIS. In SSAS, I had to manually delete the four dimensions from the UDM view via the Solution Explorer. Since the complete refresh increased the surrogate keys in the dimensions and since the names were the same, I couldn't just drop the partition and reprocess the dimensions, since, in effect, new fact rows would have to be mapped to the new keys. SSAS held on to the old keys even with Full Processing of the Dimensions first, then the Cube. Until I dropped--deleted-- the dimensional tables from the Solution Explorer and the UDM then later readded the dimensions with the new surrogate keys (both add, update and delete dimensional attribute changes in full refresh) via the Add Dimension wizard, the cube kept the old surrogate keys and failed in measure group, fact, database and partition processing.

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.

Different attribute name in each dimension for a role playing dimension

Is it possible to name the attribute differently in a role playing dimension. For example I use the Date dimension as the role playing dimension for Ship Date and Order Date. When I use these attributes on the report, both of them show as 'Date' which is the name of the attribute in the Date dimension. Is there any work around to implement these names differently?
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