DataRelease after manual quality check

[DilMustafa]

Thinking of deploying a business objects universe for the self serve reporting initiative. One of the requirements is that the user should not be able to see the data in the DW for a date, if the data has not passed a manual approval process. Please, drop your ideas about putting data access control in Business Objects.

[BrianJarrett]

Will the records that have been approved have a flag associated with each row? An 'approved_flag' or something like it? If so, you could just put a stub join on your tables in your universe where this flag exists. That would limit the resultset automatically. A user could, however, if they have permission to see and edit the SQL, get around this. If this is a concern then you could place the stub join on the table using a view so it's filtered before it ever gets to BO.

[DilMustafa]

Hi Brian,

Thanks for the reply. We need to keep the flexibility of turning the flag on and off. Lets say it was turned "on" after checking the quality and after two hours a problem was found (which was overlooked in the orignal check) So the flag will be reverted back to "off". I do see a problem updating 10's of facts and dimensions if the flag is included in each fact and dimension table.

What is your opinion on creating a control table and adding a flag for each date there. Would BO be able to do a join on every single query with this control table for the date checks. OR alternatively I can add this flag into the date dimension, which should make things easy compared to having this flag in a seperate table. Your opinion on this is much appreciated.

Thanks once again,

Dil

[robber]

I would add the flag to the existing date dimension, why add a new table if you can avoid it. Either way you would need to ensure the controls are in place to govern the update of this important attribute.

[BoxesAndLines]

This should be part of your ETL load auditing processes. In any given load for my warehouse, I can easily identify new and changed rows with a load audit identifier. This identifier links back to my persistent audit table which contains among other things, row counts, load counts, start times, end times, etc. Since this table identifies a "unit of work", i.e. a load of the warehouse, this would be the ideal location for a load approved flag.

[DilMustafa]

Thanks for the comments. My main concern is still around Business Objects reading the approved_flad='Y' data based on universe deployemnt for every single self serve report. I would love thoughts and comments BO and the approved_flag='Y' relationship.

[robber]

I'm not a BO guy but in other BI platforms I would edit the physical metadata layer for the date dimension to include the required filter:
ie. select * from dim_date where approved_flag='Y'

If BO does not cater for this then expose the date dimension through a view where you can include the filter.

[DilMustafa]

BUT, how would I control a self serve report where DATE Dimension is not involved at all. Lets say user queries Reseller_NAME and Reseller_ADDRESS from the RESELLER Dimension without involving the DATE Field at all.

Once again thank you everybody for spending time on this thread.

[robber]

If you need the ability to "roll back" changes to dimensions that is a much more difficult task. Daily snapshots, dimension versioning, and log scraping are the options I would explore if I had to but I would first exhaust/explore any and all options to govern these updates in the source systems, ie. do the manual approval process at the source or in a staging area, ODS, etc.

[DilMustafa]

Just found out that BO has way to force a join on every single query. So I can force a date_dimension join even when date_dimension is not part of the self serve query.... Thanks for suggestions and comments. Closing the thread...

[robber]

How will you join a date dimension to a reseller dimension?

[Sponsored content]