Insights #33: Building and Maintaining a Model with Oros Links
Insights #33: Building and maintaining a model with Oros Links
The Challenge
You’ve studied up on ABC/M theory, learned all you could from the Oros training classes, and worked through the modeling tutorials. You’ve even gotten a running start on your ABC project with a Rapid Prototype session. Now the honeymoon is over—in the cold gray light of the morning when you have a deadline to meet and a model to deliver.
Relax!
A significant proportion of the data needed to build and maintain your ABC/M model already exists in your organization. Ubiquitous Financial/GL systems, ERP systems, Data Marts/Warehouses, and time-tracking systems are all potential sources for raw data—both financial and non-financial—that you can ‘enrich’ in a viable ABC/M model. The challenge, then, is integrating data from these disparate and diverse sources. Enter Oros Links.
Use your leverage
Oros Links is a bi-directional data transfer engine that enables your ABC/M model to communicate with other data systems in your organization. By leveraging standardized ODBC technology, Links makes the process of building your model easier, quicker, and more repeatable. This article will provide you with a road map to do exactly that.
How do I do it?
Identify your data sources
The first step in the data integration process is identifying and qualifying your data sources. Where will your raw ABC/M data come from? Your General Ledger (GL) system is a valuable source of cost information and possibly structural data to build your resource module. An Enterprise Resource Planning (ERP) system can provide a wealth of volume and driver data. And if your organization utilizes an electronic system to help record and track employees’ time, Oros Links may be able to extract FTE consumption data directly from it.
Parlez Vous ODBC?
The primary criterion that determines whether a data source is able to communicate directly with Oros Links is ODBC compliance. Open Database Connectivity (ODBC) is an industry-standard data-interchange protocol based on the SQL query language. Bottom line? If your data-systems are ODBC compliant, Oros Links can usually talk to them.
Those systems whose data is not exposed in tabular ODBC-friendly format frequently support other methods of data extraction (e.g. they can export data into ‘flat’ text files) or support proprietary interfaces to other databases. Oros Links can then use the intermediate format to incorporate the data into your model.
Figure 1 The Windows system ODBC control panel. Every item in this list is a potential data source for Oros Links.
On-line help is key
Source data must contain key elements of information to be importable into the ABC/M model. For example, an account in your model must be defined not only by its name and reference number but also by its position in the module hierarchy relative to its parent center. The “Foreach Objects and Properties” section in the Oros Links on-line help contains comprehensive reference information detailing key import field requirements for every object in an ABC/M model. It is your most valuable reference document.
Build model structure
Once you have identified and validated your source data, you are ready to begin using Oros Links to incorporate it into an ABC/M model. This process involves the sequential import of the various components of a model. While the order of the process is not rigidly defined, it is necessary to adhere to two basic guidelines. First, build the structure of the model by importing the following fundamental structural elements:
A) Build module structure
1-Construct Centers and Accounts
2 Construct entered Cost Elements
B) Define model-wide artifacts
1-Define Attributes
2-Define Drivers
3-Define External Units
C) Use model artifacts to ‘complete’ model
1-Attach Attributes to Accounts and Centers
2- Build Assignments
3- Create Bills of Cost
4- Define Dimensions
Import periodic data
After you have imported the data to construct the structural framework of your model, you can import periodic data to complete your model. Periodic data is numeric data such as costs, driver quantities and output volumes. Unlike with structural data, import order is not as critical here:
1.Entered Costs
2.Driver Quantities
3.Attribute Quantities
4.External Unit Costs
5.Output Quantities
6.Dimensional Data, i.e. Revenue and Sales Volume
Using Links to do all of this
Links in a chain
You will interact with Oros Links through its user interface which is organized as a Workspace. Think of the workspace as a tool box that contains a model and a selection of data transfer tools. The most fundamental tool in this toolbox is the Query engine. Oros Links Queries are often used to extract data from the ABC/M model. Here, however, we emphasize another ability. Links can, through the ODBC interface, query external data sources for relevant information and bring that information (with help from another Workspace tool, the Import) into your model. You can use and store as many different Workspace configurations as you wish.
Figure 2 The Links Workspace. Arranged vertically on the left are buttons that allow you to access different Workspace objects: Models, Queries (shown here), Imports, Exports, and Scripts. You can add various components to your workspace via the ‘File’ menu. A workspace can contain up to 100 items of each type.
Work your Workspace
Workspace tools are fundamentally dependent on one another: For example, the Workspace you would create to build your ABC/M model will contain, at the very least, a blank model, Queries, and Imports. Queries may be defined to “point” to a specific model and Imports point to a specific Query. These definitions (or pointers) are stored in the form of text files: Workspaces files have an .aws suffix. Queries an .aqy and Imports an .ais suffix. If the various tools are to work together, each has to know where the others are. Keep track of where you save these Workspace tools and be careful when you move them around on your hard drive.
Figure 3 A Workspace (.aws) file viewed with a simple text editor. Notice that the Workspace’s component Imports and Queries are identified by their relative file locations (paths). Any Links object’s definition may be opened, viewed, and edited with a text editor such as Notepad.
Query cheat sheet
Teaching you to write a Links Query that will ask the right questions of your external data sources is beyond the scope of this article (ABC’s Integration Basics training covers this is depth). However, every Oros installation includes a pre-packaged set of queries adequate to help you build every essential component of an ABC/M model.
The queries are located in a folder called \Samples\Queries in your Oros installation directory (e.g. C:\Oros\Samples\Queries). Add the sample queries to your workspace and you’re one step closer to building your model.
Figure 4 The sample queries included with your Oros installation follow a naming convention (though you may choose to name your queries as you see fit). The first letter of the file name indicates the module (R-Resource, A-Activity, C-Cost Object). The subsequent letters indicate the specific purpose of the query as listed above.
Bring it on in
The Import tool applies a query to a specific data source and then actually incorporates that data into your model. Imports are simple to configure provided you’ve selected the right options.
After you’ve selected the ‘Type’ of system (e.g. MS Access or Oracle 8) you’ll be importing from, make sure you fill in every other blank field (e.g. specifying Table or Named Range) in the import configuration screen. None of these fields are optional.
Figure 5 This Import uses a standard attribute hierarchy Query to import attribute definitions from an Excel file. The Type field lets you select any available ODBC data source. The Structure Action control field allows you to specify whether the import is allowed to Insert new structural elements to your model or only to Update existing elements.
Be aware that reserved words, such as ‘Level’ and ‘Type’, or special characters, like % or &, can cause problems for the ODBC interface. Avoid using them as names for tables or column headings. If you see a cryptic ODBC error message when you import, these words and characters are probably the reason why.
No problem at all
Once you have all your Imports correctly configured, all you need to do is click Run and watch them build your model. After you’ve written a few successful imports, another Oros Links tool that may come in handy is the Script. A Script is a list of instructions that you can ‘program’ Oros Links to follow. Do you need to run twelve different imports to refresh your model every month? Put together a simple Links Script and with the click of one button, Links will set the process in action while you go home for the night.
Simple, quick, efficient, even automatic, Oros Links helps you meet the challenge of model building.