Going Global with Data Mart Consolidation, Part 2

Part 1 of this article demonstrated how the security features in DB2 V10.1 could meet several key business requirements for organizations that are consolidating data from multiple countries into a single set of tables:

1. Allow local users to only have access to data for their country
2. Allow regional users to only have access to data for their region
3. Be “locale sensitive” for regional users and automatically convert different countries’ currency values into a common currency (for example, to add the prices of two items using the currencies of Singapore and Hong Kong)
4. Mask column data based on whether or not a user is a local user or regional user

This article builds on Part 1 by demonstrating how global variables and database roles can be used to reduce code complexity and improve usability, as well as dynamically calculate locale-sensitive date/time periods.

Improving usability when specifying date/time values

Global variables can be used to make SQL simpler to understand. Instead of specifying complex formulas as part of a SQL statement, you can create a global variable for that formula and specify the variable instead. Global variables provide a shared common routine that can be used in any SQL—eliminating the need to write the same code again and again. Furthermore, you can use variables to reduce errors in your SQL by creating just one place where the formula is specified.

Global variables support the use of a descriptive name, which helps the user or reader to determine which calculation is being performed. For example, when specifying the first day of the week, one could include the calculation as part of the query:

Or we could create a global variable to improve readability. This allows anyone who views the SQL statement to infer which date calculation the query is performing:

The query can now be written as:

Global variables allow us to specify a “constant” where the name of the global variable represents the operation the business query is performing. These are a few common date/time periods:

1. Today
2. Yesterday
3. Tomorrow
4. First day of the month
5. Last day of the month
6. First day of the week
7. First day of the current quarter

Using global variables, we can represent these time periods in a user-friendly, easy-to-read format. Here are the definitions for the time periods mentioned above.

Today:

CREATE VARIABLE TODAY DATE DEFAULT CURRENT DATE

Yesterday:

CREATE VARIABLE YESTERDAY DATE DEFAULT CURRENT DATE – 1 DAY

Tomorrow:

CREATE VARIABLE TOMORROW DATE DEFAULT CURRENT DATE + 1 DAY

First day of the month:

Last day of the month:

First day of the week:

First day of the current quarter (notice that global variable FIRST_DAY_OF_THE_CURRENT_MONTH is also used as part of the definition):

Automatically calculating locale-specific time periods

So far, we have been calculating date/time values that are not tied to a country’s business rules. To illustrate how to “program” global variables to “behave” based on a particular country, let’s look at a business time duration that all countries use: fiscal year.

Calculating the beginning and end of a country’s fiscal year

The beginning and end of a fiscal year is different for each country. Because of this, any global variable that would calculate these values would need to be country-aware to ensure that the values returned are correct. Table 1 lists the fiscal years for the countries used in Part 1 of this article (in some cases, however, there is more than one fiscal year definition for a country):

To demonstrate how to calculate a country’s fiscal year, we will use the same roles defined in Part 1:

Next, we need to define a few more global variables to represent current year, next year, and last year:

The code below shows the SQL that defines two global variables named START_OF_CURRENT_FISCAL_YEAR and END_OF_CURRENT_FISCAL_YEAR. Notice that the calculation is based on the database role a user has been granted. For the sake of brevity, only Australia and Hong Kong are displayed.

If a user was granted the role AUSTRALIA_ROLE, then the query:

Would resolve to:

As you can see, the values for the current fiscal year reflect the dates for Australia based on the user’s role.

Calculating close of business day

Another example of an industry or country-specific date/time period is close of business day. The definition of this term differs by country, industry, and/or legal requirements. A common example of how close of business day plays a role in our daily lives is one’s bank balance: Often close of business day is used as part of a formula to calculate available balance (funds immediately available) and current balance (all transactions that the bank considers either not yet settled, completed, or in progress).

How would you calculate the close of business day for Argentina, Brazil, and Mexico? If we assume that close of business day is the time that the stock market closes in each of those countries, this example shows how to calculate the close of business day for these countries:

Conclusion

By combining global variables with the row column access features of DB2 10.1, you can develop a secure global data warehouse while also addressing locale-specific requirements. Global variables also help to improve usability and can decrease errors by enabling DBAs to define a formula once rather than requiring users to code the correct calculation in their SQL statements in multiple places.