locked
Modeling Ranges of Accounts: PeopleSoft/MDS? RRS feed

  • Question

  • Hello Everyone,

    I've been asked to look at modeling a list of accounts from PeopleSoft in MDS. At the leaf/detail level I have "Accounts". These accounts are classified differently depending on their number, so {1-100} is "Cash", {200-300} is "Accounts Payable". So account #10 would be "Cash", and #210 would be "Accounts Payable". So far this is looking like something a combination of a Derived Hierarchy(Account Category -> Accounts) and business rules would solve.

    In PeopleSoft one can apparently edit these ranges by simply changing the number, so "Cash" becomes {1-199} with one edit. The structure I was thinking of is this:

     

      Entity: AccountCategory{Name, Code, MinRange,MaxRange}
    
      Entity: Account{Name, Code, AccountClassification{AccountCategory}}
    
      Derived Hierarchy: AccountCategory->Account
    
      Business Rules: For each Account, AccountClassification is the AccountCategory where Account.Code <=MaxRange and Account.Code >= MinRange

     

    I haven't worked with the business rules enough to know if I can put together that level of logic, of course I can always use the staging tables, but I'm looking for simple/easy way that an administrator could use to do this without having to code or have direct access to the DB.

    I'm hoping someone else might have done something similar and could let me know if I'm heading down the right path to a solution.

    I'm following up with the business to get more details on what the precise needed functionality of this is, but for now this is what I've got.

       *I don't have any background in PeopleSoft or accounting, so please excuse/correct me if I've misinterpreted something.

    Thanks,

    Isaac


     

    Thursday, March 24, 2011 3:35 AM

Answers

  • Hi,

    Unfortunately I dont think you will be able to use the business rules to auto generate this relationship, unless there's something that I'm not seeing.

    You've correctly modelled the entities so that each member in the lowest level entity (Account) points to one AccountCategory. In MDS this should be set up by the Account entity having a domain-attribute that uses the AccountCategory entity.

    The trouble is then that the business rules for the Account entity will only be able to access the AccountCategory that is directly related to each account member, e.g {1-100} would only be able to directly see and access the value of Cash. What you want each account to is to be able to access all of the Account Categories to search for the appropriate Account Category to use for each account, and thats what I dont think you will be able to with business rules. Possibly you could do it if you wanted to use the logic of a SharePoint workflow to set each account, but that would be quite a long winded way to do it, involving using the MDS API etc.

    You can achieve what you want with the MDS front end though. Your first option is to set up a derived hierarchy (as you've mentioned) and use the drag and drop etc to move the Accounts to their correct Account Category. The second option is that you can do a multi edit in the Explorer grid for the Account entity. Go to Explorer->Entities->Account. If you use the check boxes to pick the accounts that you want to move to a single Account Category, just edit the Account Category attribute and they will all move to the same Account Category.

    You might also want to check out the Chart Of Accounts sample package which you can find at \Program Files\Microsoft SQL Server\Master Data Services\Samples\Packages

    Hope that helps

    Jeremy


    http://blogs.adatis.co.uk/blogs/jeremykashel/default.aspx
    • Proposed as answer by Jeremy Kashel Wednesday, March 30, 2011 9:18 AM
    • Marked as answer by ialtis Wednesday, March 30, 2011 1:19 PM
    Thursday, March 24, 2011 1:17 PM

All replies

  • Hi,

    Unfortunately I dont think you will be able to use the business rules to auto generate this relationship, unless there's something that I'm not seeing.

    You've correctly modelled the entities so that each member in the lowest level entity (Account) points to one AccountCategory. In MDS this should be set up by the Account entity having a domain-attribute that uses the AccountCategory entity.

    The trouble is then that the business rules for the Account entity will only be able to access the AccountCategory that is directly related to each account member, e.g {1-100} would only be able to directly see and access the value of Cash. What you want each account to is to be able to access all of the Account Categories to search for the appropriate Account Category to use for each account, and thats what I dont think you will be able to with business rules. Possibly you could do it if you wanted to use the logic of a SharePoint workflow to set each account, but that would be quite a long winded way to do it, involving using the MDS API etc.

    You can achieve what you want with the MDS front end though. Your first option is to set up a derived hierarchy (as you've mentioned) and use the drag and drop etc to move the Accounts to their correct Account Category. The second option is that you can do a multi edit in the Explorer grid for the Account entity. Go to Explorer->Entities->Account. If you use the check boxes to pick the accounts that you want to move to a single Account Category, just edit the Account Category attribute and they will all move to the same Account Category.

    You might also want to check out the Chart Of Accounts sample package which you can find at \Program Files\Microsoft SQL Server\Master Data Services\Samples\Packages

    Hope that helps

    Jeremy


    http://blogs.adatis.co.uk/blogs/jeremykashel/default.aspx
    • Proposed as answer by Jeremy Kashel Wednesday, March 30, 2011 9:18 AM
    • Marked as answer by ialtis Wednesday, March 30, 2011 1:19 PM
    Thursday, March 24, 2011 1:17 PM
  • Thanks for the reply Jeremy. I'd forgotten the sample packages, so I'll see what I can pull from them.

    Wednesday, March 30, 2011 2:17 PM