How to Use Excel to Update SAS Activity-Based …



How to Use Excel to Update SAS Activity-Based Management Model Data

Revision 05.11.30

Overview

We will provide a simple scenario to illustrate basic steps you need to do when working with MS Excel to perform ABM Data Import/Export. Let’s suppose you want to use Excel to add 2 new resource accounts (“West Salaries” and “East Salaries”) to the following model:

[pic]

Step 1 – Setup Excel Folder Share Permission:

You must have a folder that ABM Server can perform the Excel Import/Export.

If you have access to ABM Server, do the following:

a) Create a new folder “C:\XL”

b) Right-click on the “XL” folder and choose “Sharing and Security”

c) Share the folder such that “Authenticated Users” have “Full Control” on that folder

[pic]

Step 2 – Export The Model Data:

Bring up the Export Wizard (File >> Export >> Model Data).

In this scenario, we just want to keep it simple and we choose to export all tables in the model (“Select default tables and properties to archive a model”).

Step 3 – Configure the Data Link Properties:

On Step 2 of the Wizard, click on the button next to 'Browse to set up a database connection' to bring up the Data Link Properties. Double click “Microsoft Jet 4.0 OLE DB Provider” and supply the “UNC” path to the Excel file (\\your_abm_server\XL\Model1.xls) – where “your_abm_server” is replaced with the actual name of your own server.

[pic]

[pic]

Finally, goto “All” tab and supply “EXCEL 8.0” as the “Extended Properties” parameter:

[pic]

This is the basic information on how to configure ABM Excel Import/Export. The rest of the steps you can follow the Wizard Instructions or just accept the default selections.

Step 4 – Update The Excel Worksheets:

In this scenario we want to add 2 new accounts. So we must update “DimensionMember” and “Account” worksheets.

Do not insert new data on the last row or you will have to redefine the cell boundary. So it’s a good idea to insert new data right after the first title row (i.e. starting from row 2).

Notice we have added the 2 new Dimension Members starting from top row (row 2).

[pic]

We have also updated the “Account” worksheet to tell ABM where in the Hierarchy we want the new accounts to reside.

[pic]

Step 5 – Import The Model Data:

Bring up the Import Wizard (File >> Import >> Model Data). When you get to the Data Link Properties, see Step 3 on how to Configure the Data Link Properties.

Notice we have successfully added the 2 new resource accounts (“West Salaries” and “East Salaries”) to the new model (New MSC1):

[pic]

Summary:

Although Excel is not the best choice for ABM Staging repository, you can use it to update small to moderate portion of your model data. For very large data updates, you should stick with the more conventional repository like SAS, Oracle or SQL Server. The example in this document should provide you with a general idea of what you need to do when working with Excel files to update your model. As with any update process, be sure you have a good backup of your base model. Model backups can be done easily by simply running the XML model export (File >> Export >> Model Data >> XML File).

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download