I have to create a new dimension and the main reason is i wanted to use it in the report and its not a measure , but its part of a FACT table. I need to create a dimension "Address". This field already exists in a FACT table (P_FACT), PRIMARY KEY is CASE_NUMBER.
And i followed the following steps:
1) Opened BIDS, Right clicked , create new dimension and DIMENSION WIZARD Opened.
2) Select Build Method ( i chose "attributes only") .
3) Select the data source view and i selected the corresponding DSV that i needed.
4) Dimesion Type (Selected the standard type)
5) Select the main dimension table and in the drop down list i selected the (P_FACTS) table, and in the key columns i selected the "CASE_NUMBER" .
Column contatining the member name(optional)-- i left it blank.
6) In this step of "SELECTED RELATED TABLES" i didnt chosse any and went to the next step.
7) In this step "SELECT DIMENSION ATTRIBUTES" , i selected "PRIMARY_ADDRESS".
8) In this step "SPECIFY DIMENSION TYPE", i selected "REGULAR"
9) In the next stpe where it asks "DEFINE PARENT CHILD RELATIONSHIP", i clicked next as each CASE NUMBER should have a PRIMARY ADDRESS and they are not PARENT CHILD relation.
10) Next step was "Scanning dimension tables...Permit FACTS: All relationships detected.- Dimension Wizard examined all relationships but did not create any hierarchies.
11) Final step i named the DIMENSION and PROCESSED the DIMENSION. PROCESS SUCCED.
But when i opened the DIMENSION and selected the Browser tab i could see only one field at a time, i mean in the heirarchy level drop down i see two fields (one is named as the table name PERMIT FATCS, but when i select it it shows me the case number's) -- how can i change the name to CASE NUMBERS, secondly is there a way where i can see CASE_NUMBER and PRIMARY_ADDRESS fields next to each other?
Finally how can i add this dimension to an existing CUBE?
Please need help or advice.
First, if you haven't already, you need to drag the address field to the attribute hierarchy pane in BIDS.
To add the dimension to an existing cube; open the cube in BIDS and go to the dimension usage tab. Right click and select add cube dimension. Based on what you described I think you want to use a fact relationship rather than a regular relationship.
Once processed use the browser in the Cube not the dimension to look at how it interacts with the data. You should be able to slice by address. Note: This is not a best practice. It sounds like you are trying to do relational style reporting with OLAP. If your fact table has data in there that are not keys, not facts, and are not maintenance columns they should be removed. Even things like comments should be dimensionalized to keep the fact table lean and mean.
Thanks a lot!