Build and Use Business Dates Tables
- Kavitha Thangaswamy (Unlicensed)
- Pamela Totas (Deactivated)
You can build a Business Dates table in the Data Mart schema so you do not have to reference the Eagle Business Calendar table when using Data Mart. You link the business calendar data in the Business Dates table to the Data Mart snapshot you use in building data, so that regular data sources and business calendar sources can be coordinated in a convenient way.
Business Dates Table Fields
The Business Dates table is similar to the Eagle Business Calendar table, except that Snapshot_ID instead of business calendar source is a field. Since the Snapshot_ID points to the business calendar source of your choice, you can combine business calendar selection with the source rule you choose for a Snapshot so multiple calendars can be supported.
The Business Dates table contains the following fields.
Options | Description |
|---|---|
Effective_date | Specifies a date to use as the As Of date when you build the Business Dates table, either alone or within a build of other tables. This date may or may not be a valid business date according to the business calendar in use as you build data. |
Snapshot_ID | Specifies the ID of a Data Mart snapshot used when you build the Business Dates table. This may be the DEFAULT snapshot if you did not choose a snapshot that you set up for special purposes. Every snapshot is associated with a business calendar that tells where the business dates information comes from in the Eagle Business Calendar table associated with the snapshot in use. The DEFAULT snapshot is linked to the EAGLE PACE™ business calendar (source interface instance = 4). Different snapshots may share the same business calendar source without restriction. |
Day_flag | A value of 1 if the effective_date is a business date for the associated business calendar. Otherwise, the value is 0. |
Month_end_flag | A value of 1 if the effective_date is a monthend date for the associated business calendar, else 0. |
Quarter_end_flag | A value of 1 if the effective_date is a quarter-end date for the associated business calendar, else 0. |
Year_end_flag | A value of 1 if the effective_date is a yearend date for the associated business calendar, else 0. |
Week_end_flag | A value of 1 if the effective_date is a weekend date for the associated business calendar. Otherwise, the value is 0. |
About Business Dates Table
If you refer to the Business Dates table in the SQL code that you use to establish your Data Mart database query for reporting, you can restrict the rows returned to those that are valid business dates, weekends, monthends, quarter-ends, or yearends according to the Eagle business calendar you select.
The following figure displays an example of a query for market value as of business monthends during the three-month period between January 1 and April 1, 2007. The SQL "select" statement in the example requires that the Business Dates table month_end_flag be 1, indicating a business monthend. The Eagle business calendar in use is the one that has been linked in Data Mart to the Snapshot "default".
The Business Dates table joins to Fund Master on effective_date and snapshot_id, but not on the dmart_fund_id field that links most Data Mart tables of a given entity, effective date and snapshot. Like the Security Details and Issuer Details tables in Data Mart, Business Dates is not related to any particular entity, and so it does not pertain to a single dmart_fund_id, but rather to all of them sharing the same effective date and snapshot.
The Business Dates table is useful for "picking out" monthly, quarterly or annual data observations from a range of daily Mart data, then presenting the selected values as a time series table or chart.
The table can also save you large amounts of computer time and data storage if you repeatedly report certain unchanging data values such as rates of return as of several past yearends. Without the Business Dates table you may have to re-compute and re-commit such values every month or even every day because that makes them easy to retrieve. But this is very wasteful of time and space – ideally you would compute and commit each yearend return only once like any other daily value, in the row pertaining to its effective date. But then, selecting several yearend values together would require reference to each individual date in your SQL. By adding the restriction that the Business Dates yearend flag be 1, you can simply reference the overall multi-year date range in your SQL and retrieve all of the annual values. The date range can be established by parameters supplied by the user at run-time so that no fixed dates have to appear in your SQL at all.
Set Up the Business Dates Table
The Business Dates table is the simplest table in Data Mart. It is not related to funds, and has a fixed pre-configured set of fields, as shown in the Business Dates field table. You can assign the field to schedules you have set up, add a comment, and control access to the model using the Access tab.
You can assign the Business Dates table only to Data Mart Schedules of "Build All" type. This is because you may not build the fields of Business Dates selectively, since there would never be a reason to do so.
Build the Business Dates Table
You usually build the Business Dates table daily for a single effective date at a time, along with your other Data Mart models. Once you build a row in this table, you usually do not make corrections or updates since business calendars, the source data for this table, are very stable.
The difference between building Business Dates and other tables relates to how you build values for a range of dates, such as when you backfill to build history. Most models require a date-range submission in the ad hoc Submit dialog box. But this is a very inefficient way to build the simple Business Dates table. When you submit Business Dates for an effective date and date rule you establish a beginning and ending date. The result is that all dates between these two (inclusive) are built. By submitting Business dates as of December 31, 2012 with a date rule of "Current Year", you very quickly build rows in the table for every calendar date between January 1, 2012 and December 31, 2012.
The system prevents you from accidentally submitting Business Dates for a date range. If you build Business Dates along with other models, you may not use a date range. If you build All Models for a date range, Business Dates are not built.
Selective Fields does not apply to Business Dates. You cannot selectively build fields in this table, though you can selectively build fields in other tables while also building Business Dates for the same (single) date.
Execution Logs and the Business Dates Table
The Data Mart Model Manager engine coordinates building the Business Dates table. There is no OLAP report as in the case of other data, so there is no second level of drilldown in Execution Logs for Business Dates, and no ERX file of results.
Use the Filter in Execution Logs to specify the Business Dates model to find builds in which it was involved.
View Mart Data for the Business Dates Table
To view Mart Data for the Business Dates table:
In Reporting Center, from the left navigation, select Reporting Tools > Datamart > Manage Marts > Data Mart.
You see the Data Mart window.Click View Mart Data icon.
Select Business Dates from the Data Mart Model dropdown.
In the Date dropdown, specify an effective date.
In the Snapshot dropdown, indicate the snapshot id.
Click Query.
Rows of the Business Dates table appear in the Business Dates Details section. All rows of the Business Dates table going backward in time from your selected date to the earliest date in the table for that snapshot id appear.
The Funds and Currency selection boxes disappear since funds and currencies do not determine business dates.Click the Effective Date column heading to sort on date in either ascending or descending order.