LeaderBoard

Customize cubes [AX 2012]

This post related to this post [Customizing the prebuilt BI solution]

Figure below shows the process for updating a cube. This post walk through each step in detail.

image

Choose the project to update

The first step is selecting the project to modify. You can select an SSAS project in the AOT or a project maintained on disk. The wizard performs basic validation of the selected project before you can proceed.
The update process is designed to ensure that you end up with a project that you can
deploy and process without any errors. If the selected project does not build (the most basic measure of validity), the wizard will not let you proceed to the next step.

Select metadata
Next, you select the Microsoft Dynamics AX metadata that you want to include or exclude, as shown in Figure below. The metadata that is defined in the Perspectives node in the AOT is the source of metadata for the prebuilt BI solution. By including or excluding metadata definitions, you can include (or exclude) measures, dimensions, and even cubes

image

For example, if you remove the Accounts Receivable perspective from the selection, the Accounts Receivable cube will be removed from the project that you are updating. If you model a new perspective in the AOT and include it in the project, the corresponding measures and dimensions will be created and added to the SSAS project.

For a description of metadata definitions and the resulting analytic artifacts, see “Defining Cubes in Microsoft Dynamics AX” at http://msdn.microsoft.com/en-us/library/cc615265.aspx. Metadata will be covered later in post with title “Creating cubes”.

Select financial dimensions
On the next wizard page, you are prompted to select the Microsoft Dynamics AX financial dimensions to include in the project, as shown in Figure below

image

Each financial dimension that you select is added as an OLAP dimension with the same name.
If a dimension by that name already exists within the SSAS project, the system will disambiguate the newly added dimension by adding a suffix.


Select calendars
Next, the wizard prompts you to select the calendars to include as date dimensions, as shown in Figure below. If you have defined any additional calendars, you can include them in the project at this point.

image

In Microsoft Dynamics AX 2009, the prebuilt analysis project included two date dimensions: a
Gregorian calendar–based dimension called DATE and a fiscal calendar–based dimension called FISCALPERIODDATEDIMENSION. If you wanted to include additional date dimensions, you would have had to customize the prebuilt project by using Business Intelligence Development Studio.

Microsoft Dynamics AX 2012 includes a utility called Date Dimensions that lets  you define custom calendars for analysis purposes. A default calendar, Date, is included with the product, and you can define additional calendars by using Date Dimensions.

For each calendar that you add on this wizard page, the system creates a date dimension in the SSAS project. For example, if you added a new calendar called Sales Calendar, the system will add a date dimension called Sales Calendar. In addition, the system will create role-playing date dimensions that correspond to each of the dates that are present in cubes. You can’t remove the prebuilt date dimension from the project.

You can start Date Dimensions directly from the SQL Server Analysis Services Project Wizard, or from the System Administration area page.

You can define a calendar by selecting the beginning of the year and the first day of the week.
For example, for the Sales calendar, the year starts on April 1 and ends on March 31, and the week starts on Sunday. You can enter a date range to specify the calendar records that you want the system to populate in advance. You can also select the hierarchies that will be created for each calendar.

When you close the form, if you added or modified calendars, the system will populate dates
according to the new parameters that you defined. In addition, the system will add the required
translations. As you will notice later, the system adds a rich set of attributes for each calendar defined here. You can use any of these attributes to slice the data contained in cubes.

In addition, Date Dimensions adds a NULL date record (1/1/1900) and a DATEMAX date record
(31/12/2154) to each calendar, so that fact records that contain a NULL date or the DATEMAX date will be linked to these extra records, preventing an “unknown member” error from occurring during cube processing.

Select languages

The prebuilt SSAS project uses EN-US as the default language. However, you might have sites in other countries/regions and want the users there to be able to view measure and dimension names in their own languages.

The project can include additional languages through a feature in SSAS called Translations. The
Translations feature enables dimensions, measures, many other kinds of metadata, and data to be translated to other languages by letting you add companion text in other languages.

For example, if you add German translations to the project, when a German user views data in a cube by using, for example, Microsoft Excel, data labels are displayed in German.

The prebuilt SSAS project does not include translated strings. However, translated labels are
already available in the system. The SQL Server Analysis Services Project Wizard lets you add any of the required languages to the project by using existing translations from within Microsoft Dynamics AX, as shown in Figure below.

image

It is recommended that you add only the translations that you need. Each translation adds strings to your project, and the size of the project increases by a few megabytes each time you add a language. In addition, processing gets a bit slower and the size of the backup increases.

If you have the Standard edition of SQL Server 2005 or SQL Server 2008, you could not add
additional translations (for Microsoft Dynamics AX 2009). You had to buy the Enterprise edition of SQL Server in order to add translations to cubes. This restriction has been removed in SQL Server 2008 R2 and later versions.

Labels associated with Microsoft Dynamics AX tables and views are carried through to the
corresponding dimensions and measures. It is also possible to add specific labels to dimensions and measures by defining the labels in perspectives.

If you manually add translations to the project in Business Intelligence Development Studio,
the wizard overwrites the labels every time you run the Update function, by sourcing labels from
Microsoft Dynamics AX. To add your own translations, either define a new label and associate it with the object or change the translation in Microsoft Dynamics AX by using Microsoft Dynamics AX Label Editor.

Add support for currency conversion
The prebuilt SSAS project contains the logic to convert measures that are based on the Microsoft Dynamics AX extended data type (EDT), AmountMST, to other Microsoft Dynamics AX currencies. For example, if the amount was recorded in USD, you can display the value of the amount in GBP or EUR by using the analysis currency dimension to slice the amount.
If you want to, you can exclude currency conversions by clearing the check box on the wizard page shown in Figure below

image

Note Removing support for currency conversion not only removes this feature but might
also cause prebuilt reports to fail, because they rely on the currency conversion option to
be displayed in Role Centers.

Confirm your changes
When you click Next on the Add Currency Conversion page, the wizard goes to work, performing the following tasks:
■ Generates a new project based on the perspectives and other options that you have chosen.
■ Compares the newly generated project with the project you wanted to update.
■ Displays the differences between the new project (that is, the changes you want to apply)
and the old project, as shown in Figure below

image
In the wizard, it is assumed that you want to confirm all changes; therefore, all changes are
selected by default. If you want the wizard to apply all changes, click Next, and then the wizard will create a project that includes the changes that you selected.

However, if you are an experienced BI developer and want more granular control of the Update
option, you can examine the updates in detail and accept or reject the changes.
Be aware, however, that making changes to the wizard at a granular level may result in
inconsistencies within the analysis project. If such inconsistencies result in a project that does not build, the wizard displays a message to inform you.

Here are some examples of when you might want to evaluate changes individually:

■ You might have removed some perspectives from the generation process (for example, you
have not implemented Project Accounting functionality in Microsoft Dynamics AX and are
therefore not interested in the Project Accounting cube). Ordinarily, the system would remove
the resulting analytic artifacts, including a dimension. However, you may want to use that
dimension in analysis, even if the Project Accounting cube is not used. Therefore, you reject
the deletion of that dimension.
■ You have added extra attributes to the customer dimension by using Business Intelligence
Development Studio. The system would ordinarily delete these extra attributes, because they
are not associated with Microsoft Dynamics AX metadata. However, you may want to reject
the deletion and keep these extra attributes intact.

Save the updated project
Next, the wizard applies the changes you specified in the previous step. If you simply clicked Next (that is, you did not make any changes to the options selected by the wizard), the wizard would save the resulting project.
If you made changes and the wizard encountered inconsistencies (that is, the project is in an error state and does not build), it displays a warning asking whether you want to save the project or go back to the confirmation step and reconsider the changes.
If you choose to save the project in an inconsistent state (if you are an experienced BI developer, you might choose this approach), you must fix the project by using Business Intelligence Development Studio; otherwise, subsequent deployment steps will be unsuccessful.

Deploy and process cubes
Next, you can deploy the cubes to an SSAS server and, optionally, process the cubes. As discussed in the ”Deploy cubes” in another post before “ Customizing the prebuilt BI solution”, in a multiple-partition environment in Microsoft Dynamics AX 2012 R2, the system will deploy the project to multiple SSAS databases.

Customizing the prebuilt BI solution

As you have seen in the previous post, it’s relatively easy to implement the prebuilt BI solution in Microsoft Dynamics AX 2012. But regardless of how good the prebuilt BI solution is, you may want to change the functionality to suit your needs.
These changes can be divided into three broad categories:■ Configuration Although the prebuilt BI solution is designed to cover all of the functionality in Microsoft Dynamics AX 2012, you may have implemented only certain modules. Even within those modules, you may have chosen to disable certain functionality. In Microsoft Dynamics AX, license codes and configuration keys govern the availability of modules and functionality, respectively. (For more information, see Chapter 11, “Security, licensing, and configuration.”) Configuration keys correspond to functionality within modules. They can be enabled or
disabled.
If you do not activate certain license codes or if you disable certain configuration keys, the Microsoft Dynamics AX user interface configures itself by removing content that is associated with those elements. In this case, you may need to remove the corresponding analytic content. (However, because the prebuilt BI solution draws data from across Microsoft Dynamics AX, this content will not be hydrated with data in any case.) You can use the SQL
Server Analysis Services Project Wizard to remove the corresponding content from the prebuilt cubes, so that you do not have to remove the irrelevant content manually yourself.
■ Customization You might want to add additional calendars and financial dimensions, and also new attributes and measures, to the prebuilt cubes. The SQL Server Analysis Services Project Wizard lets you perform the most frequent customizations with a step-by-step approach, without requiring BI development skills.
■ Extension At some point, you may want to develop extensions to prebuilt cubes by using the SQL Server BI development tools. Table below lists categories of customizations, summarizes the types of changes that you can make, and lists the skill level, time, and tools required to make those types of changes.
image
Configure analytic contentAs previously explained, you can configure the predefined analytic content to reflect configuration changes in Microsoft Dynamics AX in a matter of minutes by using the SQL Server Analysis Services Project Wizard. In Microsoft Dynamics AX 2009, this process had to be performed manually. This process required BI development skills and a day or two of spare time. Microsoft Dynamics AX 2012 dramatically simplifies this process by introducing the following three improvements:
■ Static schema Historically, Microsoft Dynamics AX has had a schema whose shape changed depending on licenses and configuration keys. That is, when a configuration key was turned off, the database synchronization process dropped tables and data that were deemed invalid.
This caused prebuilt cubes (that rely on a static schema in the underlying database) to break at processing time. Unlike its predecessor, Microsoft Dynamics AX 2012 has a static schema.
So, when configuration keys are disabled, the database schema no longer changes. This means that prebuilt cubes can continue to be processed without generating errors. (They will, for example, contain empty measures, because the corresponding tables have no data).
■ Improved modeling capabilities in the AOT The Microsoft Dynamics AX 2009 OLAP framework did not allow advanced modeling of constructs in the AOT. As a result, developers had to implement any functionality that was lacking directly in an SSAS project. In Microsoft Dynamics AX 2012, a larger portion of analytic content is modeled in the AOT. Therefore, configuring the content can be done much more easily by the framework.
■ Wizard-driven user interface The six different forms that were necessary in Microsoft Dynamics
AX 2009 have been replaced by a single step-by-step wizard that guides you through various activities.
To configure the prebuilt BI project, you must have developer privileges in Microsoft Dynamics AX.
This step modifies the project so that irrelevant measures, dimensions, and entire cubes are removed
after the process is completed. The modified project will be saved in the AOT in your own layer.
To configure the project, start the SQL Server Analysis Services Project Wizard, and then select the Configure option. You then need to select the project to configure. Select the Dynamics AX project to configure the prebuilt project, and step through the wizard. For step-by-step instructions, see the “How to: Configure an Existing SQL Server Analysis Services Project” at http://msdn.microsoft.com/en-us/library/gg724140.aspx.
If you also deploy and process the project, you should notice the following changes:
■ Cube content (such as measures and dimension attributes that source data from tables that are affected by disabled configuration keys) is deleted from the project. You may see that entire cubes have been removed if the corresponding content has become invalid.
■ KPIs and calculated measures have been removed in cubes that depend on disabled measures and dimension attributes.
■ OLAP reports in Role Centers that source data from cubes that have been removed no longer appear on the Role Center page. If a user intentionally adds such a report to the Role Center, the report displays a warning message and will execute.
■ KPIs and measures that were removed no longer appear in the Business Overview web part.

Implementing the prebuilt BI solution [AX 2012]

Traditionally, BI solutions are implemented during the second or third phase of an Enterprise Resource Planning (ERP) implementation project. Needless to say, project fatigue sets in (and the budget gets exhausted), and subsequent phases are postponed or delayed. BI implementation is complex and involves the integration of many components. Also, the skill set required to implement a BI solution is distinctly different from the skill set required to implement an ERP system. Often, implementation of the BI solution involves engaging a different partner or consultants. All of these factors contribute to postponing the BI implementation.

Microsoft Dynamics AX 2012 simplifies the implementation of a BI solution, so that all Microsoft Dynamics AX 2012 partners and customers (regardless of whether they have access to BI specialists) can implement the prebuilt BI solution when they implement the ERP functionality.

In Microsoft Dynamics AX 2012, the default SQL Server Analysis Services (SSAS) project is a
first- class citizen of the Application Object Tree (AOT), as are other SSAS projects that you create in the AOT. This means that SSAS projects derive all of the benefits of being residents of AOT.
■ SSAS projects respect the layering concept. This means that an independent software vendor (ISV) or partner can distribute a customized version of an SSAS project that adds additional analytic components to the solution that is included in the SYS layer.
■ You can import and export SSAS projects to and from different environments as part of a model (by using models or .xpo files).
■ SSAS projects respect the version control capabilities offered by AOT-based artifacts.

When you deploy a project by using the SQL Server Analysis Services Project Wizard, which is new in Microsoft Dynamics AX 2012, the wizard selects the project in the highest layer for deployment.

If you examine the Visual Studio Projects node in the AOT, will see the default SSAS project that is included with Microsoft Dynamics AX 2012, as shown in Figure below. If you have any customizations at higher levels, they are also displayed.

image

Implementing the prebuilt BI solution consists of the following steps:
1. Implement the prerequisites.
2. Configure an SSAS server.
3. Deploy the cubes.
4. Process the cubes.
5. Provision users so that they can access the analytic data.


The following sections describe each step in further detail.

1- Implement the prerequisites
Before you implement the analytic components in the prebuilt BI solution, the following Microsoft Dynamics AX core components should be in place:
■ At least one AOS instance must be implemented.
■ The Microsoft Dynamics AX Windows client must be implemented, and the initialization checklist must be completed.
■ The Enterprise Portal web client must be configured.

If you are implementing the analytic components on a development or test instance, you might not implement a scale-out architecture. However, if you are implementing these components in a
production system, you may want to implement a redundancy or load balancing infrastructure. You need to configure the clustering or Network Load Balancing (NLB) solution before you implement the
analytic components.


2- Configure an SSAS server
This step configures a given SSAS server for the Microsoft Dynamics AX 2012 analytic components.

To do so, run the Configure Analysis Extensions step in the Microsoft Dynamics AX Setup wizard on the SSAS server that hosts Microsoft Dynamics AX 2012 cubes.
Running the configuration step should take you a few minutes. This function does the following:
■ Ensures that the SSAS server has all of the necessary prerequisites to host Microsoft Dynamics AX 2012 cubes.
■ Adds the Business Connector (BC) proxy user as an administrator of the SSAS server. This step is required to enable AXADOMD data extensions to operate without the use of Kerberos
constrained delegation.
■ Allows you to add a read-only user account to the Microsoft Dynamics AX 2012 database for processing cubes (you should specify a domain account whose password does not expire).

 

3- Deploy cubes
When you deploy cubes, Microsoft Dynamics AX generates and processes an OLAP database by using the metadata definition contained within the Analysis Services
project that is included with Microsoft Dynamics AX 2012. The result is an OLAP database that contains
Microsoft Dynamics AX cubes that are referenced by analytic reports and Role Centers.
In a Microsoft Dynamics AX 2012 R2 environment where there is only a single partition, the deployment step generates a single OLAP database that sources data from the Microsoft Dynamics
AX OLTP database. In a multiple-partition environment, the deployment step generates multiple OLAP databases that correspond to each partition. Figure below shows the deployment process both
in a single-partition and multiple-partition environment.

image

You use the SQL Server Analysis Services Project Wizard in the Microsoft Dynamics AX 2012 client to deploy, process, and in some instances, update cubes. To deploy the cubes, you must have the
right to deploy projects to the SSAS server. If you are also processing the cubes, you must have the right to read the Microsoft Dynamics AX 2012 OLTP database.

To start the SQL Server Analysis Services Project Wizard and deploy cubes, do the following:
1. In the Development Workspace, on the Tools menu, click Business Intelligence (BI) Tools > SQL Server Analysis Services Project Wizard.
2. On the Welcome page, click Next, and then select the Deploy option on the next page, as shown in Figure below

image

3. On the next page, you select an SSAS project to deploy—in this case the Dynamics AX project.
You can select a project in the AOT, as shown in Figure below, or you can select a project that is saved on a disk.

image

4. Next, you specify the SSAS server to deploy the project to, the SSAS database you want to use, and whether you want the project to be processed after deployment .
By default, the wizard uses the SSAS server that you configured earlier, but you can select any server to deploy the project to.

image

Deploy cubes in an environment with multiple partitions
As mentioned earlier, in a Microsoft Dynamics AX 2012 R2 environment with multiple partitions, the SQL Server Analysis Services Project Wizard generates an OLAP database for each partition. You can
use the wizard to select the partitions for which OLAP databases are created, as shown in Figure below

image

In this case, the SQL Server Analysis Services Project Wizard deploys the SSAS project to multiple OLAP databases. In each database, <partitionkey> is added as a suffix to the name of the OLAP
database.
Also, within each OLAP database, the data source view (DSV) is modified so that a partition filter is applied to all queries. Figure 10-8 shows the architecture of an environment with multiple partitions.
In all cases, the SSAS project in the AOT is partition-unaware, whereas the OLAP databases that are deployed are partition-specific. The SQL Server Analysis Services Project Wizard handles the step of
making sure that each OLAP database is wired to read data only from the corresponding partition in Microsoft Dynamics AX. This is a departure from the behavior of Microsoft Dynamics AX 2012. You
need to be aware of the following implications:
■ If you deploy Microsoft Dynamics AX SSAS projects by using Analysis Services tools, such as the Deployment Wizard or Business Intelligence Development Studio, the resulting OLAP
database is not partition-aware. In other words, cubes will aggregate data across partitions.
■ If you want to extend an SSAS project, always check out and modify the project in the AOT.
Do not customize a project associated with a specific partition by importing the project directly in Business Intelligence Development Studio. The Deploy function in the wizard will overwrite any partition-specific customizations that you have made directly on the server.
■ If you add custom query definitions in the DSV, the wizard adds where clauses to each select statement that restrict rows from other partitions.

image

4- Process cubes
The SQL Server Analysis Services Project Wizard lets you process deployed cubes directly. However, before processing, the wizard also runs through several prerequisite checks to ensure that cube
processing will not fail later. If you are using demo data, you can ignore these preprocessing warnings and have the wizard process the cubes.

While the project is being processed, the wizard displays a progress page. When processing is complete, click Next, and the wizard will show the completion screen.

 

5- Provision users in Microsoft Dynamics AX
After you deploy and process Microsoft Dynamics AX cubes, you must grant users permissions to access them. Provisioning users involves two activities:
■ Associate an appropriate user profile with each Microsoft Dynamics AX user.
■ Provide Microsoft Dynamics AX users with access to the OLAP database.