In the AOT, expand the Data Dictionary node.

Right-click the Perspectives node, and then click New Perspective.

Select the node for the perspective.

In the Properties sheet, specify the following property values.

The Usage property determines how the perspective will be used. Setting the Usage property to OLAP indicates that the perspective will be used to generate a BI project for SQL Server Analysis Services (SSAS).

In the AOT, right-click the Data Dictionary node, and then click Open New Window.

In the new window, expand the Tables node.

Drag the following tables onto the Tables node for the SalesAnalysis perspective.

• AddressCounty

• AddressState

• CustGroup

• CustInvoiceJour

• CustInvoiceTrans

• CustTable

• InventItemGroup

• InventTable

In the AOT, right-click the SalesAnalysis perspective, and then click Save.

In the AOT, expand the node for the SalesAnalysis perspective, and then expand the Tables node.

Select the AddressCounty table.

In the Properties sheet, specify the following values.

In the AOT, expand the node for the AddressCounty table, expand the Fields node, and then select the Name field.

In the Properties sheet, specify the following values.

In the AOT, select the AddressState table in the SalesAnalysis perspective.

In the Properties sheet, specify the following values.

In the AOT, expand the node for the AddressState table, expand the Fields node, and then select the Name field.

In the Properties sheet, specify the following values.

In the AOT, select the CustGroup table in the SalesAnalysis perspective.

In the Properties sheet, specify the following values.

In the AOT, expand the node for the CustGroup table, expand the Fields node, and then select the Name field.

In the Properties sheet, specify the following values.

In the AOT, select the CustInvoiceJour table in the SalesAnalysis perspective.

In the Properties sheet, specify the following values.

In the AOT, expand the node for the CustInvoiceJour table, expand the Fields node, and then select the DueDate field.

In the Properties sheet, specify the following values.

In the AOT, select the InvoiceAmount field.

In the Properties sheet, specify the following values.

In the AOT, select the node for the CustInvoiceTrans table in the SalesAnalysis perspective.

In the Properties sheet, specify the following values.

In the AOT, expand the node for the CustInvoiceTrans table, expand the Fields node, and then select the InvoiceDate field.

In the Properties sheet, specify the following values.

In the AOT, select the LineAmount field.

In the Properties sheet, specify the following values.

In the AOT, select the Qty field.

In the Properties sheet, specify the following values.

In the AOT, select the Remain field.

In the Properties sheet, specify the following values.

In the AOT, select the CustTable table in the SalesAnalysis perspective.

In the Properties sheet, specify the following values.

In the AOT, expand the node for the CustTable table, expand the Fields node, and then select the Name field.

In the Properties sheet, specify the following values.

In the AOT, select the node for the InventItemGroup table in the SalesAnalysis perspective.

In the Properties sheet, specify the following values.

In the AOT, expand the node for the InventItemGroup table, expand the Fields node, and then select the Name field.

In the Properties sheet, specify the following values.

In the AOT, select the InventTable table in the SalesAnalysis perspective.

In the Properties sheet, specify the following values.

In the AOT, expand the node for the InventTable table, expand the Fields node, and then select the ItemName field.

In the Properties sheet, specify the following values.

In Visual Studio, open the BI project that you want to browse.

In Solution Explorer, double-click SalesAnalysis.cube.

Click the Browser tab.

Expand the Measures node, expand Customer invoice node, right-click Invoice amount, and then click Add to Data Area.

Expand the Time node, right-click the Year - Quarter - Month - Days hierarchy, and then click Add to Column Area.

Expand the Customers - Invoice account node, right-click the Customers - Invoice account.Customer groups - Customers hierarchy node, and then click Add to Row Area.

Browse the data. You can expand and collapse rows and columns in the table. You can modify the rows and columns that display in the table or add other dimensions to further slice the data.

In Cube Designer, click the Dimension Usage tab.

Click the ellipsis button (…) that appears at the intersection of the Time dimension and the Master company exchange rate measure group. The Define Relationship dialog box displays.

For the Select relationship type field, select Regular.

For the Granularity attribute field, select Days.

In the relationship table, select DATEKEY in the Measure Group Columns column.

Click OK.

In Solution Explorer, right-click Sales Analysis.cube, and then click Add Business Intelligence. Business Intelligence Wizard displays.

On the Welcome page, click Next.

On the Choose Enhancement page, click Define currency conversion, and then click Next.

On the Set Currency Conversion Options page, select the Master company exchange rate measure group, select Euro as a pivot currency, select US Dollar as a sample currency, and select n Euro per 1 US Dollars. Click Next.

On the Select Members page, mark the checkboxes for the Invoice amount and Revenue measures in the Measures column, and then select Master end of day rate for both of those measures in the Exchange Rate Measures column. Click Next.

On the Select Conversion Type page, select Many-to-many, and then click Next.

On the Define Local Currency Reference page, select Attributes in the dimension table. In the list that displays, expand the Companynode, select Currency code, and then click Next.

In the Specify Reporting Currencies page, select all currencies except Unknown, and then click Next.

Click Finish to complete the wizard.

This creates a new named query object called Reporting Currency in the data source view.

In Solution Explorer, double-click SalesAnalysis located in the Data Source Views folder.

In the Tables pane, right-click Reporting Currency, and then click Edit Named Query.

In the Edit Named Query dialog box, replace the SQL query with the following query.

SELECT RC.CURRENCYCODE, RC.ISOCURRENCYCODE, RC.CURRENCYNAME, RC.SYMBOL, DA.ISPIVOT
FROM (SELECT CURRENCYCODE, ISOCURRENCYCODE, CURRENCYNAME, SYMBOL
  FROM (SELECT CURRENCYCODE, CURRENCYCODEISO AS ISOCURRENCYCODE, TXT AS CURRENCYNAME, SYMBOL
  FROM CURRENCY AS A) AS BICURRENCYDIMENSION
    WHERE (CURRENCYCODE IN
      (SELECT CASE WHEN UPPER(CURRENCYCODE) IS NULL THEN '' ELSE UPPER(CURRENCYCODE) END AS CURRENCYCODE
         FROM COMPANYINFO))) AS RC INNER JOIN
           (SELECT D.ID, D.NAME, D.ISVIRTUAL, (CASE WHEN C.CURRENCYCODE IS NULL THEN '' ELSE UPPER(C.CURRENCYCODE) END) 
         AS CURRENCYCODE, (CASE WHEN C.SECONDARYCURRENCYCODE IS NULL THEN '' ELSE UPPER(C.SECONDARYCURRENCYCODE) 
          END) AS SECONDARYCURRENCYCODE, (CASE WHEN
         (SELECT TOP 1 UPPER(EXCHANGERATECOMPANY) AS EXPR1
            FROM [DBO].BICONFIGURATION AS B) = UPPER(D .ID) THEN 0 ELSE 1 END) AS ISPIVOT
            FROM DATAAREA AS D LEFT OUTER JOIN
            COMPANYINFO AS C ON C.DATAAREAID = D.ID) AS DA ON RC.CURRENCYCODE = DA.CURRENCYCODE
UNION
  SELECT DISTINCT N'Local' AS [Local 1], N'Local' AS Local, N'Local' AS [Local 3], N'Local' AS [Local 2], 1 AS [Local 4]
    FROM (SELECT     CURRENCYCODE, CURRENCYCODEISO AS ISOCURRENCYCODE, TXT AS CURRENCYNAME
    FROM CURRENCY AS A) AS BICURRENCYDIMENSION_1

In Solution Explorer, double-click Reporting Currency.dim located in the Dimensions folder.

Click the Dimension Structure tab if it is not already displayed.

In the Attributes pane, select the Reporting Currency dimension.

In the Properties window, type Master Company Reporting Currency for the Name property.

Set the ErrorConfiguration property to (custom).

Expand the ErrorConfiguration node, and then set the KeyNotFound and NullKeyNotAllowed properties to IgnoreError.

Set the UnknownMember property to Visible.

In the Attributes pane, select the Currency attribute.

In the Properties window, click the ellipsis button (…) for the DefaultMember property, click No custom default, and then click OK.

In the Data Source View pane within the Data Structure tab, select the ISPIVOT field from the Reporting Currency dimension and drag it to the Attributes pane.

Select the ISPIVOT attribute in the Attributes pane.

In the Properties window, set the AttributeHierarchyEnabled property to False and type Unpivot for the Name property.

In the Attributes pane, select the Currency attribute.

In the Properties window, set the OrderBy property to AttributeKey, and set the OrderByAttribute to Unpivot.

In Solution Explorer, right-click Reporting Currency.dim, click Rename, and then type Master Company Reporting Currency.dim.

In Solution Explorer, double-click SalesAnalysis.cube.

Click the Dimension Usage tab.

On the Dimension Usage toolbar, click Add Cube Dimension.

Select Master Company Reporting Currency, and then click OK.

In Solution Explorer, right-click SalesAnalysis.cube, and then click View Code.

Locate the following code.

<ID>Reporting Currency</ID> 

<Name>Reporting Currency</Name> 

Replace it with the following code.

<ID>Master Company Reporting Currency</ID> 

<Name>Master Company Reporting Currency</Name> 

Locate the following code.

<DimensionID>Reporting Currency</DimensionID> 

Replace all instances with the following code.

<DimensionID>Master Company Reporting Currency</DimensionID>

Locate the following code.

<CubeDimensionID>Reporting Currency</CubeDimensionID> 

Replace all instances with the following code.

<CubeDimensionID>Master Company Reporting Currency</CubeDimensionID>

Save the changes and then close the file.

In Solution Explorer, right-click Master Company Reporting Currency.dim, and then click View Code.

Locate the following code.

<ID>Reporting Currency</ID> 

Replace it with the following code.

<ID>Master Company Reporting Currency</ID> 

Save the changes and then close the file.

In Solution Explorer, double-click SalesAnalysis.cube to display Cube Designer.

In Cube Designer, click the Calculations tab.

On the toolbar, click Script View. The code that was generated for currency conversion displays.

Replace the code for currency conversion with the following code.

// <Currency conversion>
  Scope ( { Measures.[Invoice amount],  Measures.[Revenue]} );     
    Scope( Leaves([Time]),       
      Except([Master Company Reporting Currency].[Currency].[Currency].Members,[Master Company Reporting Currency].[Currency].[Local]),Leaves([Company]));       
        Scope({ Measures.[Invoice amount],  Measures.[Revenue]} );                       
          This = [Master Company Reporting Currency].[Currency].[Local] * (Measures.[Master end of day rate], StrToMember("[Currency].[Currency].&["+[Company].[Currency code].CurrentMember.Name+"]")) / (Measures.[Master end of day rate], LinkMember([Master Company Reporting Currency].[Currency].CurrentMember, [Currency].[Currency]));                        
        End Scope;            
     End Scope;                
  End Scope;  
// </Currency conversion>