Reg: Aggregation not working in Dim table

User_JZKI6

Hello Team,

Hope everyone doing well.

We have dimension and fact tables all the detail level tables and no hierarchies was created.

Recently we have created summation columns in both fact and dimensional tables

1. Distinct count column based on ID in the table, we kept default aggregation rule as distinct count  (this we created in both fact and dim)
2. TIMESTAMPDIFF calculated column, we kept default aggregation rule as sum (this we created in dimension only)

After deployment in fact tables it’s working fine whereas in dimension table for Timediff column it was showing null and distinct count column it was showing 0.

When we tried to check the query in session log physical query was not generating we could see below error message can someone help on this.

Error message: converted to null because [nQSError: 14020] None of the fact tables are compatible with the query request Individual

In RPD we don't have any errors or warnings.

version using : OBIEE 12.2.1.4.0

Need to add another point.

Dimension tables is a detail level table it had snow flake join with another summary dim table and it didn’t joined with any of the fact table directly

Thanks,

[Deleted User]

3913018 wrote:Recently we have created summation columns in both fact and dimensional tables

You created aggregations in the dimension?

Stop right there. Take 5 steps away from the computer.

Never ever do that. That's the wrongest possible thing. Facts hold measures with aggreagtions and nothing else. Dimensions hold dimensional attributes. That's it.

User_JZKI6

Hi Christina ,

Thanks for your reply.

date fields was available only in dim columns calculating for timediff .

Is there any other approach to achieve this I have added another point in the question   

Need to add another point.

Dimension tables is a detail level table it had snow flake join with another summary dim table and it didn’t joined with any of the fact table directly

User_JZKI6

Can anyone suggest on this or how we can create measures from dim tables (In my case we have start date and end date column need to find Time diff of hours and days between the date columns).

Thanks,

Gianni Ceresa

3913018 wrote:...how we can create measures from dim tables...

The star-model has very simple rules: a dimension is a dimensions because it contains attributes qualifying measures. A measure is an aggregated information.

So if you need a measure from a dim table, it means you dim table must be modelled as a fact table and contain measures. But only measures. Any other part of the table which isn't aggregated as measure must stay outside in a dim table.

OBIEE business models work by referencing objects, therefore a single object can play multiple roles in your business model.

Stick to the rules of the star-model, otherwise be ready to have issue after issue in the future as you aren't using the tool as it's designed and therefore OBIEE could act in a different way than you expect.

User_JZKI6

Thanks Gianni for the reply.

I will be trying this approach, making another fact_date table adding all date related column from physical tables (detail dim table) and creating required measures using Timediff and aggregation sum.

Joining new fact_date table with summary dim table, already there was a snowflake join with summary dim to detail dim table. Using the measure columns from fact_date table let’s see will it work.

Thanks,