Categories
Need suggestion over aggregation of records
Hello Experts,
I need a suggestion regarding a requirement related to OBI.
I have a requirement where
- Against one user_ID there are two customer Name
- Both are the name of same customer (some how in source system it has been entered).
- In OBI actual report is looking like
The requirement is
- Create a single record with User_ID.
- Name of the customer can be any (can be picked randomly out of three records).
-Create a new column which will aggregate against user_ID
Can this be feasible in OBI.
I tried to club it but due to different customer name it is always showing three records with total target.
Kindly guide.
Thanks for your help
Abhi
Answers
-
Sum by....
Sum(YourMeasure By CustomerId)
Simples!
Ideally you want some data cleansing on your source data.
0 -
The best option and the only option in my opinion is to fix the data issues at source. Fixing this in the Analytics layer is just masking the issues you've highlighted with your data. There are things that can be done but that's just "hiding" the data issues. What happens if you uncover further data quality issues?
0 -
Hello Joel,
We have tried to sum by customer_id, but problem is it will give me two records when we pull customer_name in the report.
Its working without customer_name, but client want any single customer_name (eg:- 1 cust_id, 1 cust_name and agg sum).
Actually you are correct that fixing it sourcing side and we have raise the point also, but it will take some time to get it fix in source till the time client is asking for workaround .
Thanks again for help.
Abhi
0 -
You can also do; -
case when CustomerName = 'Whatever' then 'ChangeToThis' else CustomerName end
But this is the thin end of the wedge to a bug riddled poorly performing OBIEE solution, all so someone can pretend that you have good controls on your master data.
My best advice, tell the truth and shame the devil - your OBIEE system will remain simple, clear and performant.
0 -
Hi,
As a work around, could you try by creating BINS (or CASE STATEMENT) for CUSTOMER_NAME column? Obviously, this needs to be done manually and only if customer list is not huge. but that's the ONLY solution to fix this temporarily.
Let me know your results..
Thnx,
Sagar Tippe
0 -
Thank you, I will try all the options mentioned above and let you know the findings.
0 -
Honestly I wouldn't implement any workaround ! If you do, nobody will ever look at fixing the data quality issue.
In the meantime tell your client that it's not your fault if they aren't able to manage a simple thing as a proper list of clients, and all you can do is to give them OBIEE analysis using only the client ID, which is unique, waiting for them to fix data.
Otherwise the 3 rows are the correct answer to the question you asked to the database.
Another workaround: Excel! Tell them to export to Excel and delete make up the name changes by hand and adjust everything else they would dislike in their data ... (This is a Friday kind of answer, in case it wasn't obvious)
0 -
I totally agree with you Gianni that this need to fix from backend source system end.
0 -
I sometimes wish I could write a virus that would corrupt all data that is exported to excel.
Then I remember that there is no need as there are already end users.
0 -
Thank you but do me a favor, if you write such code please post it publicly I will be the first person to implement that code via - save content with HTML Markup code on dashboard and BOOM!!!
0
