Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSRS Data Source View


SSRS Data Source View

Author
Message
prnuttall
prnuttall
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 58
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.
Toby White
Toby White
Mr or Mrs. 500
Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)

Group: General Forum Members
Points: 507 Visits: 639
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.
prnuttall
prnuttall
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 58
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.
Toby White
Toby White
Mr or Mrs. 500
Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)Mr or Mrs. 500 (507 reputation)

Group: General Forum Members
Points: 507 Visits: 639
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)
)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search