.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

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

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


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?


View Complete Post

More Related Resource Links

Dimension with multiple fields per fact row




I have a dimension which looks like this

Customer      Customer Responsible

Cust1             TMG
Cust1             TOL
Cust2             TMG


Time Dimension Enhancement with Business intelligence Issue

Hi all, I want to add a year over year growth using the BI wizard (Time diemsion enhancement) but when I try to add this enhancement via the wizard then this last one has the button next disabled with a waning that says   A time dimension is required to enable this functionality. Ensure that you have a dimension of type Time, that contains at least one hierarchy with a level flagged as a time period. Inspite of the fact that I added that time dimsension with one hierarchy Time hierarchy Calendar Year Calendar Semester Calendar Quarter Time Key(With namecolumn defined as a named calculation that repsents the day with this format  yyyy, dd mm ) Me personaly I have a doubt about the last condition of the warning (with a level flagged as a time period) but I dont know exactly 1. If my doubt is right 2. What shoud I do to enhance the cube in this context using the time dimension enhancement The complexity resides in the simplicity

display non measure, non dimension fields in drill down

Hi All, I created my fact table with more fields than just the foreign keys linking dimensions and the field(s) to be used as mesures.  I did this hoping that on drill down I would be able to see the extra fields so that the user would have access to detail information on the records making up the measure amount.  The extra fields do not appear on drill down.  How can I make them appear, or am I on the wrong track? Thanks for any help

Create a dimension based on 2 fields

Hello I have a table as follow: No   Placestart    Placeend 1      DK                USA 2      UK                USA 3      USA              DK Now, I want a dimension called Country, which selects either of the rows where a value exist In SQL it would be ex (SELECT * FROM table WHERE placestart = 'USA' OR placeend = 'USA) So, when I select USA in the dimension all 3 rows are listed, as USA is included either in placestart or placeend. If I select DK row 1 and 3 is selected etc... Is this somehow possible?      

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        

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

Time dimension not grouping correctly

Hello, I'm new to SSAS but I did some reading and some training already. This is my third cube and I'm having trouble with my time dimension.. - first I browse the cube and drag the year and month to the columns section and it correctly renders only the times to which I have data - then I drag my workstation dimention to the rows section and it's also shows the wks fine. - Then I add my counter dimention to the rows section and it correctly shows up as a sub set of workstations. - The problem: When I drop my value fact it renders different value to each counter but show the same value for ANY time, including future dates. (time dimension generated with SSAS) Let me explain the cube, maybe you have seen something similar or could have some insight. Fact: A hourly based table with counters for my workstations. Columns: Datetime, wksid, counterName, and value (value aggregated by max()) Dimention Workstation: id, name Dimension Time: timeid, date, year, month, semester, etc. Dimension counter: id, countername All columns were connected to the correct related columns in the data source view. Why would the cube fail to correctly aggregate my facts by different dates? Any clue? Please let me know if you need any clarification.. Thanks in advance!

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.

SSAS 2K5 - Server time dimension

Hi, I'm building a cube and I want to add a time dimension. I don't have any time table in my OLTP (Source) database. So I tried to use the wizards from SSAS to create a time (date) dimension. I have choosed the "Server time dimension" one. It did create all what I need with all the hierarchies. But when I tried to add time business intelligence with the wizard, it doesnt see my new time dimension. I have checked if the type was "time" for the dimension and It was ok. So I don't know why the Time intelligence can't see my time table .... btw, I start the Business Intelligence wizard from the cube.   Thanks

Shell Dimension - Not Time based

Hi   I am creating a shell dimension to create a hierarchy.  The idea is we want to know how much revenue we make from new accounts.  So I have created an account dimension and linked that to the Revenue fact.  I also created a Fact linked to the Date and User dims that will show the date the user was created.  By doing this, I was able to create a calculated measure that will show me total revenue, plus the revenue that is only for new players for a specific month: with member NewRev as aggregate( exists(DESCENDANTS([User Account].[User Account].[All], [User Account].[User Account].[User Account Key]) , [Date].[Date - Calendar Month].currentmember , "User Registration") , [Measures].[Revenue])   select {[Measures].[Revenue]  , NewRev} on columns , [Date].[Date - Calendar Month].[Calendar Month].&[201008] on rows from [Revenue] Sorry it is a bit messy, testing at the moment. What I would like to do is create a shell dimensions so I can create a hierarchy that will show new and returning players as members.  It would have an all level, or the user could split the revenue by this dim.  I set things up like the Time calculations shell dim, but am a bit stuck with how to create the mdx so that it will work with any measure. What would I change to do this? Regards Michael

Getting Query Time Out Problem in Particular Dimension?

We are getting query time out problem in particular dimension in SSAS. can any one help on this?  

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

How big is your time dimension ?

Hi, We have a time dimension which is used by around 90 columns out of 20 tables. We have a fixed time table which started in 1800-01-01 and ends in 2199-12-31. This gives around 150000 members in that dimension. That does hurt the performances and the users complain like why does the time start in 1800 ?. So we created a view a which says give the last 5 years and coming 2 years. Users are happy and the performance is fine. Until one day we added another system. That system has some "strange" dates in it. They are dates like 1900-01-01 ,1901-01-01,1966-02-23, 1995-04-31 but also 2017-01-01, 2019-01-01 or 2022-05-01 or even 2098-03-04. The guy who build the export says : I am not validating but only exporting what the user has entered. The guy who build the import says : I am only importing data and I validate only the type (dates like 2020-03-35 are rejected) So what should I do, make time big again and have angry users and bad performance ? I can make filters so that they won't show up but I don't like losing data and the check reports will fail. I can replace them with another date but I don't like that since I am manipulating data. I can convert them to unknown but I don't like that too. Any other suggestions ? And is a time dimension with 400 x 365 members big ? Constantijn    

Dimension Range over time

Is there a method for categorizing measures into ranged dimension attributes over time.  Consider the SQL:       SELECT date, sales = SUM(sales), segment = CASE WHEN SUM(sales) BETWEEN 0 AND 99 THEN 0 WHEN SUM(sales) BETWEEN 100 AND 199 THEN 100 ELSE 300 END      FROM tabl1      WHERE date BETWEEN '01/01/1900' AND '12/31/1900'   Date Sales Segment 1/1/1900 100 100 1/2/1900 150 100 1/3/1900 49 0 1/4/1900 175 100 1/5/1900 99 2 … … … Is it possible to apply this logic across a time range?  For example, this is on a daily basis, is it possible to do this on a monthly, yearly basis or for a range like '01/02/1900' - '05/25/1900' at monthly rollup? Month Sales Segment January 999 300 February 1 0 March 500 300 April 199 100 May 99 0 … … … I've done this using keys on the fact table; they aren't dynamic, but aggregate the measures into defined buckets January 1900, February 1900, etc.  My keys on the fact table are at the monthly level.

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.

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


Time Dimension


HI All,


I have seen that in on our cubes, the time dimension is setup as a regular type dimension instead of time. How would it affect the users? What's the process to change it back to time dimension? Please advise..


Thanks in advance.

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