December 24, 2020 at 2:21 pm
Hi all! Your help would be appreciated.
The fundamental issue is that we have 20 different reports that all point at the same structure but need to filter the data depending on the customer needs.
Here is the only caveat: I do not want to exclude loading any of the invoice data into the data warehouse during the ETL. Why?
We have come up with a couple of solutions and am I curious if we are down the right track with any of them:
Change the queries
Alter all the queries for all the reports to exclude the required data. This is high maintenance and as soon as customer 4 comes along and asks to filter on something else, we have to rework all the queries. I am not thrilled with this idea.
Create a View on top of the tables
If we create a view that sits on top of every table and force the reports to query the view then we can control the filtering by customer without necessarily having to retest every single customer installation.
The default installed view over top the invoice table would be: select * from invoice
For customer 2 it would be: select * from invoice where supplier not in (a,b,c) and customer not in (1,2)
For customer 3 it would be: select * from invoice where ponumber not like 'tf%' and invoicekey not in (select invoicekey from invoicelineitem where productcode = "Internal Surcharge")
This would also mean that we could probably change 2 to 3 views and affect many of the reports. Additionally when we do upgrades we would only ever change the underlying structures of the tables and the views would go untouched as they would be customized for each customer.
Flagging the invoices
One of the developers came up with this and it has caught my eye --- I cannot seem to find any examples of this technique being used.
We would add a bit flag to both the invoice and invoice lineitem tables that would represent "include this data in the base reports". A value of 1 would mean include it and a value of 0 would mean exclude it.
We would then need to alter all the standard/base reports to filter the data to only include rows where "include this data in the base reports" is set to 1.
Since all the data is in the DW we can subsequently add more reports at a later time that make use of the excluded data. Those reports could just ignore the flag.
So how does the bit get set? During the ETL process we would have a stored procedure or function that would take in the invoice number and check the data against the constraints of the customer and decide whether to mark the data with a 1 or 0. The benefit of this method is that all the customer specific code is centralized to a very small number of stored procedures or functions. The downside is that if they ever retroactively change their criteria we have some work to do (not a huge deal)
Thoughts? Does anyone have any links they could pass along?
Thanks in advance
Tony
December 24, 2020 at 4:07 pm
Are the customers able to export the data from the reports into something like Excel? If so, then problem solved - push it back on them.
If they CAN'T export to Excel, can they use your reports as a data source for Excel and pull data and filter it how they need?
Alternately, if the customer has the ability to build reports based on your reports, they could build their own reports.
And another option - add the filters into the reports for the fields they want filtered. If the reports are SSRS, the filters can be inserted into the URL, so they could set the filters that they need directly into the report.
Now, I would avoid creating custom queries as it just makes more work for you. I would also avoid making views on top of the tables as again, this makes more work for you. I would be trying to push this off to the customer as much as possible.
Flagging the invoices is a method we use and it is messy and gets complicated quickly. If you want to go that route, our approach was to set the flag in the ETL process. What makes it hard is we ended up with multiple flags. For example, a consumer of the data wanted to see data where item A cost more than what it was sold for. This isn't hard... but then they add in except when it is sold as a 'spare part"... ok,.. not too bad... but show it when it is sold as a spare part to customer C... and the requirements list grew with each iteration of the data we sent out. I think we have over 90 different flags on that data set now.
The other risk with all 3 of the methods you mentioned is it could be that user X at company Y wants to see the data that way. But when User X quits the company and didn't document things well, their replacement MAY not have any clue how the filters were applied to the data and may actually expect to see things that are being filtered out.
TL;DR - I would add the filters as options in the reporting framework. This allows consumers of the data to dictate what they see and does not hide any business logic from the consumers. Hidden business logic WILL be forgotten why it was put in place in the future and it will somehow be your fault that the report was hiding data they needed to see.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
December 24, 2020 at 8:20 pm
Just a thought ...
What about an exclusion table (or set of tables) that you can populate per customer. Then modify your queries to exclude data based on the config table.
As each customer changes their filtering requirement,you simply INS/UPD/DEL the data in the exclusion table(s).
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply