Our customers occasionally have information needs that require building queries on top of Union statements, but sometimes it is just impossible to provide exactly what the customer asks for or we just don’t trust the complex process that is happening behind the scenes. This article explains a workaround in cases the native Union functionality is not matching our needs.
Process limitation: Let’s take the Combined Queries feature from the Query Panel in WebI. With the Union functionality we can deal with several sets of information, provided that between the queries that we want to combine, we take the same:
- Number of objects
- Object format type (text, number or date)
The requirement above is a known property inherited from the SQL language.
But a specific limitation may arise because in WebIntelligence the Query Properties are shared among the various Combined Queries, as indicated in the figure below:
Let’s take the example of a customer who reports on a Finance Universe and wants to monitor their NET SALES defined as Gross Sales + (-Returns), and because both pieces of information are located in different tables, they decide to use two combined queries and sum them up via Union. But an issue with the multiple Universe contexts is met: To query Gross Sales a SALES context is used, but to query returns a RETURNS context is used. The outcome is that such an operation cannot be performed correctly because WebI cannot assign different contexts to different combined queries.
Reliability: Another reason can be data check purposes: The Union engine might not be trusted and for simplicity and data testing purposes we would like to query the information in different queries and test them separately, and after do the Union at a report level.
Migration bug: The Upgrade Management Tool of BI4 might fail to convert certain DeskI reports based on Union statements.
The solution proposed reproduces the UNION ALL behavior retrieving data in separate queries and combining them directly in the report. It can be implemented following the steps below:
- Creation of a query for each set of information.
- Use the "Merge Dimensions" feature merging ALL the related dimensions from each query.
- In Properties of the document, tick the “Extend Merged Dimension Values” feature.
- Create attribute variables with the attributes referred to the new related merged dimension.
- Create measure variables showing the related measures coming from the different queries as a sum. In the example stated before we would sum sales and (-returns).
- Create the single table dragging and dropping all the merged dimensions, attribute variables and measure variables that are needed.
- Filter the attributes showing only the ones that are not null.
Conclusion The workaround adds value to the advanced user who cannot use the native Union feature but wants to get the benefits of summing up two sets of data that come in identical format. If you have questions about this method, or if you want to share your experience or tips, please feel free to leave a comment!