PowerBi summarizes Oracle query (view) data incorrectly by date

[Acarrier]

Hi All, 

Some data in a power Bi report is an oracle view about numbers for a work shift. Each day has 2 shifts, 1 and 2. Basically the Key is the prod_date and prod_sh. See the query below returning the date and shift. (first scrteenshot)

You see the result on the second screenshot where the data is summarized by date. Although the total of the numerics is correct, I need 2 records per day, one for each shift. Data points such as the crew name are incorrect as they are summarized (character field).  All shift fields are = 1. One of the metrics I need is the numbers per crew. The summarization breaks all this. 

I have removed the summarisation from the prod_sh (shift) field and even added a shift table, 1 = night and 2 = day ( see screenshot 3) that links to to the prod_sh field. I reloaded the data and no change. 

How can I tell powerBi to not summarize by date? I have not found so far how to solve this. 

Would this be partly because it is a view and powerBi does not know what the key for the rows would be?

Take care all!

[Anonymous]

Hi @Acarrier ,

I think it's caused by the relationships built in oracle.

You may try to

1. Add a Index column to the table.

2. Delete the relationship

3. Or uncheck the "Include relationship columns"option

For more information,you can refer to this:

How to Create and Use Indexes in Oracle Database

Remove an entity, attribute or relationship

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Acarrier · Accepted solution]

Hello Evelyn,

THanks again for your input, it is appreciated. 

The object in question is a view, a union of other tables/joins. 

I just solve my issue. I ended up using a pipelined function. In oracle a pipelined function is a function that acts like a table. You can make a view off of a pipelined function. This is what I did. Somehow this solved the issue. I cannot explain how this solved it and why the original view was a problem. I tried many thinks. My view was a summary. I tried a view that was all the detail rows. The same fields were wrong as well. 

I am at a loss, but I found a reasonable workaround. 

Thanks!

[Anonymous]

Hi @Acarrier ,

To my knowledge, values will not be automatically summed based on any columns in Power Query.

So It seems that advanced query is added in Advanced options when connect to database or you have applied some transformations( such as Groupby Date) to the table in Power Query.

Please check them.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Acarrier]

Hello Evelyn, 

THanks for your reply. 

I have verified the parameters. They are ok. See the screenshots below.

- I tried a new oracle view that includes a key field (varchar) and I specified that that is the key field in the model area. 

- Checked in power query for additional steps. 

- Traced the oracle connection. The query sent to oracle is not grouped by.

When I reload in PowerBI I get for example 1007 rows fetched, which is the count you get AFTER summarization by date. 

I do not understand where this summarization is done! Perhaps a driver issue somewhere???

I think my next step is to load the detail rows and have powerbi Summarise per day and shift. I am at a loss to understand.

Thank you again!