The Arthur Knowledge Base (AKB) offers numerous measures that Arthur Planning and Performance Analysis report from. But how do merchandisers get that information into other systems? With the Arthur Enterprise Suite AIMRunDML application, it's right at the merchandiser's fingertips guaranteeing they receive on version of the truth. Click Here.
The Arthur Knowledge Base (AKB) is full of numerous measures that Arthur Planning and Performance Analysis very easily report from. But how do merchandisers get that information into other systems? With the Arthur Enterprise Suite, merchandisers use an application called AIMRunDML. It offers access to all the same measures in Planning and Performance Analysis, but it imports and exports using common formats including: AKB, XML, CSV, and any ODBC database.
Although AKB utilizes Oracle as its database, using SQL to get the data out of AKB is a complicated process and will only yield the base measures which are actually stored in AKB. But with AIMRunDML, merchandisers get both types of measures, base (stored in AKB such as Sales) and derived (calculated by the Application Server such as Gross Margin) in the exact same manner as Planning and Performance Analysis does. This guarantees receiving one version of the truth, regardless if the source is Planning, Performance Analysis, or AIMRunDML.
The following offers a brief introduction and practical applications on how to make use of the AIMRunDML utility for version 2004.1. More details can be found in the AIMUtils.pdf when the utility is installed.
How to Use the Utility
Install the utility found on the Arthur Enterprise CD setup menu under the Product selection of Arthur Knowledge Base and the Tier of Administrator Machine. There will be a selection for Aim Administrative Utilities.
Next, develop the DML script. This script tells the utility where and how to get the information, allows for some processing and conditional operations on the data, and where to send the data.
The AIMRunDML utility behaves as a standard executable initiated through a command line specifying the path to the AIMRunDML executable and the path to the DML script such as:
"C:\Program Files\JDA\Arthur\AIM Admin\AIMRunDML.exe" "C:\Test.dml"
In addition, there are numerous command line parameters that can be added to specify some options in terms of how the application should run.
Highlights of AIMRunDML
- Easily incorporated into a third party scheduler (such as Windows Scheduler) to run at a particular time.
- DML script can specify attributes of structure members to narrow the selection.
- CURRENT (time group) function designed so the script does not always have to be modified if it is to report on the current time.
- Source and destination of the data can be any one of the following (the source and destination do not have to be the same formats):
- AKB database
- ODBC table
- TAB/FMT file pair
- XML data and schema file pair
Scenarios for Using AIMRunDML
- To copy from one version of AKB to another version within the same AKB database. For example, copy the Original Plan version to the Revised Plan version.
- To copy data from one AKB database to another AKB database. For example, copy Revised Plan data from the production environment to the training environment.
- To get a list of all product members included in a particular subcube, perhaps with a particular attribute.
- To get last week's Actuals and Plan data from AKB into the SQL Server database for a summary report.
- To clear out AKB data for a specified product, location, time period, version, and variable.
- To copy last week's EOP to this week's BOP in AKB.
Examples of DML Scripts
1. How to copy one version to another version:
Source
AIM
Channel demonames
DescriptionChannel demodesc
Schema demortl
Subcube analysis
User demortl
Password demortl;
Down Products SELECT GROUP CLASS;
Down Locations SELECT GROUP TOTAL;
Down Time SELECT GROUP PERIOD where Season='S20021';
Down Customers SELECT TOTAL ;
//Get the RP version and send it to OP
Down Versions Select RP Map RP OP;
Across Variables SELECT ADJ_CST,ADJ_RTL,ADJ_UNT, BOP_CST,BOP_RTL,BOP_UNT,
FRT_CST,PMD_CST,PMD_RTL,POSMD_CST,POSMD_RTL, REC_CST,REC_RTL,REC_UNT,
SHR_CST,SHR_RTL,SHR_UNT,SLS_CST,SLS_RTL, SLS_UNT;
Nulls On;
Destination
AIM
Channel dbs_demo
Subcube analysis
Stream demo
User demortl Password demortl
JobFile C:\JobFile.txt ;
2. How to copy data from one AKB database to another database:
Source
AIM
//source channes are from production environment
Channel demonamesprod
DescriptionChannel demodescprod
Schema demortl
Subcube analysis
User demortl
Password demortl;
Down Products SELECT GROUP CLASS;
Down Locations SELECT GROUP TOTAL;
Down Time SELECT GROUP PERIOD where Season='S20021';
Down Customers SELECT TOTAL ;
Down Versions Select RP;
Across Variables SELECT ADJ_CST,ADJ_RTL,ADJ_UNT,BOP_CST,BOP_RTL,BOP_UNT,
FRT_CST,PMD_CST,PMD_RTL,POSMD_CST,POSMD_RTL,REC_CST,REC_RTL,REC_UNT,
SHR_CST,SHR_RTL,SHR_UNT,SLS_CST,SLS_RTL,SLS_UNT;
Nulls On;
Destination
AIM
//Destination channels and stream are for training environment
Channel dbs_demotrain
Subcube analysis
Stream demotrain
User demortl Password demortl
JobFile C:\JobFile.txt ;
3. How to get member (structure) information for a particular buyer (where buyer is an attribute on Class):
Source
AIM
Channel demonames
DescriptionChannel demodesc
Schema demortl
Subcube analysis
User demortl
Password demortl;
//get the name and descriptions of the products
Down Products SELECT GROUP CLASS where BUYER = 'SMITH'
Include &Description as Desc, &Name as Name ;
Down Locations SELECT &any delete;
Down Time SELECT &any delete;
Down Customers SELECT &any delete ;
Down Versions Select &any delete;
Across Variables SELECT &none;
Nulls On;
Destination
TAB
DATA C:\Products.tab
FORMAT C:\Products.fmt;
This DML results in a data file that will have the descriptions and names of all the classes in the group Class as follows:
Formal Suits,CL001
Casual Suits,CL002
Boxers,CL003
Briefs,CL004
Mens Loafers,CL005
Mens Boots,CL006
Short Socks,CL007
Long Socks,CL008
Polos,CL009
Casual,CL010
Chinos,CL011
Western,CL012
Designer Jeans (M),CL013
Mens Belts,CL014
Mens Gloves,CL015
Mens Hats,CL016
Business Coats,CL017
Casual Coats,CL018
4. How to populate an ODBC Database with AKB data for the current week:
Source
AIM
Channel demonames
DescriptionChannel demodesc
Schema demortl
Subcube analysis
User demortl
Password demortl;
Down Products SELECT GROUP CLASS;
Down Locations SELECT GROUP TOTAL;
Down Time SELECT Current(Week);
Down Customers SELECT GROUP TOTAL;
Across Variables,Versions SELECT (SLS_RTL,TY) as Sales, (GM,TY) as GM, (SLS_RTL,RP) as PlanSales, (GM,RP) as PlanGM;
Nulls On;
Destination
ODBC
//send it to an ODBC DSN
//Action Empty will empty the table before populating it
//Allow absent fields means the ODBC database can have
// more fields than what the dml will be sending
DSN AKBReport Table tblReport Action Empty AllowAbsentFlds;
5. How to clear out data for version for a particular season:
//DML to empty out data at the lowest levels for a given season
//Assumes storage rules for the VERSION is set to MODIFY
Source
AIM
Channel demonames
DescriptionChannel demodesc
Schema demortl
Subcube analysis
User demortl
Password demortl;
//specify intersections at lowest levels for the version:
Down Products Select Group CLASS;
Down Locations Select Group TOTAL;
Down CUSTOMERS Select GROUP TOTAL;
//Specify the version to empty:
Down Versions Select RP;
//Specify the week to empty:
Down Time SELECT GROUP PERIOD where Season='S20021';
//variables will come from the process section
Across Variables Select &NONE;
//Nulls are on in order to get every intersection
Nulls On;
//Process section- set all base variables for this version to EMPTY
process
ADJ_RTL = Empty();
ADJ_UNT = Empty();
BOP_CST = Empty();
BOP_RTL = Empty();
BOP_UNT = Empty();
FRT_CST = Empty();
PMD_CST = Empty();
PMD_RTL = Empty();
POSMD_CST = Empty();
POSMD_RTL = Empty();
REC_CST = Empty();
REC_RTL = Empty();
REC_UNT = Empty();
SHR_RTL = Empty();
SHR_UNT = Empty();
SLS_RTL = Empty();
SLS_UNT = Empty();
Destination
AIM
Channel dbs_demo
Subcube analysis
Stream demo
User demortl Password demortl
JobFile C:\JobFile.txt ;
6. How to copy last week's EOP to this week's BOP:
// Transfer Last Weeks EOP to this weeks BOP
Source
AIM
Channel demonames
DescriptionChannel demodesc
Schema demortl
Subcube analysis
User demortl
Password demortl;
Down Products Select Group CLASS;
Down Locations Select Group STORE;
Down CUSTOMERS Select GROUP TOTAL;
Down Versions Select TY;
Down Time Select W200101 Map W200101 W200102;
Across Variables Select
EOP_RTL as BOP_RTL,
EOP_UNT as BOP_UNT;
nulls on;
Destination
AIM
Channel dbs_demo
Subcube analysis
Stream demo
User demortl Password demortl
JobFile C:\JobFile.txt ;
About RPE
RPE is a leading consulting services provider exclusively focused on the challenging needs of the retail industry. RPE provides strategic consulting services, systems management, implementation, integration, modification and system upgrades for retailers worldwide. With a time-tested and proven record in retail, RPE delivers services on time and on budget. Areas of expertise: Manhattan Associates' Integrated Planning SolutionsT, Integrated Logistics SolutionsT and Warehouse Management solution; Microsoft RMS; Island Pacific; and the JDA® ASP, PMM® and MMS® applications, E3®, Arthur® and Intactix®. For more information, visit http://www.rpesolutions.com.
Return to Top of Page
|
