September 9, 2010 at 1:32 pm
Hello all.
Just looking for some advice for a newbie to SSRS.
Search as I might I've been unable to find any useful information with regard to best practice for DSV design approaches. I guess the crux of my question is should I, when starting from a relational source, be using the DSV to model a star(ish) schema or would that be counter productive. Is it normal to simply import the tables and associations from the underlying source untouched, or is some tweaking required?
Another thing troubling me is how to handle multiple pseudo "fact" tables that reference a single "dimension". Is it acceptable to use a single dimension table joined to several other facts, or would that result in a potential ambiguous join. My concerns arise from pit falls encountered when using other data modelling tools (see Cognos' Framework Manager), where it is recommended to use multiple aliased instances of the dimension table.
Appreciate any advice you chaps have.
Ta.
September 10, 2010 at 3:31 pm
It sounds to me like you are using SSAS (Analysis Services). I always try to start out using the base objects as they are in the DSV, and if I need to do something special I can expend it would select statements. You should definitely just have one dimension and not try and create duplicates to be use for different measure groups. You can use the same dimension more than once in the same cube or measure group. For instance the time dimension might be used multiple times in the same measure group, once for transaction start, and once for transaction end for instance. The dimension can also be used by different measure groups or cubes. There is a concept of role playing which is how the measure group uses the dimension, or the context it uses it in. This abstraction layer allows for the contextual dimension to use a different name than the base dimension.
Hope this helps.
September 11, 2010 at 12:53 am
Thanks for your comments.
I am using reporting rather than analysis services, just pre-disposed to treating everything as a warehouse.
I take your point regarding re-use of a dimension but how would this be handled in the scenario where, for the sake of argument, two country code fields exist on a fact table (ie origin and destination country). If these country codes were different would this create two paths via the country dimension to the fact, or would this not be an issue.
September 13, 2010 at 7:20 am
This goes back to the role playing that I mentioned.
Dimensions can play different roles in a fact table depending on the context. For example, the Date dimension can be used for the ordered date, scheduled shipping date, shipment date, and invoice date in an order line fact.
In the data warehouse, you will have a single dimension table for the dates. You will have multiple warehouse foreign key from the fact table to teh same dimension.
The following code demonstrates this from the relational perspective:
CREATE TABLE DimCountry (
Country_Code INT NOT NULL
,Country_Name Varchar(200) NOT NULL
,CONSTRAINT PK_DimCountry PRIMARY KEY CLUSTERED (Country_Code)
)
CREATE TABLE Fact_Table (
Fact_ID INT IDENTITY(1,1) NOT NULL
,Important_Fact INT NULL
,Origin_Country INT NOT NULL
,Destination_Country INT NOT NULL
,CONSTRAINT PK_Fact_Table PRIMARY KEY CLUSTERED (FACT_ID)
,CONSTRAINT FK_FT_Origin FOREIGN KEY (Origin_Country)
REFERENCES DimCountry (Country_Code)
,CONSTRAINT FK_FT_Destination FOREIGN KEY (Destination_Country)
REFERENCES DimCountry (Country_Code)
)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply