Loading Currency and Entity Dimension Members


Continuing our setup of PPS Application based on AdventureWorksDW database. Let us focus on loading Currency dimension first as it is needed before we start loading Entity dimension members.

There were many records in DimCurrency table, however I took only those which are being used in DimOrganization table. You can use below query to create a View and add this new view to DataSource and then load the Currency members.

CREATE VIEW [dbo].[vDimCurrency]
As
SELECT
CURRENCYALTERNATEKEY LABEL, CURRENCYNAME DESCRIPTION, CURRENCYNAME NAME, ” SYMBOL
FROM
DBO.DIMCURRENCY
WHERE
CURRENCYKEY IN(SELECT DISTINCT CURRENCYKEY FROM DBO.DIMORGANIZATION)

image

For loading Entity dimension, I noticed that in DimOrganization table there is no Code (Label) specified in this table. So I thought it will be better to assign Label to each of these members so that later it will be easy for us in loading data and performing other tasks.

Please find below query that we can create as view and load the members using Data Source option.

ALTER VIEW dbo.vDimEntity AS
SELECT ‘100’ LABEL, ‘100 – AdventureWorks Cycle’ NAME, ” PARENT, ‘USD’ CURRENCYMEMBERID, ‘Corporate’ ENTITYTYPEMEMBERID, 1 OWNERSHIP_PERCENT UNION
SELECT ‘110’, ‘110 – North America Operations’, ‘100’, ‘USD’, ‘Management’, 1 UNION
SELECT ‘113’, ‘113 – Northeast Division’, ‘112’, ‘USD’, ‘Management’, 1 UNION
SELECT ‘114’, ‘114 – Northwest Division’, ‘112’, ‘USD’, ‘Management’, 1 UNION
SELECT ‘115’, ‘115 – Central Division’, ‘112’, ‘USD’, ‘Management’, 1 UNION
SELECT ‘116’, ‘116 – Southeast Division’, ‘112’, ‘USD’, ‘Management’, 1 UNION
SELECT ‘117’, ‘117 – Southwest Division’, ‘112’, ‘USD’, ‘Management’, 1 UNION
SELECT ‘111’, ‘111 – Canadian Division’, ‘110’, ‘CAD’, ‘Management’, 0.75 UNION
SELECT ‘120’, ‘120 – European Operations’, ‘100’, ‘EUR’, ‘Management’, 1 UNION
SELECT ‘130’, ‘130 – Pacific Operations’, ‘100’, ‘AUD’, ‘Management’, 0.75 UNION
SELECT ‘121’, ‘121 – France’, ‘120’, ‘EUR’, ‘Management’, 0.5 UNION
SELECT ‘122’, ‘122 – Germany’, ‘120’, ‘EUR’, ‘Management’, 0.25 UNION
SELECT ‘131’, ‘131 – Australia’, ‘130’, ‘AUD’, ‘Management’, 0.5 UNION
SELECT ‘112’, ‘112 – USA Operations’, ‘110’, ‘USD’, ‘Management’, 1

image

image

Now once All Members are loaded in Entity dimension. We can use below query (this query assumes that you have the vDimEntity view available) to create the CSV file for creating the member set with Parent-Child relationship.

SELECT LABEL, NAME, PARENT FROM dbo.VDIMENTITY

Paste the content of this query to excel file, specify the header columns and save the file as CSV. Create a new member set on Entity dimension and load the members using CSV data load option.

image

Once loaded, the member set should look like below:

image

Next we will look at other dimensions required to configure the Finance model.

Advertisements

Intercompany Eliminations for a Financial Model


Just came across a video giving overview of Intercompany Elimination Process in PerformancePoint Server. Thought of sharing with everyone.

http://blogs.msdn.com/normbi/archive/2008/04/30/pps-planning-intercompany-eliminations-for-a-financial-model.aspx

Dimension Data Loading – Bugs Identified


Two of the items specified in my previous blog http://ppsinfo.blogspot.com/2008/07/loading-account-dimension-members-using_17.html has been identified as bug and we should be getting the fix soon.

Bugs Reported:

  1. If we only specify Label and ParentLabel in the csv, the Name column gets rewritten by the Label column.
  2. Cannot select Parent column when loading from Data Source.

Details are available at TechNet Forum:

http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=3631674&SiteID=17

Loading Account Dimension Members – Using CSV Files


In this example we will be loading members to a Member Set. Ideally for loading members in a member set I should only need two columns Label and Parent.

Below is the query that we need to execute and paste the result into an excel file and save it as CSV.

SELECT
    CONVERT(nVarchar, AccountCodeAlternateKey) AS Label,
    CONVERT(nVarchar, AccountCodeAlternateKey) + ‘ – ‘ + AccountDescription AS Name,
    ISNULL(CONVERT(NVARCHAR,ParentAccountCodeAlternateKey), ”) AS Parent
FROM
    AdventureWorksDW.dbo.DimAccount

 

*Note: The parent for root members needs to be blank. You can see that adjustment being done in the query.

Now if I only take the Label and Parent column in my CSV file then the data members that gets loaded is not something we expect. Note the Name column.

image

Now if you put the columns Label, Name and Parent the data gets loaded perfectly.

image

Now one thing is still not clear to me. If I am loading the members for a member set using the Data Source option it is not at all giving option to select the Parent column.

image

Also in this case we don’t need to specify the AccountTypeMemberID again as this will be same as loaded in All Members member set.

Loading Account Dimension Members – Using Data Source


We will load the Account information from the sample SQL Server 2005 database ‘AdventureWorksDW’.

Here the first step will be to create the Data Source in PAC. We will be loading data to All Members member set.

*Note: The sample application I am working on is ACME. I have created a database called ACME_SourceDB and imported the DimAccount table from AdventureWorksDW database.

Now we can create a view on this database with below mentioned query.

CREATE VIEW [dbo].[vDimAccount]
AS
SELECT
    CONVERT(nVarchar, AccountCodeAlternateKey) AS Label,
    CONVERT(nVarchar, AccountCodeAlternateKey) + ‘ – ‘ + AccountDescription AS Name,
    CONVERT(nVarchar, AccountCodeAlternateKey) + ‘ – ‘ + AccountDescription AS Description,
    CASE AccountType WHEN ‘Assets’ THEN ‘Asset’ WHEN ‘Balances’ THEN ‘HeadCount’
    WHEN ‘Expenditures’ THEN ‘Expense’ WHEN ‘Flow’ THEN ‘Unit’ WHEN ‘Liabilities’ THEN ‘Liability’
    WHEN ‘Revenue’ THEN ‘Income’ WHEN ‘Statistical’ THEN ‘Unit’ ELSE ‘Header’ END AS AccountTypeMemberId FROM
    dbo.DimAccount

The columns needed for loading Account dimension are Label, Name, Description (Optional), AccountTypeMemberID. Notice the adjustment we need to do for AccountTypeMemberID. Now we can add this view to our data source.

image

Once data source is added to the application, we need to open the PBM and check out account dimension.Click on Load Members from right pane.

image

image

Verify the members loaded and check in the Account dimension.

image

That’s  it! we are done.

Data Input is not enabled in Forms – Common Reasons


#1: When we create a Cycle we need to specify the Scenario and Time Period range for which data entry will be done. Once the data input form is designed we need to make sure that it contains the scenario and period range as defined in the Cycle definition.

#2: Whenever we are creating a Data Input Form we need to make sure that we are including all the Dimensions used in Model in either Row, Column or the Filter area of the Form. Also in Filter we need to make sure that the Leaf level is selected. Similarly on Rows and Columns members should be available till leaf members as data input options will be only enabled at that level.

*Note: In case of Financial Models for below listed Dimensions we can make the specified selections:

  • BusinessProcess – Input
  • Flow – NONE
  • TimeDataView – Periodic

*Note: Sometimes some of the Dimensions in Filter area are fixed and is not changed by the Budget users (e.g. BusinessProcess, Flow etc.), we can hide these dimensions simply by hiding the particular row in the excel worksheet. 

#3: The Start Period of the Cycle should be greater than the Current Period setup of the Model for which Form is created. Model Current Period can be verified from Business Modeler.

#4: We need to verify whether ‘Allow Data Entry’ option is enabled for the form.

#5: Verify whether the user has write access to the Model and appropriate Dimension Members for which data input needs to be done.

PPS Downloadable implementation plans


PerformancePoint Server Implementation guide (worksheets, templates and projects) is available for download from Microsoft Technet website. The list contains templates for requirement gathering, planning configuration, testing and training.

image

http://technet.microsoft.com/en-us/library/cc535015(TechNet.10).aspx