.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

Datetime fields within a fact table

Posted By:      Posted Date: September 29, 2010    Points: 0   Category :Sql Server

Morning all,

I'm currently defining a fact table in my first datacube and have a question (basic one albeit).  Can a datetime column be left in a fact table as a measure or should this be stored only within a dimension table?  The other columns I have left in thus far are foreign keys for the dimension tables.

Any pointers appreciated.



View Complete Post

More Related Resource Links

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?


Linq to SQL returns null for DateTime fields.


I have a linq to sql entity class in which I declared some DateTime fileds like this:

[Column(DbType = "smalldatetime")]
public DateTime? StartDate { get; set; }

And this is how I fetch data form repository:

return repository.Logs.OrderByDescending(l => l.LogID).FirstOrDefault();

All non DateTime fields are filled properly but all datetimes are null.

[Column(DbType = "smalldatetime")]

        public DateTime? StartDate { get; set; }

How to access the refrenced table fields in Ilist object of table MVC asp.net



I have 2 tables, master, detail. 1.master table have fields (id, username, plan)-->id is primary key (PK) 2. detail table have fields (srNo,id, worksummary, ... )--> srNo is PK.

I have created foreign key relationship from detail to .master table for "id" field.

the code is:

IList<detail> objDetail=new List<detail> (); 
IList<master> objMaster = new List<master> ();
string[] sarray = queryFields.Split('|');//

for (int i = 0; i < sarray.Length; i++)
string[] sfields = sarray[i].Split(',');

if (sfields[0] != "")

objDetail.Add(new _detail { Id = count , modify = sfields[1].ToString(), verified = sfields[2], I});


I have problem to add fields in "objDetail" using Add method. But I am unable to access the reference field "Id" , rest of the field of detail table can be accessed using objDetail.

How can I access the "Id" field from objDetail object to add in IList.

I want to solve this problem

I have added new fields to a table in SQLserver, why could not find these new fields in Analysis Ser

I have added new fields to a table in SQLserver, why could not find these new fields in Analysis Services database source view?  My version is 2005.

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

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

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.

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.

Reviewer suggested common table expression instead of adding 12 fields

So during a code review a DB Mgr suggested that I use a common table function instead of adding the fields below. No idea how to possibly do this. Anyone have any ideas? Here is a code snippet that he wants to lose: SELECT   LP .leaseID, sum   (paymentAmount1+paymentAmount2+paymentAmount3+paymentAmount4+paymentAmount5+paymentAmount6+paymentAmount7+paymentAmount8+paymentAmount9+paymentAmount10+paymentAmount11+paymentAmount12) as restPayment FROM   leasePaymentScheduleDetails AS L inner join leasePaymentSchedules AS LP ON L.leasePaymentScheduleID = LP.leasePaymentScheduleID WHERE   paymentYear>=@curyear+5 GROUP   BY LP .leaseID   Any input is 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

Joins on non-indexed fields and table primary keys.


I have two tables (a,b) each with a primary key (which are clustered indexes). When I issue the t-sql statement

delete XX from t1 XX inner join t2 on (xx.A2 = t2

[SCD] fact table and SCD



I've read several articles about SCD, most of them exmplaining the standard SCD type 2 rules, like a customer adress change.

My problem is about PurchaseOrders changes :

I have a fact table "Internet Sales", with a Total property for each sale

Imagine that this Total property can change (ie. : missing product cannot be delivered) --> the order is recalculated, and its total changes. Of course, I need to keep both informations for the order, because if I do a request for the sum of the order totals for example, I won't get the same results if the request is done with data as they where BEFORE or AFTER the Total change. .... hope that's clear.

I wanted to add a "start", "end" and 'current" columns on my fact table, but I read that it's not the good way to do.

Can you help me with that ?

Thank you


 PS : I didn't consider the option of setting the order total in a dimension.... hope that's not the good way to do !

Need 2 Measure Groups for One Fact table



I have one fact table that contains all the measures.  The problem is that I want to have two measure groups that point to this one fact table.  Some measures would be in measure group A and some in measure group B but the underlying source still comes from the one fact table.  I haven't found a way to do this.

My solution currently is I have created another fact table that is an exact copy of the main one, that way I can create two measure groups.  The issue is performance, it takes 4 minutes to build and if I took one of those fact tables out it would be cut in half.

I would have thought there would be a way to create a measure group and drag what you want in there..but it seems that this isn't the case that you can only create measure groups based on how many fact tables you have.

Any help would be appreciated.


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


Building Cubes with less granularity than the underlying fact table


If I have a fact table with a geography dimension where zip code is the primary key in the dimension table and the foreign key in the fact table, is there a way to create a cube where the leaf level in the geography dimension is at a higher level, say City (or State), even if City (or State) is not an attribute on the fact table?

Alternately, if I have a fact table with dates but I only want monthly summary data in my SSAS cube, is there a way to make the Month level the leaf level member of the Time dimension in the cube without adding a Month foreign key column to the fact table?

It would seem to me that people would want to do this wiht some frequency; however, I can't find a way to do it in the SSAS documentation.  This link seems to suggest that the grain of the cube has to be basically the same as the grain of the fact table in all cases: http://msdn.microsoft.com/en-us/library/ms166573.aspx


how can i drag and drop fields of table in teh desing surface


I am followint the insructions of this walkthrough of VS2003


adn in a part say this

  •  8   Find the authors node and expand it to show the fields in the authors table.
  •  9   Using CTRL+Click, select the au_id, au_lname, au_fname, and city fields.
  •  10  Drag these fields from Server Explorer onto the design surface
  • Now i am using VS2008 

    When i try to drag and drop i can not made it is maybe because of the differences between 2003 and 2008 versions

    how can do it in VS2008?


    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