Category Archives: Universe Design

Best practices, hints and tips of Universe Design

Filtering for Latest Data in a Universe

Summary

This article looks at a reporting requirement where we need to restrict data to the latest data, for example, to only show the latest account balance or the latest action item for work streams in a project.

Our solution is to implement a series of predefined conditions one for each dimension that we need to filter for latest data by.

Continue reading

This article was published on November 16, 2009 by Al Gulland.

Date Objects Every Universe Should Have

A large amount of data analysis will use dates to filter or group results. This article looks at what objects and predefined conditions we should include in your universe that provide rich functionality to report authors that are querying with dates.

Continue reading

This article was published on November 11, 2009 by Al Gulland.

Limitations with using System Date part 1

Often in a reporting or data analysis scenario the analyst or business user wants to view data that is current, for example, sales for the current week or employee turnover for the current month etc. The analyst could create the report and then just enter whatever the current date is or in a operational report the user could enter today’s date when the report however this is obviously not ideal. Furthermore if we are creating a report that is scheduled to run daily then it would need to automatically choose the current date. This issue is easily and we create these reports using functions that return the system date for example SYSDATE in Oracle, getDate() in SQL Server or CurrentDate() in a Web Intelligence report. However although this at first seems an adequate solution there are limitations of using system date functions during the development and testing phases of a BI project.

Typically the development and test systems won’t have current data in their databases. Normally a sample data set is taken from the production or backup database that is, for example, a two month range of data taken from last year. Now any report created in dev or test that uses the system date to filter the data won’t return any data as there is no ‘current’ data in the underlying database.

This article looks at what we can do to allow proper development and testing of reports that will still use the correct system date when released to production. Although the article will use BusinessObjects as the BI application with a SQL Server data mart the techniques described here can apply to other BI Applications and database types.
Continue reading

This article was published on January 27, 2009 by Al Gulland.