The Date Dimension – A MUST HAVE in all Reporting Environments

Bryan Townsend

Business Analytics Specialist - Data Warehousing, Modeling

As a report developer, how many times have you been stuck creating reports in a Business Intelligence environment without a date dimension? If your answer is only once, I can tell you from experience that even once is too many times. If you are consistently in that situation, then you may want to befriend the DBA and plead your case to have them implement a date dimension.

Date dimensions are common in BI implementations that have a data warehouse. They are not as common in BI environments where reporting occurs directly off of the transactional system(s). Regardless of whether you’re reporting off of a data warehouse, or a transactional system, I would argue that date dimensions are a MUST HAVE in all reporting environments for the following two reasons:

• Enhanced query performance - All fact tables in a reporting environment contain one or more dates that can be linked to date dimensions. By tying these fact tables to a date dimension, report developers can utilize time period attributes which results in more efficient queries sent to the database.
• Increased efficiency for report development - Most if not all reports reference a time period such as Month, Year, or Prior Year. Using attributes from a date dimension make reporting on time periods easier, which results in faster developing rather than having to code logic for these time periods.

Those two points alone make date dimensions the most commonly used dimensions in Business Intelligence implementations. The following will discuss the structure, the implementation process, and benefits of having a date dimension in your reporting environment.

Structure

The granularity of the date dimension is day. It consists of a record for each day for a timeframe defined by the business that will be sufficient to handle their reporting needs. Date dimensions have dates that go well into the future. For instance, a date dimension can start on 1/1/2000 and end 12/31/2030. This range will vary from business to business.

Date dimensions consist of a key and many attributes. The key is usually an integer value that is unique for each record, and is used to join the dimension to fact tables. For example, the key for 12/01/2014 would be 20141201. The many attributes of a date dimension are what makes them so powerful. Many companies use their fiscal calendar for reporting. Because of that, most date dimensions will contain attributes for both calendar year and fiscal year. Below is a list of common attributes found in a date dimension.

Common Attributes	Additional Attributes
Date	Calendar_Weekday
Calendar_Year	Calendar_Day_Of_Week
Calendar_Qtr	Calendar_DOM
Calendar_Month	Week_Ending_Date
Calendar_Week_Of_Year	Working_Day_Of_Month
Calendar_Day_Of_Year	Working_Days_In_Month
Fiscal_Year
Fiscal_Qtr
Fiscal_Period
Fiscal_Week_Of_Year
Fiscal_Day_Of_Year

Populating

There are several methods for loading data into date dimensions. Since most date dimensions are typically loaded once to contain all the attributes, past dates, and future dates, any of the options discussed below are valid. They all may take an afternoon to put together, but they all accomplish the same task. These options are:

• Build the date dimension manually in a spreadsheet, and have that loaded into a database table. This method would be for those that are less technical, but have a great understanding of what attributes need to be in the date dimension. If you’re loading 30 years of dates, you’ll have over 10,000 records to manage in this spreadsheet. Dealing with that volume manually may make this method slightly more cumbersome than the next two methods. It may be tougher to maintain as well if there is a need to add more years. Once all the keys and attributes are in the spreadsheet, have your Database Administrator import that data into a database table where it can be used for report development.
• Create a spreadsheet with formulas that calculate the attributes, and have that loaded into the table. Reference the spreadsheet to see this method. This option can be used by those savvy excel users that have the ability to write complex formulas. Once the correct formula is created for a cell, that formula can be copied across all date cells. For example, if your Fiscal Year starts on Oct 1^st, you can use the formula below to calculate the period number. Many of the Calendar Year attributes can be achieved by referencing the date column, and adding some formatting. Another benefit of this option is that a quick search on the internet for creating any of the attributes in a spreadsheet will most likely return several ways to create the formula. When the spreadsheet is complete, your DBA will have to import that into a table.

          =IF((MONTH(A2)+3)>12,(MONTH(A2)+3)-12,(MONTH(A2)+3)) 
          Note: A2 is the date column in this example

• Write a SQL statement that will populate the date dimension. This method will require knowledge of writing SQL statements, and for you to have access to your database environment. This option is the most complex of the three methods, but it is also the most effective. It is also the easiest to maintain. Because this task is accomplished in the database environment there is no need for an import step as there is with spreadsheets. Once the SQL statement is completed, it’s as easy as executing the statement to populate the date dimension. Similar to the previous method, a simple search on the internet can reward you with many SQL statements that can act as a great starting point.

Specialty Attributes

Unlike the other attributes that are originally loaded with the date dimension and remain static, these specialty attributes need to be updated daily. Specialty attributes can consist of Yes/No Flags, and Relative Time Periods or Lag Periods.

• Yes/No Flags can be created to identify specific Period to Date records. For example, if we are two days into a Period, the Period_To_Date_Flag would be “Y” for the first 2 days of the current period, and “N” for all other records for that column.
• Relative Time Periods or Lag Periods are another common specialty attribute. Using Relative Time Periods allow report developers to easily identify current periods, and prior periods. Using Relative_Calendar_Day as an example, 0 would represent current day, -1 represents prior day, -2 identifies two days ago, +1 identifies tomorrow. All the Relative Time Periods have the same representation so for Relative_Fiscal_Year, 0 is current fiscal year, -1 is prior fiscal year, and +1 is next year. Below is a list of several common specialty attributes.

Specialty Attributes

Year_To_Date_Flag

Prior_Year_To_Date_Flag

Period_To_Date_Flag

Prior_Year_Period_To_Date_Flag

Relative_Calendar_Day

Relative_Calendar_Week

Relative_Calendar_Month

Relative_Fiscal_Period

Relative_Calendar_Year

Relative_Fiscal_Year

By updating these specialty attributes daily, report developers can easily make their reports dynamic. If there is a requirement for a report that contains sales for current period, the report developer can easily identify that, and make the report dynamic by using the filter Relative_Fiscal_Period = 0.

Reporting Benefits

The main benefit of having a date dimension is that it greatly simplifies the report development process. Report developers know that the majority, if not all, of report requirements are based on a specific timeframe, and more often than not the report needs to update dynamically when that timeframe changes. Accomplishing reports with specific timeframes is certainly possible without a date dimension, but the logic to do this is much more complex. Most reporting environments have beginner, intermediate, and advanced report developers. With the added complexity advanced report writers would be forced to develop all reports with these timeframe requirements. Having a date dimension allows beginner, and intermediate report writers to handle these requirements as well. Let’s look at the report requirement below, and discuss how this would be achieved with and without a date dimension.

Report Requirement:

Compare the current period to date Sales Dollars to the Sales Dollars for the same period to date last year. The criteria specifies that when the period changes, the report should dynamically update with the current period. 

With a Date Dimension

Logic for Current Period to Date Sales Dollar would look something like this:

CASE

   WHEN Relative_Fiscal_Period = 0

   THEN Sales_Dollars

   ELSE 0

END

Logic for Last Year Same Period to Date Sales Dollar would look something like this:

CASE

   WHEN Relative_Fiscal_Period = -12 AND Prior_Year_Period_To_Date_Flag = ‘Y’

   THEN Sales_Dollars

   ELSE 0

END

Without a Date Dimension

Good Luck with that!

ALL reporting environments should have a Date Dimension. There are too many benefits that they bring to BI implementations, and those benefits cannot be ignored.

A Report Developer's Guide to Sanity

Bryan Scheck

Business Analytics Specialist

IBM Cognos Report Studio is a tremendously flexible and useful application for querying and presenting data in a multitude of forms. From crosstabs to graphs, details or summaries, sales reporting to financial statutory reporting, Report Studio is both powerful and versatile.

Due to its expansive feature set and extensibility, every report developer will have their own approach to development. When inevitably under deadlines, even the most experienced developer is pressured to skip important steps that may seem unnecessary at the time. 

Developing a consistent and disciplined methodology around the creation and modification of reports is a habit that can actually save time and effort. Consider the following suggestions when developing new reports or modifying others to make the most of your time and to avoid the frustration that comes with modifications and requirement changes.

#/* Comment */#

Liberally use the #/* comment */# syntax to internally document formulas, filters and other particulars of your report development. These can prove invaluable to recall changes when returning to a report after a period of stability. Commenting out a previously used formula, dating changes or even noting the business user requesting the change can be useful inclusions.

Version your work

Which copy of your work is current? Unless you have a robust version control system in place, this question causes headaches. Versioning your report copies with the date as shown below has a couple of distinct and concrete advantages.

     Report Name vYYYYMMDD

     Example: Financial Statement v20141122

• Firstly, there's never a question of which version is current. The most recently dated version is the current one. This naming and date convention also sorts reports appropriately within Cognos Connection. 

• Secondly, previous known good versions exist to which you can revert in the event of irreconcilable errors. I typically have an archive folder to which I immediately move old versions in case I need to revert.

• Finally, and perhaps most importantly, habitually creating a well-labeled new version when presented with new requirements establishes an implicit release process. The newest appropriately named copy becomes your current 'Development' version.  When it's ready to test it can be moved to the test package or other user accessible location for validation and verification. Once this 'Test' version is approved, the 'Production' version can simply be a view that is repointed to the final validated and approved copy of the current version. (This view is essentially where the user always goes to for their most up to date version. The name or location of this never has to change, providing a consistent user experience.)

Organize and label reports and objects.  

While elaborated on by my colleague in a previous post, it’s worth mentioning again. If report objects are well labeled and kept orderly, they will be easier to locate and change. This is especially true as none of us are working on just a single report at a time! Returning to a well-organized report saves the mental switching costs of re-familiarization.

• Clearly label query items as they're created. Make use of the query item 'Label' property by which to create report output labels.

• Use mock query items. Create placeholder query items (with expression value of 0 or some other neutral value) that are used solely for organizing the query data. Visual landmarks are created, making the job of quickly identifying groups of query items easier. 

• Use mock filter items. The above technique can also be applied to filters. Well labeled filters allow quick navigation and will make the job of modifying your work, either by you or others, much more straightforward. How many times have you disabled a filter only to forget whether it was originally required or optional?!

Copy and paste the following within a query in Report Studio to get the mock filters shown in the image above.

        
[CLIP - Report Studio FilterSections.txt]

          

• Organize reports. One axiom I adhere to relentlessly is to place all reports within the Framework Manager package upon which they're based. I find it useful to have a 'Development', 'Test' and 'Production' version of each package for these respective purposes. If the users need the reports to be accessible from alternate locations, views are employed. This approach disconnects the 'Presentation' of the reports from the release cycle and needed organization. It undeniably adds time when moving a report through the release cycle; however the benefits of stable testing and insulating users from changes are invaluable. (This multiple phased package approach is also useful as Framework Manager will also automatically document the last published date for each, helping keep package versions under control as well. If you are subject to someone else's organization of the packages, try employing your own report folders which correspond to report packages.)

Document report changes

One of the first things I do when creating a new report is to create a documentation page within the report to record feature requests and requirements changes. Report studio isn't intended to be a word processor, but creating a blank page with a table full of empty Text Items can be used for documenting relevant project dates, feature requests and key stakeholders, as well as serving as a change log for report versions.

Copy and paste the following text into the Page Explorer in Report Studio to include a change log template.

         

[CLIP - Change Log.txt]

          

Of course you don't want your documentation page to render along with the report. Use a Boolean variable to control its display. I typically create a 'Do Not Render' variable with an expression of 0=1 which always renders false and will never render under any condition. I am able to view the content in the documentation page, while report consumers cannot.

While these tips are not entirely comprehensive, they hopefully represent some guideposts that can keep your development cycles clean and humming without the added noise of rework and expensive mental switching costs.  And maybe - just maybe - they can keep you sane in light of ever changing business requirements!

Documentation Tips for Report Studio

Erin Dunmire

Business Analytics Specialist

Have you ever opened an existing report in Report Studio and blundered through the many components of the layout and queries wondering where the best place is to start trying to understand how it all comes together to form the output? If you’re a report developer, I’ll bet that you have. Report Studio is amazing in its capabilities, but the vast functionality can be a hindrance for developers having to maintain complex reports. Read on for tips to help document Report Studio reports so that the amount of time getting up to speed on the functionality of a report doesn’t take longer than the updating of the report. These tips are helpful for teams – so that other members of a BI team can gain a quick understanding of the report functionality – and even for yourself – so that the functionality you designed into a report comes back more quickly when you are asked to make modifications a year later.

Give objects meaningful names

You may be thinking, “Of course objects should have meaningful names, I don’t need to read a blog to know that!” Yes, of course we report developers know this, so let’s use this tip as a reminder to actually do it, rather than just know it. Most environments have their share of reports containing multiple data items named Data Item1, Data Item2, Data Item3 and multiple queries named Query1, Query2, Query3, Test Query, Prompt Query1, Prompt Query 2, Detail1, Detail2, Detail3. You may know what’s in that query or data item or on a page when you’re developing, but those generic names will not help you navigate the report the next time you are in it.

Meaningful names prove even more useful when there are multiple items to differentiate. Renaming Page1 to something indicative of its purpose is most useful when there is more than one page in a report, so that it is helpful to developers to quickly identify what is on each page. My point is, don’t kill yourself renaming pages on every one-page report – Spend your time naming items meaningfully where it will add value.

Documenting Queries

For reports that have only one query feeding the layout of the report, you can consistently name that query something like “Report”, and position it at the top of the queries, as anyone coming into an existing report will likely start here to gain an understanding of the report. For reports that have multiple queries in the layout, you can name the queries based on the report page names to quickly piece together how they’re linked.

Empty queries can be placed among the data queries to group and separate related data queries. For each empty query being used as an organizational tool, set the Name property of the query to a meaningful description for the group, surrounded by a repetitive filler so that the organizational queries are set apart from all of the data queries.

Names given to prompt queries should indicate that the query is used specifically for prompt information. When prompt queries are separated as indicated in the screenshot below, you may think prefixing the prompt query name with Prompt_ isn’t necessary, but you will find it useful to help you easily navigate to the correct option when selecting a query from the drop-down in the Properties pane of items like prompts and data containers.

Documenting Filters

Filters can be grouped using disabled filters. Insert a filter, add descriptive text to the expression surrounded by a repetitive separator, and then set it to Disabled.

Documenting Data Items

Data items can be grouped using dummy data items. Add a data item from the Toolbox tab, give it a descriptive name surrounded by a repetitive separator, and set the expression to something like a blank string or a zero. Insert as many dummy data items as necessary to group, separate and label the report data items. 

More Detailed Documentation

Text can be added to the expression box of a data item or filter within the characters #/* and */#. This does not affect the expression. Adding comments in this nature can be used to document specific calculations, or to add descriptions or background information to specific items.

Developer Notes

Adding an additional page to a report and setting it not to render is a great way to maintain a change log within a report. This can be incredibly helpful when reports are maintained by multiple developers. Modifications made by each developer can be documented by entries on the “developer notes” page.

Your BI team could greatly benefit from these Report Studio documentation tips.  Although documentation can be painful at the time of implementation, it is priceless when you delve back into a report months later. You will surely be grateful for it!