Fact Tables based on a View 1

[DWArchitect]

Hi,

I have two fact tables that join in a one to one relationship.

I want to use the joined tables as the fact table, can Analysis services create a fact table from a view or materialized view? it seems to only let me choose a table it doesn't show any views.

Also, can I rename a dimension level? I have a dimension level based on a field that has two values, 1 or 0, but I want the cube to display 'YES' or 'NO'.

Thanks,
DWArchitet

 

[johnherman]

Second question - you can associate a name with the code table when you define the dimensional hierarchy. Click in the names section and choose the table. This means you will need to create a little table with code = 1, desc = YES, etc.

First question, I believe you can use a view. I have seen it discussed here, but have never tried it myself. Have you tried searching this forum for the keyword 'view'?

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[DWArchitect]

Thanks,for the code lookup tip. I wanted to avoid building my own dimension lookup. Sounds like AS will handle this for you.

As far as sourcing a fact table from a view, I have not seen any posts on this and have not been able to figure out if it can be done.

DW Architect

 

[MDXer]

First Question:
Yes you can use a view. Infact views are my preffered method even if it is select column1, Column2 from MyTable, I use views for both facts and dimensions.. By using a view you can build some calculations into the cube rather than have the cube preform the calculation. Also it adds a level that insulates your cube schema from any changes in the fact table schema. The view can also allw faster processing if you partition your cube. you can have the same view with a different where clause and point a partition to the view. This allows you to process only current data and not reprocess historical data.

Second Question:
This is one reason I like to use views on dimensions. you can add the logic to the dimension to change 1 & 0 to yes & no. This also allows you to quickly modify the member names if the business users suddenly determine they need the members to be called something else.

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[johnherman]

I gave you the star. Someone needs to recognize your excellence in this forum.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright

 

[MDXer]

THanks

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[DWArchitect]

Hey ALL,

That's great you can source facts and dims from views. But how? I'm using SQL Severe 2000, when I go into Analysis Services and use normal approach, it only says pick table that will be source for the fact table. Nothing about views. Is it cause I'm using a wizard?

Thanks,
DWArchitect

 

[MDXer]

views are displayed as tables. in the table list the difference being the Icon (I believe).

Shoot Me! Shoot Me NOW!!!
- Daffy Duck

 

[DWArchitect]

I'm sorry MDXer but the choose table to base fact table dialog box does not show views, only table. UGH.

How do you point its source to a view in Analysis Manager?

Thanks,

Jeff