We have 4-5 databases for DEV, TEST and PROD and whenever there is an UAT, user will get confused or whoever refreshing the report, they were not able to find which database currently the universe / report was pointed.
To avoid User confusion and provide clear information to the user, we have come up with solution at universe level and applied the same in all Standard reports.
Steps to get Database information
1. Create “Database Name” as object in universe. When you create Database name object, you can select table as SYS.DUAL. So that it won’t impact anything in your report result.
Note: Max is to avoid multiple values error in the report.
max(‘DATABASE=’|| UPPER (SYS_CONTEXT (‘userenv’, ‘db_name’)))
2. Add the Database Name object into your report as a separate Data Provider or else you include them into your existing data provider. Below is the sample report for separate Data provider for Database Name.
3. Refresh the Database Name Data provider and place the Database Name object into your report footer.
This will give you more flexibility and information about what database data being refreshed and used for the report.