[ChrisKiklas]

I have a cube based on a star scheme DW. Everything works great with BiRT onDemand until I try to filter data. The links between my data sets for my Fact and Dimension tables use primary and foreign keys, which are all numeric, so in my groupings, the top layer has to be the key, but then I set the display field to the actual text description for what the key represents.

In displaying data, everything is groovy. For example, I have a dimension table for Status, so when I pull in status what gets displayed is the text value (display field) of say "Active" while they key of 12 is what's happening in the background.

Now, if I try to apply a filter to the data, and I click on "Select Values" I should see values like "Active," "Inactive," etc. but what I am seeing instead is the foreign key, so a list of numbers like -1, 14, 56, 34, 23, etc.

Is there any way to get the display field to show up instead of the underlying key?

Thanks!

[Srividya Sharma]

Hi

Can you please post a sample?
It would be easier to test.

Srividya Sharma

[ChrisKiklas]

Sure thing.

Our data warehouse uses a star schema implemented with surrogate keys to protect the integrity of the data against possible changes in dimension data over time. One example is as follows:

FactAssignment is the main fact table for one of our stars. This table has a field called StatusSK which is the surrogate key to the DimStatus table. DimStatus contains StatusSK and also StatusDesc which is the text description of what the status actually is.

In the BIRT Designer, I try to create a group based on StatusDesc; however, I have to include StatusSK in the group or else when I am linking the Dimension table to the Fact table, the surrogate key is not available to use for the link.
 Edit Group Level.png (41.97K)
Number of downloads: 1
 Link.png (9.33K)
Number of downloads: 1

What I did to get around then is used the StatusDesc for the group, and then changed the Key Field to the StatusSK field and set the Display Field to StatusDesc.

Then I uploaded everything to BIRT onDemand, processed my cube and then created a crosstab report based on my new cube. Everything looks great, and StatusDesc does show as the correct text based field you would expect. Then, if you try to filter on StatusDesc and browse for a list to choose from, instead of the text based descriptions of the Display Field, the values for the Key Field are listed instead. Since the Key Field is the surrogate key, the values will not make any sense to any users since it is only meant to be used by the database.
 cross tab builder.png (26.24K)
Number of downloads: 1

I've attached some screen shots of how it looks along the process to try and illustrate what I've described here.

Thanks for your help on this!

[Srividya Sharma]

Hi

I have been trying to re-create your problem, but no success yet.

I have created two tables with a relation, need to create the cube as per your requirement.

I have created a simple sample using flat file data source.

Can you please modify the sample and tell me what the problem is.

Rename .csv.txt to .csv
Browse for the csv files location in the design file before testing/modifying.


Srividya Sharma

Attached File(s)

•  Table1.csv.txt (142bytes)
  Number of downloads: 1
•  MasterTable.csv.txt (66bytes)
  Number of downloads: 1
•  TestFilter.rptdesign (13.72K)
  Number of downloads: 1