Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSRS Data Source View Expand / Collapse
Author
Message
Posted Thursday, September 09, 2010 1:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 20, 2013 4:49 AM
Points: 9, Visits: 38
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.
Post #983402
Posted Friday, September 10, 2010 3:31 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 09, 2012 2:48 PM
Points: 493, Visits: 636
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.
Post #984165
Posted Saturday, September 11, 2010 12:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 20, 2013 4:49 AM
Points: 9, Visits: 38
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.
Post #984257
Posted Monday, September 13, 2010 7:20 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, November 09, 2012 2:48 PM
Points: 493, Visits: 636
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)
)
Post #984740
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse