DSBI Cube Features – (Object Descriptions)
A Cube is a large collection of related data able to create a variety of reports. The data is re-collected and re-calculated usually every night, with many subtotals and options pre-calculated to make for fast reporting. The complexity that is built into the cube enables the final report creation to be relatively simple. Excel pivot tables is one of several options for displaying the cube data as reports. It is important to realize that the actual data is held within the cubes, not within Excel. Excel pivot table reports and charts provide a window into a selected subset of the data available in the cubes. You will generally want to refresh your Excel reports daily to display the latest cube data.
Measures are the numbers that are being reported. Unless otherwise specified, measures will sum across the dimensions.
Measures appear in Measure Groups at the top of the PivotTable Field List panel. Measure groups are signified by a sigma (∑). The measure group name designates where the measures originate. The name of the first measure group also includes the name of the underlying OLAP cube.
Measure Folders – Calculated Measures
The most commonly-used measures are listed first. Additional measures may be available within folders within the measure groups. These are generally calculated measures, that is, they are calculated at the time the report is run, because the calculation depends on what data is selected. These are measures such as averages, year to date (YTD), and percentages. In some installations with large amounts of data, reports using calculated measures may take noticeably longer to refresh. If the refresh is taking longer than thirty seconds, please contact your database administrator. Modifications to the cube structure can enable particular report configurations to run faster.
“Use With…” Measure Folders
Some calculated measures, such as YTD measures, directly depend on which time periods are selected. The formulas will only work when the specified type of date object (hierarchy or attribute) is incorporated in the report’s rows, columns, or filter.
Calculated Measures Naming Conventions
Note that Month indicates the calendar or fiscal period (companies’ fiscal periods are usually calendar months, though not always). Period refers to the period of time currently selected in the report, such as day, month, quarter, year, week, or season.
Avg = Average of the data selected
Pct = percentage
PTD = Month (fiscal period) To Date; Use a Date attribute for the PTD to roll up to.
QTD = Quarter To Date; Use a Date attribute for the QTD to roll up to. If you select Month, it will roll up from the first day of the quarter to the last day of the selected month.
YTD = Year To Date; Use a Date, Period, or Quarter attribute for it to roll up to.
Prior PTD = month to date as of the same day last month.
Prior QTD = QTD as of the day or month for immediately previous quarter that corresponds to the current date selection
Prior YTD = YTD last year as of the day, month, or quarter that corresponds to the current date selection. Note that if a whole year is selected, Prior YTD will include the entire previous year.
… Growth from Prior … = This time period minus previous time period
… Same Period Prior Year = In this case, “Period” refers to whatever time period level you have selected. If third Quarter one year is selected, this measure will return third quarter of the previous year. It will also work for other levels, such as month or week.
KPI’s, Key Performance Indicators, are financial and non-financial metrics that indicate how a company is (or is not) meeting its goals. DataSelf BI uses graphical representations, such as stop lights or colored arrows, designed to quickly identify trends, goal achievement, or other status measurements. You will probably want to have the default values customized to your company’s exact goals.
Excel pivot tables also allow you to create your own colorful KPI graphics. See: http://blogs.office.com/b/microsoft-excel/archive/2005/12/21/pivottable-vii-conditional-formatting-gets-even-better-or-visualizing-your-data-in-pivottables.aspx
Dimensions are groupings of data, such as Customer, Product, and Date, that are used in the rows, columns, and filters. One dimension, such as Customer, can have many pieces of information, such as the customer’s name, address, last invoice date, and Account Manager (default salesperson).
An attribute is a single piece of information associated with the dimension, such as the customer’s state. In Excel, attributes are displayed in the dimension in folders or in the More fields folder.
Hierarchies contain one or more attributes that can expand down from one level to the next. In Excel’s PivotTable Field List, the most common hierarchies display first within each dimension.
Relative Dates keep your reports current
The internal keys of the Date Document Parts and Date Document hierarchy are re-calculated at every refresh, to be relative to the Last Cube Refresh date. Therefore, if you create a report during January 2013 with Jan-2013 selected (the current month), when you refresh this report during February 2013 it will still display and report on the current month (now Feb-2013).
Most other Date dimensions also use relative keys for their hierarchy and “Date… Parts” attributes.
Fiscal or Calendar Year
DataSelf BI can accommodate one or more fiscal and calendar years. Fiscal year date attributes generally have names that start with FY, whereas CY designates Calendar Year.
Using PTD, QTD, YTD measures with Date Document
The PTD, QTD, and YTD type calculated measures in the Use With Date Document (or similar) folders calculate their value based on the last day of the time period selected.
(movie? About using two different months vs same period prior year, YTD in months vs YTD Prior Year)
See also “Calculated Measures Naming Conventions”
Date Document hierarchy versus Date Document Parts
1. Use the Date Document hierarchy in the Report filter (and in Column Labels when you want to be able to drill down from year to quarter, month and possibly days.)
2. Use a Date Document Parts attributes in Column Labels, and filter on it for the desired dates.
To display all the months in a certain year, put the Year attribute in the Report Filter, and filter for one year. Then put the Period attribute in the columns, but do not filter it.
Date Document Calculated Measures
Any measure can be used with Date Document, including averages, except for date-based measures specifically designated by folder to be used with a different date dimension.
Date-based calculated measures designed to work with the Date Document hierarchy and attributes (“Date Document Parts”) are in the measure-group folders such as _SalesCubeSalesDetails’s Use with Date Document folder.
Rolling periods are relative date ranges that count backward and forward from the current Last Cube Refresh date. This first day of period number +00 is the Last Cube Refresh date. Options are:
Rolling Week (7 day grouping buckets)
Rolling Month (30 day buckets)
Rolling Quarter (90 day buckets)
Rolling Half Year (183 day buckets
Rolling Year (exact years).
Date Document Other folder
Generic Time Periods
The generic time period attributes (in the Date Document dimension’s Other folder) name date periods without being tied into a certain year. This allows year-over-year comparisons, when used in conjunction with the Date Document Parts folder’s Year attribute.
This is most commonly used with a Period Name in the columns and Year in the rows.
Seldom-Used Date Hierarchies and Attributes
This normal date dimension is not relative: it will not change when the report is refreshed.
Found in the Absolute Date folder, Season is an example of how time period reporting can be customized to follow the normal business cycle of a particular business. Calculated measures designed to work with Season are in the measure group SalesCubeSalesDetails’s Other folder.
h5. To-Date Document
Used in large datasets that are experiencing slow report refresh, To-Date provides the ability to rapidly report YTD, QTD, and MTD totals. This is a relative time dimension: it always displays a To-Date as of the
Last Cube Refresh date. Use it with measures such as Amt Sales, or any other measures that are not tied into another date dimension. Calculated measures designed to work with the To-Date hierarchy are in the measure groupSalesCubeSalesDetails’s Other folder.
Other Date dimensions
Other cubes may have one or more date dimensions, such as Due Date (for Receivables and Payables) or Date Expected (for Sales or Purchase Orders). These are usually relative dates.
Last Cube Refresh
A date/time stamp of when the underlying data was last refreshed from the source data. Display this date by placing it in Report Filter and select the date.
“Stacked” Dimensions: Stacked Sales Line Dimension
Using attributes from two or more separate dimensions in the rows in a large data set can lead to slow report performance.
In the Sales Cube, to display sales by customer, salesperson, and/or product within the rows, try using the Stacked Sales Line dimension.
Product Default Filter
Unless otherwise noted, dimensions that are not used in the report do not affect the report. The exception is any dimension that has a “default member”. That is, it will always filter the report in the background unless you specifically change that selection.
In the Sales Cube, the Product Default Filter dimension only allows Inventory Item invoice lines to be included in reports. To include Miscellaneous Charges or other line types, add the Product Default Filter to the pivot table and change the product type selection.