There has been a lot of questions from developers about the best strategy to test a universe. In this post, I will talk about how to test a universe once the development/design is completed.
Before you begin testing the universe, it is a good practice to refresh the universe structure. Refreshing the universe structure detects if any columns were added/removed to/from the tables, if any tables were removed from the database or if any tables were renamed in the database.
Testing a universe will need to be done in two phases:
1. Testing metadata
In this phase, you will test the integrity of the entire universe. In other words, you will:
- Test syntax(parse) of all the objects in the universe
- Test syntax of all the joins in the universe
- Test syntax of all the predefined conditions in the universe
- Make sure that there are no loops
- Make sure that there are no fan/chasm traps
- Make sure there are no Isolated tables; which means that each table is added to atleast one context(if there are contexts defined in the universe)
- Make sure that there are no loops within contexts(if there are contexts defined in the universe)
Besides what has been listed above, you will have to make sure that for each measure object, there is an appropriate aggregate function defined in the select clause and an appropriate projection defined on the properties tab of the object. Though this is not directly related to testing, it will help you enhance the performance of the reports by pushing the pain of aggregating data down to the database server. The projection setting will display data in reports at the "appropriate" level of detail.
2. Testing data
In this phase, you will test the actual data that will be extracted from the database using the objects, joins etc defined in the universe. This is a bit tricky when compared to testing the metadata of the universe. Here are a couple of methods that I use to test the actual data:
Unit Testing or System Testing:
Create reports on top of the universe and verify the numbers against already existing reports. Create adhoc reports using the universe by dragging and dropping objects into the query panel(WebI or DeskI). Take a look at the generated SQL and see if it makes sense, especially check whether the joins are defined as per the data model. Create enough reports so that all the objects in the universe are tested.
User Acceptance Testing:
Let the business users create adhoc reports using the universe and verify the data. Please be sure to include all the users from whom you got the requirements for the universe. This is probably the best method in my opinion. Users are well aware of the data and this way you can also get their sign off.