Hi MVP's need help in this scenario
is it necessary to join those tables in star schema from where you derive measures with fact tables on the basis of their primary and foreign key ...
View Complete Post
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 ?
PS : I didn't consider the option of setting the order total in a dimension.... hope that's not the good way to do !
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.
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))
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?
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
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.
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?
I am very new to SSAS world.
My question, can any one guide me , how to create Fact Table and Dimention 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?
I'm new to SSAS.
I have dimensions: [Customer], [Date], [Group] (group is a dimension I use that allows me to group customers into predefined categories like frequency) and facts: [Daily Spend], [Monthly Spend Snapshot].
The monthly snapshot fact table I use mainly to hold the measures I use to rate the customer into categories defined in the [Group] dimension.
I don't understand how to relate the monthly snapshot fact table back to the daily spend fact table so I can display daily spend metrics.
I'll try to illustrate below:
[Monthly Spend Snapshot]
I have a column in my fact table called Average Handling Time, here I keep an average value - this is at a leaf level.
What should I set the aggregate to in my cube. For example, when I aggregate this to team level I don't want the sum but the average.
I did try the AverageOfChildren but didn't get the right value - see below