通过

Creating a Fiscal Calendar in BA 4.00

  • 项目

By request I'll briefly run over generating new timedimension in BA. By default BA is not able to create a Fical Calendar that fits the content of the table AccountingPeriodes. Nor is it able to handle calendars from Service. These can however be created manually. It is really simple and requires only editing of the Analysis Services database. It is important to note that changes made this way will not show up in the BA configurator inside Navision and the changes will be overwritten if the configurator is run from inside Navision.

To start editing open Analysis Manager. In the database BA for MBS Navision 400 right click the Shared Dimensions node and choose New Dimension - Wizard as shown below.

Choose Start Schema as the dimension type and click next

In the Select dimension table box select the table wPostingDate that contains a list of dates.

Select Time dimension as the dimension type and make sure that the Date column is set to thedate column.

If you wish to create a Fiscal Calendar where the fiscal year begins in July this is set in the Create time dimension levels box shown below. Valid selections are any month and any given day within that month.

Clicking next will enter the Dimension Editor where advanced settings can be defined and the dimension can be setup to fit more specific needs. Since the dimension at the current point doesn't show a date as the lowest level by a day number this will have to be changed to obtain having a date shown as the lowest level of the dimension. This is done by marking the Day level which is shown in the upper left hand pane and afterwards editing the Member Name Column property in the lower left hand pane.

To have a date shown instead of a number choose DateName and click ok. 

 Clicking data to browse the dimension will render the result below. Save the dimension and process it.

The dimension can now be added to any cube in the database. Given the fact that the table wPostingDate already is added to some cubes in the Default setup no further editing should be necessary for those cubes when adding the new dimension. If the dimension is added to a cube where the table is not part of an existing dimension the join to the fact table should be specified when adding the cube. This should be done by pointing the column TheDate from wPostingDate to the appropriate datetime column of the fact table.

I is important to note that this works as long as no editing of the setup takes place from inside Navision as it overwrites any manual changes made since the last deployment when running.

The optimal solution to creating a Fiscal time dimension would be extracting the starting period end closing period from the AccountingPeriods table. If the interest is there I will do a post on this at a later point in time. Also please let me know if this was of any help and if you would have suggestions on other topics. My next post will be on incremental updates instead of the default full loads supported by BA. Especially I'm interested in the relevance on posts on Reporting Services in conjunction with Navision.

Comments

  • Anonymous
    November 07, 2005
    I would be interested in hearing more about creating a Fiscal time dimension based on the AccountingPeriods table.