The concept of a user profile was introduced in Microsoft Dynamics AX 2009. A user profile determines the Role Center that is displayed when a user starts the Microsoft Dynamics AX client. A user can be associated with only one profile.

If you do not associate a user profile with a user in Microsoft Dynamics AX, the default Role Center is displayed when the user displays the Home area page in the Microsoft Dynamics AX client. To associate a profile with a given user, click System Administration > Common > Users > User Profiles (see Figure 10-9). You can associate either one user at a time or multiple users with a given profile by using this form.

Figure 10-9. Associating a user with a profile.

You can also associate a user with a profile in the Users form (System Administration > Common > Users > Users). Changes to a user profile take effect the next time the user starts the Microsoft Dynamics AX client.

Unless you provide your users with access to the OLAP database, they cannot open reports and display key performance indicators (KPIs) drawn from cubes in their respective Role Centers. Security permissions defined in Microsoft Dynamics AX 2012 are not automatically applied to OLAP databases. You must grant access to OLAP databases manually by using SQL Server management tools, such as SQL Server Management Studio. For step-by-step instructions, see “Grant users access to cubes” at http://msdn.microsoft.com/en-us/library/aa570082.aspx.

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.

Next, you select the Microsoft Dynamics AX metadata that you want to include or exclude, as shown in Figure 10-11. 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.

Figure 10-10. Updating a cube with the SQL Server Analysis Services Project Wizard.

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 is also covered in further detail later in this chapter, in the Creating cubes section.

Figure 10-11. Selecting metadata.

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 10-12.

Figure 10-12. Selecting financial dimensions.

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.

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

Figure 10-13. Selecting a calendar for a date dimension.

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 (see Figure 10-14) 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.

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 10-14.

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.

Figure 10-14. Selecting languages.

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. For more information, see the section Define perspectives later in this chapter.

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.

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 10-15.

Figure 10-15. Selecting support for currency conversion.

For more information about currency conversion, see the section Add currency conversion logic in the Creating cubes section.

When you click Next on the Add Currency Conversion page, the wizard goes to work, performing the following tasks:

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.

Figure 10-16. Confirming changes to an SSAS project.

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:

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.

Next, you can deploy the cubes to an SSAS server and, optionally, process the cubes. As discussed in the Deploy cubes section earlier in this chapter, in a multiple-partition environment in Microsoft Dynamics AX 2012 R2, the system will deploy the project to multiple SSAS databases.

The DSV contains the table and view definitions that are used by analytic artifacts. Notice that the OLAP framework has implemented several query definition patterns in the DSV:

Avoid modifying any of the framework-generated objects in the DSV. Any changes that you make to these objects are overwritten without warning the next time you update the project.

You may add your own objects to the DSV (for example, new query definitions. The Project Update option will preserve these objects.

In Microsoft Dynamics AX 2012 R2, do not implement any partition-specific logic in any of the query definitions. Otherwise, when the project is deployed to multiple partitions, the system may generate processing errors. (Because the framework adds partition-specific logic to the DSV at deployment time, it may not apply the changes accurately to your query definitions.)

A data source has been created that points to the Microsoft Dynamics AX OLTP database.

In Figure 10-18, notice the dimensions that are included with the Microsoft Dynamics AX 2012 prebuilt BI solution, as well as the measures and measure groups.

For a list of measures and dimensions, see “Cube and KPI reference for Microsoft Dynamics AX 2012” at http://msdn.microsoft.com/en-us/library/hh781074.aspx.

The SSAS project contains prebuilt KPIs and calculations. Microsoft Dynamics AX 2012 does not provide the capability to model KPIs and calculations in the AOT. You can modify these definitions or add new ones directly in Business Intelligence Development Studio.

Data warehouses are a popular solution for providing analytic capabilities to users. Until recently, data warehouses were the only reasonable solution for building robust analytic capability. However, as applications become easily interoperable and as technologies such as in-memory databases and OLAP become cost-effective and simpler to use, building a data warehouse is not the only solution to meet analytic requirements.

Table 10-2 presents several architecture options for integrating external data with the prebuilt analytic solution; a data warehouse is just one of the options. The columns represent architecture options, whereas the rows represent the benefits and cost implications of each option.

When most data is in Microsoft Dynamics AX (assuming that Microsoft Dynamics AX is the predominant source of data in the organization), you have two options.

The data mash-up option is best suited to an environment where capable users author and publish analyses for the use of others. This option relies on client tools such as Excel PowerPivot. Microsoft Dynamics AX 2012 enables Microsoft Dynamics AX queries to be published to data mash-up tools through OData feeds, or as data exports to Excel.

You can bring external data into Microsoft Dynamics AX either through services (data services consumed by means of inbound ports) or as batch jobs that are executed periodically to import data into tables. With this approach, external data is represented as read-only data within Microsoft Dynamics AX. The benefit to this approach is that external data appears as native Microsoft Dynamics AX data to Microsoft Dynamics AX tools. You can create analytics, reports, and inquiry forms that use the combined data.

A more complex approach involves integrating external data directly into the prebuilt BI solution. With this option, a BI developer adds another data source to the prebuilt BI solution by using Business Intelligence Development Studio. Additional data tables are brought into the DSV by using the new data connection. It is possible to create dimensions and measures by using the new tables in the DSV.

The traditional ETL-based data warehouse option is suited to scenarios that require complex transformations or large volumes of data. Although this option is more flexible in terms of capabilities, it is also the most expensive to implement and manage.

You might want to build a data warehouse to implement the following scenarios:

Although Microsoft Dynamics AX 2012 does not directly support the creation of a data warehouse schema, the following artifacts generated in Microsoft Dynamics AX 2012 can be used to build a data warehouse:

Next, you need to define the metadata that is required to generate the cube in the AOT. As you might recall from Microsoft Dynamics AX 2009, you define the metadata required to generate cubes in the Data DictionaryPerspectives node of the AOT.

Each perspective corresponds to a cube. Tables or views that are contained in a perspective node generate measures or dimensions. Depending on table relationships (and inferred view relationships), measures are associated with dimensions within the generated project.

If you want to designate a perspective node that contains only dimensions, Microsoft Dynamics AX 2012, includes a property at the perspective level specifically for this purpose: SharedDimensionContainer. If you designate a perspective as a shared dimension container, tables and views within that perspective will be used only to create dimensions. Moreover, all of the dimensions will be associated with all of the measures; that is, they are truly shared dimensions, provided that they are related in Microsoft Dynamics AX.

Follow these steps to create the new perspective for this example:

For more information, see “How to: Create a Perspective for a Cube” at http://msdn.microsoft.com/en-us/library/cc617589.aspx.

Strictly speaking, table-level properties (see Figure 10-22) are optional. However, if you do use them, cubes will perform better.

Figure 10-22. Table-level properties.

You can also specify custom labels to give specific names to generated measure groups and dimensions. AnalysisDimensionLabel, AnalysisKeyAttributeLabel, and AnalysisMeasureGroupLabel are new properties introduced in Microsoft Dynamics AX 2012. Instead of providing English text, you can provide Microsoft Dynamics AX labels so that dimension names are translated into other languages. The AnalysisIdentifier property defines the field that provides the name for a dimension key. If you look at the Name field for this property in Figure 10-22, you will notice that the Methods Of Payment dimension is keyed by the Name field.

For more information, see “Business Intelligence Properties” at http://msdn.microsoft.com/en-us/library/cc519277.aspx.

If you are a fan of the semantics introduced with the IsLookUp property in Microsoft Dynamics AX 2009, you will be pleased to know that views in Microsoft Dynamics AX 2012 provide this functionality. However, the IsLookUp property will be deprecated in future releases, so it is recommended that you do not use this property.

Defining field-level properties is the key step in defining metadata. You need to identify individual measures and attributes that are necessary in the cube.

First, expand the CUSTTRANSTOTALSALES view, and set the field properties as shown in Table 10-3.

The AmountMST field will generate a measure that is summed when it is aggregated. ExchangeRateDateField is a new attribute added in Microsoft Dynamics AX 2012 for currency conversion. In this example, the OLAP framework should convert the AmountMST measure to all available currencies, so that users can analyze transactions (possibly conducted in different currencies) across a common currency. The TransDate field contains the date on which the measure will be converted into other currencies with Microsoft Dynamics AX exchange rates.

Users need to be able to slice the data by TransType and TransDate, so these fields are designated as attributes.

Next, open the CUSTTABLECUBE view, and set the field-level properties as shown in Table 10-4.

Finally, expand the CUSTPAYMODE table, and set the field-level properties as shown in Table 10-5.

For more information about field-level properties, see “Business Intelligence Properties” at http://msdn.microsoft.com/en-us/library/cc519277.aspx.

In the wizard, select the Create option, because you are creating a new project, and provide a name. Alternatively, if you want to include the new cube in the prebuilt SSAS project, you can select the Update option.

On the next page, select the perspectives that are used to generate cubes and dimensions within the project. For this example, you would select the MyCustomers perspective. You can include one or more perspectives within the same project.

You can also include Microsoft Dynamics AX financial dimensions, in addition to Microsoft Dynamics AX calendars and Microsoft Dynamics AX languages, as discussed earlier in this chapter.

Next, the wizard lets you add currency conversion logic to the project.

As you may recall, while defining field-level properties for the perspective, AmountMST was identified as a measure that needs to be converted to other currencies. The AmountMST field contains an amount that is recorded in the accounting currency of the company. Because Microsoft Dynamics AX might contain multiple companies that have different accounting currencies, transactions might be recorded in different accounting currencies.

For example, the CEU company’s accounting currency is GBP, whereas the CEUE company’s accounting currency is USD. In the AmountMST field, sales for CEU are recorded in GBP, whereas those for CEUE are recorded in USD.

Because a cube aggregates data across companies, a user browsing the cube could inadvertently add GBP values to USD values unless something is done to differentiate the two amounts. The Microsoft Dynamics AX 2012 OLAP framework builds this mechanism for you in the form of currency conversion support.

Microsoft Dynamics AX 2012 cubes contain two system dimensions: Currency and Analysis Currency. If the user uses the Currency dimension to split the measures that are shown, Microsoft Dynamics AX displays amounts only in the chosen currency. If the user uses the Analysis Currency dimension to split the measures that are shown, all amounts are shown, but the resulting values are converted to the chosen analysis currency by using Microsoft Dynamics AX exchange rates. This happens through currency conversion.

Here is an example: assume that the transactions shown in Figure 10-23 are included in the CUSTTRANSTOTALSALES view. (Note that two columns have been added, Accounting Currency and AmountCur, to clarify that each company has a different accounting currency.)

Figure 10-23. Transactions for companies in different accounting currencies.

If a user creates a PivotTable and displays the total AmountMST value split by the Analysis Currency dimension, the result is as shown in Figure 10-24.

Figure 10-24. Analysis currency.

To get the value of AmountMST in USD, the system calculated the USD equivalent of each of the amounts, as shown in Figure 10-25.

Figure 10-25. Currency conversion for analysis.

To determine the exchange rate between CAD and USD, and between GBP and USD, the system used the field-level metadata tag ExchangeRateDateField. For this example, the ExchangeRateDateField value for AmountMST is TransDate. So the TransDate value associated with each record was used to find the exchange rate to use for the conversion.

Microsoft Dynamics AX 2012 has the concept of a rate type. In other words, multiple exchange rates can be associated with a given company. A company can use different rates for different purposes or different rates for different locations. The Microsoft Dynamics AX 2012 OLAP framework uses the system exchange rate type for the currency conversion logic. This rate type is a systemwide parameter that a system administrator specifies on the System Parameters form (System Administration > Setup > System Parameters), as shown in Figure 10-26.

Figure 10-26. Setting the system currency and exchange rate type.

If you create a PivotTable with the Currency dimension, AmountMST values are filtered by the specified currency, as shown in Figure 10-27. You would expect this behavior if you created a PivotTable with any dimension.

Figure 10-27. PivotTable with the Currency dimension.

Provided that you define the field-level metadata tag ExchangeRateDateField, the wizard adds the currency conversion calculation to the generated project as a multidimensional expression (MDX) script. The wizard also adds the system dimension Analysis Currency (the Currency dimension is added regardless of whether you select currency conversion). The wizard also creates an intermediate measure group called Exchange Rates By Day in each cube.

If you open the generated project in Business Intelligence Development Studio, you can see the currency conversion calculation created by the wizard:

CALCULATE;
//-----------------------------------------------------------------------------
// Dynamics AX framework generated currency conversion script.
// Customizing this portion of the script may cause problems with the updating
// of this project and future upgrades to the software.
//-----------------------------------------------------------------------------
Scope ( { Measures.[Amount] } );
    Scope( Leaves([Exchange rate date]),
           Except([Analysis currency].[Currency].[Currency].Members,
                  [Analysis currency].[Currency].[Local]),
           Leaves([Company]));
        Scope( { Measures.[Amount] } );
            This = [Analysis currency].[Currency].[Local] * ((Measures.[Exchange rate],
StrToMember("[Currency].[Currency].&["+[Company].[Accounting currency].CurrentMember.N
ame+"]"))
/ 100.0);
        End Scope;
    End Scope;
    Scope( Leaves([Exchange rate date]),
           Except([Analysis currency].[Currency name].[Currency name].Members,
                  [Analysis currency].[Currency name].[Local]),
           Leaves([Company]));
        Scope( { Measures.[Amount] } );
            This = [Analysis currency].[Currency].[Local] * ((Measures.[Exchange rate],
StrToMember("[Currency].[Currency].&["+[Company].[Accounting currency].CurrentMember.N
ame+"]"))
/ 100.0);
        End Scope;
    End Scope;
    Scope( Leaves([Exchange rate date]),
           Except([Analysis currency].[ISO currency code].[ISO currency code].Members,
                  [Analysis currency].[ISO currency code].[Local]),
           Leaves([Company]));
        Scope( { Measures.[Amount] } );
            This = [Analysis currency].[Currency].[Local] * ((Measures.[Exchange rate],
StrToMember("[Currency].[Currency].&["+[Company].[Accounting currency].CurrentMember.N
ame+"]"))
/ 100.0);
        End Scope;
    End Scope;
    Scope( Leaves([Exchange rate date]),
           Except([Analysis currency].[Symbol].[Symbol].Members,
                  [Analysis currency].[Symbol].[Local]),
           Leaves([Company]));
        Scope( { Measures.[Amount] } );
            This = [Analysis currency].[Currency].[Local] * ((Measures.[Exchange rate],
StrToMember("[Currency].[Currency].&["+[Company].[Accounting currency].CurrentMember.N
ame+"]"))
/ 100.0);
        End Scope;
    End Scope;
End Scope;
//-----------------------------------------------------------------------------
// End of Microsoft Dynamics AX framework generated currency conversion script.
//-----------------------------------------------------------------------------

This logic is similar to the code added by the Define Currency Conversion option in the SSAS Business Intelligence Wizard. If the selected Microsoft Dynamics AX exchange rate type does not have records corresponding to the dates (for example, TransDate) that are present in data, the calculations will use the most recent rate for the corresponding currency pair.

After you specify currency conversion options, the system will generate the project and prompt you for a destination to which to save the project.

You can save the project in the AOT or on disk. This gives you the flexibility to maintain SSAS projects in the development environment of your choice. OLAP framework tools, such as the SQL Server Analysis Services Project Wizard, will work with projects whether they are on disk or in the AOT.

If you save the project in the AOT, the project will be saved in your layer.

You can deploy the project directly to the Analysis Services server at this stage. It’s important to note that the wizard calls the Analysis Services deployment functionality behind the scenes. If you do not have the Microsoft Dynamics AX Development Workspace (including Business Intelligence Development Studio) installed on your computer, this step may fail.

As discussed earlier, in Microsoft Dynamics AX 2012 R2 you can deploy a project to multiple partitions. If you have multiple partitions defined, you can deploy the project to the set of partitions you choose.

Embedding an existing Power View report in a Microsoft Dynamics AX Role Center is easy: just use the Page Viewer web part in SharePoint Server, and enter the URL of the Power View report.

Start the Power View report viewer in a browser window, copy the URL for the report (see Figure 10-28), and then paste it into Notepad.

Figure 10-28. Power View report.

Here’s an example URL:

http://vsqlbuvh0301/_layouts/ReportServer/AdHocReportDesigner.aspx?RelativeReportUrl=/Shared%20Documents/Dynamics-SalesbyRegion.rdlx&ViewMode=Presentation&Source=http%3A%2F%2Fvsqlbuvh0301%2FShared%2520Documents%2FForms%2FAllItems%2Easpx&DefaultItemOpen=1

Notice that the first part of the URL contains the path to the Power View designer and the report being viewed in the designer. The remainder is a collection of parameters that are passed to the designer when it is started by the caller.

You can customize the appearance of the Power View window shown in the Role Center by manipulating these parameters. Table 10-8 lists the parameters and describes what they do.

If you change the URL by applying the parameter values in Table 10-8, the modified URL looks as follows:

http://vsqlbuvh0301/_layouts/ReportServer/AdHocReportDesigner.aspx?RelativeReportUrl=/Shared%20Documents/Dynamics-SalesbyRegion.rdlx&ViewMode=Presentation&Source=http%3A%2F%2Fvsqlbuvh0300%2FPPSubSite%2FShared%2520Documents%2FForms%2FAllItems%2Easpx&DefaultItemOpen=1&Fit=True&PreviewBar=False&BackgroundColor=White&AllowEditViewMode=False

Now, open the Role Center, and select the option to modify or personalize the page. In Edit mode, select Add Web Part, and the Web Part gallery will appear. Select the Page Viewer web part from the gallery of available web parts, as shown in Figure 10-29.

Figure 10-29. Web Part gallery.

After you add the web part, specify the URL for the report. You can also provide a friendly title and height and width parameters to suit the window, as shown in Figure 10-30.

Figure 10-30. Specify the presentation options for a Power View report.

Figure 10-31 shows the result. Notice that you can match the color scheme of the Power View report with the color scheme of other reports and charts on the page. This way, users won’t notice a difference between the Power View report and the other charts on the page.

Figure 10-31. Power View report in a Role Center.

In the previous example, the Edit button and the chrome were disabled because an editing experience in a small window would not be optimal. Also, the capability to edit a report may be beyond the reach of some of the users.

However, you can easily allow users to edit a report by creating a quick link to start Power View in a separate browser window.

To do so, create a new URL quick link by clicking the Add Links option in the Quick Links web part, as shown in Figure 10-32.

Figure 10-32. Adding a link to the Quick Links web part.

In the Add Quick Link dialog box, paste the URL of the Power View report, as shown in Figure 10-33.

Figure 10-33. Specifying a URL to a Power View report.

You will now see the new quick link added, as shown in Figure 10-34.

Figure 10-34. A quick link to a Power View report.

Start the Microsoft Dynamics AX client and then navigate to a Role Center. Note that the example in this section uses a sample Role Center. Select the option to edit the Role Center page. If you are using the Microsoft Dynamics AX Windows client, you are able to personalize the page for yourself only. If you are a developer, customizing the page for everyone, you should start the Microsoft Dynamics AX Enterprise Portal web client and edit the page.

Select Add Web Part. You should see the SharePoint Web Part gallery, as shown in Figure 10-36.

Select the KPI List web part. Click Add. After the web part is added, select Exit Editing. Now you will see the new web part added to the Role Center page.

Figure 10-36. Adding a KPI.

Click the Add KPIs option to add a new KPI to the web part. You will see an Add KPI dialog box similar to the one in Figure 10-37.

Figure 10-37. The Business Overview–Add KPI dialog box.

If you are familiar with the Business Overview web part in Microsoft Dynamics AX 2009 or Microsoft Dynamics AX 2012, you will notice several new additions to the Business Overview – Add KPI dialog box (the Add New Indicator dialog box provides similar options).

First, you have an expanded set of options for applying filters. You can add any number of filters—both relative time periods and fixed values. This way, a user can add a filter to an existing KPI definition and display it on his or her Role Center. This feature yields two benefits. You can define a general-purpose KPI definition that applies to the entire organization or the business unit. Users can narrow down the scope of the KPI definition so that it closely matches their area of focus, without developer intervention.

You are probably familiar with the Split option that lets a user display the breakdown of a KPI definition by a selected attribute. For example, the Revenue KPI can be split by sales units so that a sales manager can monitor units that are falling behind. Unlike in Microsoft Dynamics AX 2012, the user can display the top 10 or bottom 10 values, so that the list is not too long.

It was possible to provide a drill-through link to each KPI in Microsoft Dynamics AX 2012, but the picking experience was not user friendly. In Microsoft Dynamics AX 2012 R2, the picking experience has been improved so that the user can associate a Microsoft Dynamics AX menu item or a URL with each KPI.

Notice that the Cube field is already set to a prebuilt cube. In Microsoft Dynamics AX 2012, the Business Overview web part is hardwired to display KPIs from the default cube database. If you want to point the Business Overview web part to a different database, you can specify the database by providing a database connection file; that is, an Open Database Connectivity (ODC) file. For information about how to define an ODC file and add ODC files to Enterprise Portal, see “How to: Create an ODC file for a Business Overview Web Part” at http://msdn.microsoft.com/en-us/library/hh128831.aspx.

The default database is specified in the System Administration > Setup > Business Intelligence > Analysis Services > Analysis Servers form. When you deploy an SSAS project by using the SQL Server Analysis Services Project Wizard, the OLAP database created by this action is added to the list of databases in the Analysis Servers form.

Select an analysis server, and then click the OLAP Databases tab (see Figure 10-38).

Figure 10-38. Analysis Servers form specifying the default OLAP database.

The Default check box specifies the default OLAP database used by the Business Overview web part. You can switch the default database by selecting the check box for a different database.

Relative time period filters are shown in the Business Overview web part when you add a KPI or an indicator. However, you can define your own time period filter by using the Time Periods form (System Administration > Setup > Business Intelligence > Analysis Services > Time Periods), as shown in Figure 10-39.

Figure 10-39. The Time Period form.

This form lists three types of time periods:

You can define additional indicator and KPI list periods by using MDX code in this form. The Business Overview web part makes these filters available to users at run time.

The following are example definitions to help you understand time period filters.

If the time period definition is a template, you need to modify only the MDX expression in the template.

The CurrentDate period template contains the following MDX expression, which gets the current date from the system:

STRTOMEMBER('[|DateDim|].[Year - Quarter - Month - Week - Date].[Month].&[' +
vba!format(vba![date](), 'yyyy-MM-01') + 'T00:00:00]')

Notice the token |DateDim| in the expression. The Business Overview web part replaces this token with the actual name of the date dimension; therefore, you can use this expression with any date dimension.

If you examine the period template definition for CurrentFiscalDate, you will notice another token:

STRTOMEMBER('[|FiscalDateDim|].[Year quarter period month date].[Date].&[|c|]&[' +
vba!format(vba![date](), 'yyyy-MM-dd') + 'T00:00:00]')

In this case, the system interprets the token |FiscalDateDim| as a fiscal date dimension. The system identifies a fiscal date dimension by the name given to the dimension. The system interprets the token |c| as the current company.

The definition for the Month_LastMonth indicator uses the template that was discussed in the previous section.

The definition for an indicator contains two MDX expressions that correspond to two time period definitions (see Figure 10-40). The expression that provides the value for the current period is defined in the Current Period MDX field. Because there is already a template for calculating the current month, you can use that definition by referencing the template %CurrentMember%.

The expression that provides the value for the previous period is defined in the Previous Period MDX field. Again, you can use the template already defined and define an expression by using that template.

You will also need to provide a description and a display name for the period definition, as shown in the left pane of the Time Period form. These descriptions and display names appear in the Business Overview web part when the user applies the period filter.