Home

User Guide: QAD Business Intelligence

1. Data Transformer version 2 2 0 FAQAD 114 User Guide QAD Business Intelligence Table 2 1 Main Screen Toolbar Commands FAQAD Some functions are available using any of these methods For example when a table is selected in the main menu you can define subsets by clicking Subset on the Tools menu clicking the Subset command on the toolbar or right clicking the table name and selecting Subset Whichever method you use the same Map Table screen displays Note Some screens include their own toolbars typically containing two or three additional commands that apply only to the current function Other functions are available using only one method For example on the Indexes screen you must click a command to add or delete an index or to define a primary index Toolbar Commands and Menu Functions Table 2 1 through Table 2 5 list the menu functions and related toolbar commands Command Menu Function Replication Setup Subset I Check Scripts T Update Links Execute Now Preview Data data warehouse Refresh Field Mapping zr EJA Command Menu Function Properties BE Task List Add Database a T Application Settings 22 Rebuild Links m Command Menu Function Field Properties Add Field in E3 kA Delete Field custom tables only Command Menu Function
2. Browse Data Temp cwadieemp 00 Browse Model Temp cmadbetemp 000000 Browse Cubes cmadidwiognsrubes Browse Log cmadietNon 00000 Browse Login Password User Class omen Models Enter or browse to the location where the Cognos model files were installed or copied Data Temp Enter or browse to the location that the Cognos Transformer should use to store temporary data files created during cube generation Model Temp Enter or browse to the location that the Cognos Transformer should use to store temporary files created during model operations Cubes Enter or browse to the location where the Cognos Transformer should create the cubes Log Enter or browse to the location where the Cognos Transformer and the Data Transformer should write the log files The following optional properties are used when building Cognos models in which Cognos security has been enabled Cognos security is set up using the Cognos Access Manager and stored in a local authentication export 1ae file or LDAP compliant namespace Using QAD BI Progress 9 Edition 169 Login Specify the log in for a Cognos user whose authority will be used to regenerate the models Password Specify the password for a Cognos user whose authority will be used to regenerate the models User Class Specify a Cognos User Cla
3. cane Languages The Languages tab allows for the selection of the language or languages in which multilingual cubes should be created Note Single language cubes ignore these settings Important To support different languages when displaying Cubes in Cognos PowerPlay Transformer you must set special language encoding and fonts in the Windows operating system For information on setting up language support see Installation Guide QAD Business Intelligence Cubes are created in subfolders of the Cube directory as specified in the Application Settings Cognos Transformer tab Subfolders are named using the two character language identifier for the selected languages If no language is selected the cube is built directly within the Cube directory Fig 1 44 Cube Properties Schedule Tab AQAD 72 User Guide QAD Business Intelligence Fig 1 45 Cube Properties Languages Tab AQAD Cube Properties x Cube Details Schedule Languages piii Nederlands English Castilian Spanish Fran ais Deutsch Espa ol Latino Polish Brazilian Portuguese PRA omm000000008 00 For information on creating or modifying language tokens for translated Cognos Transformer model files see Creating Translatable Cognos Transformer Models on page 191 Edit IQD Files Cubes are created by Cognos Transformer using Impromptu Query Definition iqd files One IQD file with native
4. omen FAQAD 166 User Guide QAD Business Intelligence Fig 2 47 Application Settings Table Linking Tab AQAD Generated Scripts Directory Enter or browse to the location where the automatically generated table replication scripts are to be written Custom Scripts Directory Enter or browse to the location where the custom scripts are located Automatically Resort Fields Alphabetically Select to have the fields columns of the mapped table automatically sorted on the field mapping screen Automatically Check ETL Scripts Select to automatically generate the mapped table scripts and to compile the generated and custom scripts This process also applies after a metadata import Work Directory Enter or browse to the location of the ETL tool This directory contains the encrypted source code log files and temporary files and should not be changed unless recommended by QAD Support Currency Select a currency from the list of currency codes in which to express all currency values defined in the standard business models The default currency is the MFG PRO base currency Table Linking The Table Linking tab includes parameters used to connect to the Access data warehouse proxy Application Settings x Data Warehouse Cognos Transformer Batch Process Notification AppServer r Database Details Database casco wabiwh mdb Browse JV Automatically Refresh Linked Tables ODBC Connec
5. D Add Index Delete Index al t Designate as Primary Index Iri Designate as Business Key Index Using QAD BI Progress 9 Edition 115 Table 2 2 Field Selection Toolbar Commands Table 2 3 Indexes Toolbar Commands FAQAD 116 User Guide QAD Business Intelligence Table 2 4 Import Metadata Toolbar Commands Table 2 5 Tasklist Toolbar Commands FAQAD Command Menu Function Delete Business Key Index fz Designate as Complete Business Key Index Designate as Incomplete Business Key a Index Command Menu Function Open Cancel Open and load metadata file Exit screen without loading metadata Command Menu Function Select All L1 Note Toolbar commands are context sensitive they are enabled only when the function they represent is appropriate to the current task or selection Unavailable commands display in gray Select None Other situations require you to right click to display and choose available functions For example to assign tables to the data warehouse structure you must right click the appropriate database and choose the Select Tables command There is no other access point for this function Source System Icons Using QAD BI Progress 9 Edition 117 The Data Transformer distinguishes items in the left pane of the main screen the tree using icons Table 2 6 lists the icons and their des
6. appltask engine defs i amp SCOPED DEFINE SRC DB SRCDB j amp SCOPED DEFINE TRG DB DWHQ E n P xl FAQAD 152 User Guide QAD Business Intelligence Fig 2 34 Merge Tables FAQAD Click Browse to navigate to the directory containing the Progress script The system displays the contents of the script You can update the default script name as needed However you cannot update the content of the script itself on the preview screen On the Schedule tab specify when this script is run This is the same as scheduling replication for standard tables For a more detailed discussion of setting the sequence for a replication task see Setting the Replication Task Sequence on page 189 Merge Data from Multiple Databases You can use the merge feature in the Data Transformer to consolidate data from multiple data sources that share the same underlying table definitions After defining replication for one database use the Merge command to configure additional databases to reuse the replication configuration of the configured database To merge in data from a data source right click the data source and select Merge Tables The system displays the data warehouse structure associated with all other data sources Merge With Database BOs ED 3 2 I Active Transactions El Do Historic Transactions H0O Master Files To use the same replication configuration as another data source select the che
7. AQAD Field dwh admin fld map loj xl DWH Name Source Name Label Description DataType OEA code_q code qadcoi character x 8 OF code desc code desc Description character x 24 OF code fld code fldname Field Name The Field For w character x 32 OF code_userl code useri Ufld1 User Field 1 character x 8 OF code user2 code_user2 Ufld2 User field 2 character x 8 B Customer code_value Value The allowable character x 8 Mra Customer code cmmt Comments The user s com character x 40 theDbName character x 32 RECID amp SRC recid gt gt gt gt gt gt 9 From the field mapping screen select the fields in the source table that you want to replicate to the data warehouse table Then click OK The system validates each field record for syntax and when successful places a green triangle on the table icon on the main screen to indicate that replication setup is completed for that table If validation fails a message is displayed to indicate the nature of the error and the table icon is set to a yellow exclamation point Create Subsets Within the data source data from different functional areas is sometimes stored in the same table For example the QAD ERP transaction history table tr_hist stores data related to sales orders but it also contains many other types of business documents as well You can define subsets to isolate the data of interest in a data warehouse table In QAD BI
8. AQAD 8 User Guide QAD Business Intelligence AQAD Data Transformer generates a set of Progress scripts to synchronize the data warehouse with the source databases Progress developers can customize the automatically generated scripts or create new scripts to extend the ETL process This guide includes detailed information on using the features of Data Transformer to configure your QAD BI environment Data Warehouse The data warehouse is a Progress database that is populated with data from the QAD ERP database and or other data sources during the ETL process The data warehouse is an environment separate from the source databases where data can be specially structured to support analysis BI Monitor BI Monitor is a tool for monitoring and managing tasks You can use BI monitor to e View detailed information of currently running tasks and task batches Stop a running task View logs of tasks and task batches stored in the administration database Archive logs of tasks and task batches in the administration database Impromptu Query Definition iqd files Impromptu Query Definition iqd files are query definition files to be used in Cognos PowerPlay Transformer One file with native SQL corresponds to each query subject or dimension externalized for use in Transformer See the Cognos 8 documentation for details on the IQD method Using QAD BI OpenEdge 10 Edition Cognos PowerPlay An important par
9. Database Physical Name xcRaes se S Browse Logical Name CorpExchRetes v Active V GADDB Version 21 zf Connection Details Remote IV Single User Host ecn F Service Other Parameters fu doe o92 P password Connection String CONNECT db C installish db db ExchRates Id CorpExchRates i j Cancel When a schema holder is first added the Data Transformer retrieves all defined schema images and creates additional connection records for each one You can limit the selection of schema images to include using either the standard Delete function or the Select Schema Images screen AQAD 124 User Guide QAD Business Intelligence Fig 2 9 Select Schema Images Menu Item Fig 2 10 Select Schema Images AQAD el Data Transformer File Tools Settings Fes I e 85 coliao Corp Domain Database E E QAD Domain Blade st92bmfg Domain Blade aa eer u ag z Sl aaddb Saers Database H dor Disconnect D evt B cv PEPPER B 8 evi Select Schema Images a ne ES Properties QAD 1 Domain Blade Hi l tQ72hmfn 1 Mamain Rladel Use the Select Schema Images screen to choose which schema images you want to work with and remove those you do not need to replicate Move schema images from the Available Schema Images list to the Selected Schema Images list to have them show up in
10. Note An important exception to this is the currency setting which defaults from the MFG PRO base currency unless You are connecting to a domain database and the base currencies in the domains are different AQAD Using QAD BI Progress 9 Edition 165 You are connecting to multiple databases and the base currencies in the databases are different In both of these cases the currency setting is blank and you must manually set the currency for the data warehouse database See Currency on page 166 If for any reason you need to update any of those settings choose SettingslApplication Settings from the main menu The system displays a screen with the following tabs Data Warehouse Table Linking Cognos Transformer Batch Process Notification AppServer The following sections describe the functions of each tab Data Warehouse The Data Warehouse tab includes parameters applicable to the data warehouse database Fig 2 46 tings X Application Table Linking Cognos Transformer Batch Process Notification AppServer Settings Data Warehouse Tab DWH Details Generated Scripts Directory cjaadbivtwiscripts gen Browse Custom Scripts Directory Jergadbidwiscriptstcustom Browse System Scripts Directory ejasdbivtwtscrigtetsystem Browse lv Automatically Resort Fields Alphabetically v Automatically Check ETL Scripts Work Directory c asctiet Browse Currency uso gt
11. database that is MFG PRO eB2 1 or later releases of QAD ERP on the main screen and select Import Meta Data The Choose Import File screen displays Fig 1 47 Chose Import File Choose File Fea BS Look in e821 jotvom Active Transactions xml G Additional Custom Scripts eB21 xml My Recent Additional xml IE IDEE Code Tables xml E Custom Script DWH to DWH xml Custom Scripts eB21 Finance xml Desktop K Custom Scripts xml Data Warehouse xml 9 Historic Transactions xml Master Files xml System Tasks xml My Documents gr My Computer v My Network File name j Places Files of type Source Files xmll Cancel v Navigate to the directory where the model source files were placed during installation Typically this is qadbiInstallDir data meta version Where version is the QAD ERP database version for example c qadbi data meta eB2 1 FAQAD Using QAD BI OpenEdge 10 Edition 75 Select a file and click Open to load the metadata and create the data warehouse structure The Meta Filter Window displays Use the check box to Indicate that you want to import the object information for each object Object Name Displays the name of an object Fig 1 48 Meta Filter Window 5 x Meta Filter Window OPS DAccounts_Payable_ DAccounts_Receivable_ Accounts_Receivable_Details_ O Budget_Details_ Call_Detail_ Calls_ O Current_Costprices_ Inv
12. the Use AppServer check box is selected Use non integrity parameter Specify whether the data warehouse is started in non integrity mode When this option is selected Progress runs without using the database integrity or database recovery features Using QAD BI OpenEdge 10 Edition 85 Important Selecting this option can improve system performance However if Progress fails for any reason you cannot recover the database Use this feature with caution and make sure you have an adequate database backup strategy Log Language Select the language in which the log information is written Log Level Select the level of log information to be captured Error Displays functional errors Warn Displays activities that may cause errors as well as errors Info Displays trace of user activities as well as errors and warnings Debug Displays debug information for developers as well as errors and warnings Log to DB Specify whether to store log information in the administration database as well as write log files Note Whether or not you choose to log to the administration database log files are always generated Start If Database Management is Scripts enter or browse to the appropriate script files that are used to start the data warehouse Stop If Database Management is Scripts enter or browse to the appropriate script files that are used to stop the data warehouse Database Enter the data warehouse database name as defined in t
13. Fig 1 61 Cognos Server Configuration AQAD Cognos Server Configuration Cognos Server Configuration Gateway URL localhost 9300 p2pd servlet dispatch Namespace INTLM User kvz Install Complete Password eee InstallAnywhere by Macrovision Cancel Previous Gateway URL Enter the gateway URL to connect to the Cognos server This is a required field A gateway is an extension of a Web server program that transfers information from the Web server to another server Gateways are often CGI programs but may follow other standards such as ISAPI and Apache Modules Namespace Enter the logon namespace to log on to the Cognos server as a full authentication provider Namespace is a configured instance of an authentication provider that allows access to user and group information for authentication and access control User and Password Enter the username and password combination that is authorized to access applications and resources on the Cognos server The username is part of the credential created on the Cognos server for user authentication For information on setting up credentials on the Cognos server see the Cognos server installation guide Click Next to continue On the second Cognos Server Configuration screen enter the Cognos installation directory Using QAD BI OpenEdge 10 Edition 99 Fig 1 62 Cognos Server Configuration Cognos Server Configuration Cognos Server Configuration Cognos Instal
14. product profitability forecast and so on providing at a glance business visibility to CEOs sales VPs and sales directors alike Warehouse Management a collection of reports on inventory turnover on hand quantity warehouse usage inventory carrying cost and so on Purchasing Management a collection of reports on received and returned purchased quantities goods receipt efficiency and related cost and so on It helps better manage raw material costs and supplier performance Using QAD BI OpenEdge 10 Edition 11 See Installing and Configuring QAD Provided Dashboards on page 95 for information on implementing dashboards Data Transformer User Interface Important This guide is limited mainly to descriptions of the features of the QAD BI Data Transformer For information on user interfaces for associated tools such as Cognos PowerPlay see the user documentation for those products The QAD BI Data Transformer features a Windows based graphical user interface Windows conventions are used throughout the product for example drop down menus shortcut menus drag and drop and so on This section describes user interface features specific to the QAD BI Data Transformer Starting As part of the initialization process Data Transformer prompts for user credentials Fig 1 2 AEE QAD BI Data Transformer Log in Screen User Password r Language English en QAD A Passion For Manufacturing The c
15. replicated to the data warehouse Syntax for the filter always has the same format WHERE amp SRC DB tablename fieldname operator value AND OR amp SRC DB tablename fieldname operator value Operator can have the following values gt lt lt gt matches begins When this is blank all records are replicated When you save your changes the system validates the syntax Click OK to save your changes and display the field mapping screen Map Fields to Warehouse Fields Use the Field Selection frame to select the fields from the source table that will be replicated to the data warehouse table Note After initial replication setup access this function by selecting a table and choosing ToolslField Mapping Note If you loaded the database structure from QAD provided metadata you do not have to use this procedure unless you want to modify the default field mapping structure The first column indicates whether a field is included in the replication process When a field is selected on this screen values are copied from the source database to the data warehouse during replication Important When you first set up table replication the DB NAME and REC ID for Progress data sources fields are automatically configured These system defined fields are used to maintain links between the data source and the data warehouse You cannot deselect them AQAD 32 User Guide QAD Business
16. see QAD BI Parallel Replication Setup on page 178 Performing Administration Tasks Update User Records During installation a single default user is defined with database administrator rights in the Progress Administration database Default user ID dwh Default password admin FAQAD 174 User Guide QAD Business Intelligence Fig 2 52 Updating User Data FAQAD You can modify this default information as well as create additional users or delete existing ones with the following procedure 1 2 Start Progress Data Administration Select DatabaselConnect and enter connection information on the Connect Database screen If the default installation was performed the connection parameters are Physical Name dwh Logical Name dwh Network TCP Multiple Users checked Host Name localhost User ID dwh e Password admin x PhysicalName dwh Browse I Logical Name dah 0 Cancel Database Type PROGRESS Options lt lt Help Network TCP M IV Multiple Users Host Name flocalhost Service Name oo User ID jn Password Lo sis Trigger Lcaon tti CS O C Browse Parameter fief Browse Other CONNECT Statement Parameters 3 Select AdminlSecuritylEdit User List 4 On the Edit User List screen click Add Modify or Delete to manage For additional information on managing users in a Progress database see user
17. to start and stop the data warehouse Data Warehouse ODBC Connection Cognos Transformer Replication Batch Process AppServer Notification Database AgdbipwHidwh db Browse Repository es qadbi dwh admin dwh admin db Browse Database Management Fingoss Explorer z Use non integrity Parameter Log Language Log tevet Eror z I Logtons Scripts Start Stop rogress Explorer Database Emm Port 20931 Host flocahost User NENNEN Config deFaultConfiguration Password cm Database Enter or browse to the location of the data warehouse file By default this is the dwh db file in the directory where QAD BI is installed Repository Enter or Browse to the location of the data warehouse repository By default this is the dwh admin db file in the directory where QAD BI is installed Database Management Select the method to use to start and stop the data warehouse Progress Explorer or Scripts Depending on this setting you can update the fields in the appropriate frame Single User Batch Process Specify whether the data warehouse database is started in single user mode when running the batch process If selected no other connections to the data warehouse are accepted until the batch process completes This field is not available when parallel replication is selected on the AppServer tab that is
18. 10 Edition 79 Generated Scripts Directory Enter or browse to the location where the automatically generated table replication scripts are to be written Custom Scripts Directory Enter or browse to the location where the custom scripts are located System Scripts Directory Enter or browse to the location where the system task scripts are located IQD Files Directory Enter or browse to the location where the IQD files are located Work Directory Enter or browse to the location of the ETL tool This directory contains the encrypted source code log files and temporary files and should not be changed unless recommended by QAD Support Automatically Resort Fields Alphabetically Select to have the fields columns of the mapped table automatically sorted on the field mapping screen Automatically Check ETL Scripts Select to automatically generate the mapped table scripts and to compile the generated and custom scripts This process also applies after a metadata import IQD Source Specify the data source name set up in Cognos Access Manager to connect to the data warehouse database By default this is dwh Currency Select a currency from the list of currency codes in which to express all currency values defined in the standard business models The default currency is the QAD ERP base currency AQAD 80 User Guide QAD Business Intelligence ODBC Connection The ODBC Connection tab includes parameters used by Cognos T
19. AppServer tab in AppServer on page 86 Multicurrency Configuration This section provides the information required to enable QAD BI to extract data from MFG PRO sources that have differing base or system currencies and to convert the currencies to a single user defined data warehouse currency for the standard business models Overview Multicurrency support in QAD BI allows amounts valued in different currencies to be revalued in a common currency within the standard business models This provides a foundation for analytic operations that compare and aggregate amounts that were originally valued in different currencies Important The Sequence dependencies for multicurrency scripts must be observed to get the correct results See Setting the Replication Task Sequence on page 189 for information Reference 185 Configure the Data Warehouse Currency A single currency can be defined as the data warehouse currency in Data Transformer When a data warehouse currency is defined all source system currencies are revalued using the Exchange Rate table see next section in this currency If no data warehouse currency is defined the data warehouse is considered a single currency implementation and no conversions occur To set the Date Warehouse currency follow these steps 1 Choose SettingslApplication Settings from the menu 2 Select the data warehouse tab and select the desired currency from the Currency drop down list 3 Click OK
20. Class cm Models Enter or browse to the location where the Cognos model files were installed or copied Data Temp Enter or browse to the location that the Cognos Transformer should use to store temporary data files created during cube generation Model Temp Enter or browse to the location that the Cognos Transformer should use to store temporary files created during model operations Cubes Enter or browse to the location where the Cognos Transformer should create the cubes Log Enter or browse to the location where the Cognos Transformer and Data Transformer should write the log files The following optional properties are used when building Cognos models in which Cognos security has been enabled Cognos security is set up using the Cognos Access Manager and stored in a local authentication export 1ae file or LDAP compliant namespace Fig 1 53 Application Settings Cognos Transformer Tab FAQAD 82 User Guide QAD Business Intelligence Fig 1 54 Application Settings Replication Tab AQAD Login Specify the log in for a Cognos user whose authority will be used to regenerate the models Password Specify the password for a Cognos user whose authority will be used to regenerate the models User Class Specify a Cognos User Class to which the specified log in user belongs and which will be used to regenerate the models For additional information regarding the use of th
21. Domain Fig 2 12 Select Domains Menu Item AQAD 126 User Guide QAD Business Intelligence Fig 2 13 Select Domains FAQAD Select Domains xj Available Domains Selected Domains QAD st92bmfg Select Tables To begin constructing the data warehouse you first select the tables you want to add to the data warehouse from those in the connected data source Note If you loaded the table mapping structure from QAD provided metadata you do not have to use this procedure unless you want to modify the default table selections To view the available tables right click the database icon in the left pane of the main Data Transformer screen Then choose Select Tables The system scans the schema definitions of the connected database and displays the Select Source Tables screen illustrated in Figure 2 14 Fig 2 14 xi Select Source Available Tables Master Files Tables D E E EE Assi jeta ac_mstr Account Master absi_mstr Shipper Information Master absl det Shipment Detail Line Charges absr det Shipment Requirement Detail abss det Shipment Sequence Detail accd det Asset Cost Change Detail acdf mstr Account Default Master acm mstr Co product By product Average Cost Master acod mstr Application Code Master act mstr Alternate Container Master acd det El had dar Historic Transactions acx mstr Account Cross Reference Master adc ctrl Address Control adx det External Address Cross Reference Deta
22. Intelligence Fig 1 16 Field Selection Fig 1 17 Field Properties FAQAD E Field dwh admin fld map E OP code _qadc01 OFS code desc LIE code fldname OFS code user Severity _ ZIP severity De inl xl Source Name Label Description DataType Format Extent 1ndex _ Sequence DWH Descriptio code_qadc01 character x 8 code_desc Description character x 24 code fldname Field Name The field for which t character x 32 code useri Ufldi User field 1 character x 8 code user2 Ufld2 User field 2 character x 8 theDbName character x 32 RECID amp SRC DB integer gt gt gt gt gt gt 9 The allowable or sug character x 8 The user s comment character x 40 code value Value code cmmt Comments The allowable The user s com Specifying Field Properties To define the properties associated with each field select it and click the Properties command Note You can also define custom calculated fields by clicking the Add Field command The system displays a Field Properties screen with all data fields blank and input enabled Field Field Properties DWH Field Name Jamount_SRC_ Field Type pma z Convert String to Upper Case Precision fis gt Decimals 3 _ Width fea gt Description PC O O Expression ap_base_amt Cancel DWH Field Name Enter the name used to represent this field in the data warehouse The
23. Name Action PurchaseManagerHomepage Create PurchaseManagerHomepage None SalesManagerHomepage None test None Test ABC None WarehouseManager Homepage None Name PurchaseManagerHomepage SearchPath content package name qadbi_purchase folder name dashboard s folder name purchasemanagerdashboard report name Purch aseManagerHomepage Create Replace Report Resource Yes Code PurchaseManagerHomepage Fi Go 2 Help 4 End 7 PgUp 8 PgDn 9 LnUp 10 LnDn Enter or select from the mismatching report list the name of the dashboard home page that you want to take action on Set the action on the selected dashboard home page to Create The Create action creates a new report resource in the system from the selected report on the Cognos server The actions you choose will be displayed for the corresponding report in the mismatching report list Note The actions will not be executed until you save the changes Enter a unique report code identifying a report resource in the system The code defaults from the name of the specified dashboard home page Press Enter Repeat steps 4 through 6 for all the dashboards you want to synchronize from the Cognos server to QAD ERP Press End and choose Yes to execute the synchronization Using QAD BI OpenEdge 10 Edition 105 Create Menu Entries for the Dashboards Create menu entries for the new dashboard home pages using Menu System Maintenance 1 2 L
24. Table 1 7 Icon Description Icon Description Status Icons Running Pending E 95 Stopping Stopped E Eb Finished Error 5 Fi Monitor and Manage Currently Running Tasks By default BI Monitor displays currently running task sets in the task set pane Select a task set to display all its tasks in the task pane Select a task in the task pane to view its detailed information in the lower section of the task pane Specify search criteria in the search box at the top of the task pane and click Search to filter tasks by task name task status and database To stop a currently running task select it and click the Stop Task button This stops the selected task and starts execution of the next task in the queue By default the running tasks task sets screen refreshes every 10 seconds You can change the refresh rate by choosing Preference from the Settings menu and specifying a new value in the Preference dialogue box You can also manually refresh the screen by clicking the Refresh button at the bottom To disable the auto refresh feature clear the Auto Refresh option from the Control menu You can define how often task events are logged by choosing Settingsl Preference and specifying the Logging Frequency value in the Preference dialogue box FAQAD 94 User Guide QAD Business Intelligence AQAD View Tasks and Task Sets Logs To view history information of tasks and task sets select Log in the search
25. The date the order is to be ship E Exchange R L Hnvoice Date The date that the invoice For th Ej Invoiced A flag indicating an invoice has Ej Order Date The date the order was enterec Fa Order Type Code to distinguish normal quo By Project_ Project number Ey Purchase O The customer purchase 8 gt Pot Billto_Address_ The address code For th By oz Customer_ The address code for tt By 03 Invoice_ The invoice number tha Enter a name for the new index The left pane displays all the fields in the data warehouse table or subset that are available for indexing Drag and drop fields to the right pane to include them in the index Important After adding or deleting an index you should validate the synchronization script using these steps 1 Select the updated table on the main screen 2 Choose Check Scripts If the new index validates properly a green triangle displays on the table icon Otherwise a yellow warning icon displays Maintain Business Keys For Progress data sources QAD BI Data Transformer uses the DB NAME and REC ID fields to maintain links between the data source and the data warehouse However this approach is not always reliable in some circumstances When the user dumps and loads tables in the source database the REC ID values of the records in these tables are changed FAQAD 48 User Guide QAD Business Intelligence FAQAD Data is archived and
26. and a preview of the script contents Important Not all scripts in the custom folder are to be loaded into Data Transformer Table 3 3 lists the custom scripts distributed for the current version Only primary scripts should be loaded Loaded secondary scripts will fail validation Table 3 3 Primary Scripts Secondary Scripts Custom Primary and Secondary Script Balance Sheet Totals p None Scripts Script Call Billing Detail p None Script Call Lead Time p Script Call Lead Time2 p Script Engineer Hours Available p Script Engineer Hours Available2 p Script Engineer Hours Available3 p Script Forecast p None Script Forecast Simulation p None Script Format Positions p Script Format Positions2 p Script Invoice Discounts p None FAQAD 196 User Guide QAD Business Intelligence Table 3 4 Multiple Currency Scripts FAQAD Primary Scripts Secondary Scripts Script_Invoice_Trailers_ p None Script_Purchase_Statistics_ p None Script_Repeat_Calls_ p Script_Repeat_Calls2_ p Script_Sales_Order_Discounts_ p None Script_Sales_Order_Trailers_ p None Script_Sales_Statistics_ p None Script_Stock_History_Week_ p Script_Stock_History_ p Script_Stock_History_Month_ p Script_Stock_History_ p Script_Currency_Fields_Incremental_ p Script_Currency_Fields_ p Additional scripts should be loaded if multiple MFG PRO base currencies wi
27. audit enabled Audit based replication is Audit based replication is not Be used and the table is audit n used but the table is audit enabled enabled FAQAD Using QAD BI OpenEdge 10 Edition 17 Setting Up the QAD BI Data Transformer This section describes the steps necessary to connect to a QAD ERP database that provides source data to the QAD BI analysis tools as well as define the tables and fields that are included in the analysis models Additionally you control how and when data is replicated between this database and the data warehouse which serves as the actual source of data used in analysis You can use one of the following methods to set up Data Transformer Perform a manual setup by defining tables and fields based on the QAD ERP database schema If you purchased one or more of the QAD developed business models load table definitions and scripts then use Data Transformer tools to fine tune your environment as needed See Using QAD Provided Model Data on page 73 for information AQAD 18 User Guide QAD Business Intelligence Figure 1 4 summarizes the work flow used in the setup tasks Subsequent paragraphs describe each task Fig 1 4 QAD BI Setup Work Flow Define database connections Load QAD defined models Select tables from ERP from meta data database If required adjust table and field 1 properties create additional subsets an
28. box at the top of the task set pane Specify a start date and an end date and click Search to view all task set runs executed within this time range If no start date is specified the check box next to the date is cleared the search lists all task set runs executed before the specified end date If no end date is specified the search lists all task set runs executed after the specified start date If neither start date nor end date is specified the search lists all executed task set runs Select a task set to display all its tasks in the task pane Select a task in the task pane to view its detailed information in the lower section of the task pane Specify search criteria in the search box at the top of the task pane and click Search to filter tasks by task name task status and database Tasks task sets log screen does not refresh automatically You can refresh the screen manually by clicking the Refresh button Archive Tasks and Task Sets Logs Log information stored in the administration database can grow large over time and bring down system performance Archiving frees up space and improves the performance of the administration database by dumping log data in table contents d files when it is old or not in use anymore If required Table contents can be loaded back to store log information in the administration database To archive log data 1 Choose Archive from the Control menu 2 Inthe Archive dialog box specify a date
29. date then ontime ontime l dwhq Transaction History tr qty chg duhq Invoice Lines idh qty ord else toolate toolate 1 dwhq Transaction History tr qty chg dvhq Invoice Lines idh qty ord Adding Scripts to Data Transformer To add a custom script right click the Custom Scripts folder on the main screen and select Add Script Fig 1 33 Add Script Add Custom Script Script Details Schedule rETL Script ETL Script C aecbiethastevscripts custom Script Balance Sheet Totals p Browse Script Name ecrit Balance Sheet Totals Script Preview itappl repl def iy itappl gdefs iy appltask engine dets i amp SCOPED DEFINE SRC DB SRCDB j amp SCOPED DEFINE TRG DB DWHQ E n P xl FAQAD 58 User Guide QAD Business Intelligence Fig 1 34 Merge Tables AQAD Click Browse to navigate to the directory containing the Progress script The system displays the contents of the script You can update the default script name as needed However you cannot update the content of the script itself on the preview screen On the Schedule tab specify when this script is run This is the same as scheduling replication for standard tables For a more detailed discussion of setting the sequence for a replication task see Setting the Replication Task Sequence on page 189 Merge Data from Multiple Databases You can use the merge feature in Data Transformer to con
30. default is the QAD ERP schema field name Note You also can rename a field directly on the field selection screen by selecting its name and overtyping it Using QAD BI OpenEdge 10 Edition 33 Field Type The system displays the field data type from the QAD ERP schema You cannot change it Convert String to Upper Case If the field type is Character set this to Yes to convert the field values to upper case in the ETL script This can be used to rationalize data that is entered in mixed cases in the data source for use in Cognos PowerPlay a case sensitive tool Precision Decimals If the field type is Decimal change precision and decimal places of values stored in the field Description Enter a text description of the field Expression The system displays the field name or formula associated with the field Click OK to save the properties of an individual field When you finish specifying field properties for the table click OK to save your configuration The system validates each field record for syntax Valid A green triangle on the table icon on the main screen indicates that replication setup is complete for the table Invalid A yellow exclamation point displays on the table icon and a message indicates the nature of the error Important To modify field properties for tables that have already completed replication setup you cannot access the Field Mapping function through the Replication Setup screen You mus
31. downloading from the QAD Online Support Center at http support qad com You can register fora QAD Web account at the QAD Online Support Center Your customer ID number is required Access to certain areas is dependent on the type of agreement you have with QAD Most user documentation is available in two formats Portable document format PDF PDF files can be downloaded from the QAD Web site to your computer You can view them with the free Adobe Acrobat Reader HTML You can view user documentation through your Web browser The documents include search tools for easily locating topics of interest AQAD 4 UserGuide QAD Business Intelligence FAQAD Conventions This document uses the text or typographic conventions listed in the following table If you see It means monospaced text A command or file name italicized monospaced text A variable name for a value you enter as part of an operating system command for example Your CDROMDir indented command line A long command that you enter as one line although it appears in the text as two lines Note Alerts the reader to exceptions or special conditions Important Alerts the reader to critical information Warning Used in situations where you can overwrite or corrupt data unless you follow the instructions 17 95 6 User Guide QAD Business Intelligence FAQAD Overview The QAD B
32. drop down list determines the language in which text labels and menus display Important If you chose to enable the support of multiple code page data sources during installation a warning message displays after log in Ignore it and choose OK It has no impact on your application and database Using QAD BI Progress 9 Edition 113 Fig 2 3 x Log In Warning x WARNING Using cpintemal UTF 8 is not supported for GUI or TTY clients You may corrupt files or databases 9429 Navigating Navigation within the Data Transformer provides up to three methods of accessing individual functions depending on the type of action being performed Menus on the Data Transformer main screen Toolbar commands Context sensitive shortcut menus The primary view of the Data Transformer is the main screen which includes a toolbar a menu bar and a two pane navigation tree The left pane shows the current structure of the data warehouse When completely minimized the top level tree is a series of category folders When completely maximized it lists all the tables and custom scripts in the warehouse Select a table to display its fields in the right pane Fig 2 4 ioii QAD BI Data Fie Tools Settings Transformer Main in ZE EIE VET ies Ej localhost eB2 ce Active Transactions C3 Code Tables 2 e Historic Transactions Master Files Custom Scripts O Cubes Custom Tables O System Tasks
33. of domains can be set up in one physical database each domain with its own base currency chart of accounts and operating controls You add the domain database just as any other database Data Transformer retrieves a list of all domains defined in the database each domain is displayed as a subnode of the domain database From that point forward a domain is treated exactly as any other database in the system and represented with the Database Connected and Database Disconnected icons Important Only the domain database itself can be connected or disconnected Individual domains cannot be connected or disconnected because they are physically realized in a single database All domains are connected based on their domain database s status Text is appended to the logical names of the domain database and each domain in the tree to indicate the type of source system domain database or domain blade which represents the individual domains within the database Additionally the Database Properties screen for an individual domain allows only the Active field to be modified All other fields are read only and based on the information entered for the domain database eu Data Transformer File Tools Settings E Ta 8 ges 5 amp 2 Bx Ej Local Connections E y ExchangeRates Schema Holde CorpExchRates ODBC Sche Custom Scripts E z Western Custom Scripts BE Corp Domain Database E QAD Domain Blade Ej E st92
34. or browse to the appropriate script files that are used to start the data warehouse Stop If Database Management is Scripts enter or browse to the appropriate script files that are used to stop the data warehouse Using QAD BI Progress 9 Edition 171 Database Enter the data warehouse database name as defined in the Progress Explorer Host Enter the Progress server defined in the Progress Explorer and assigned to manage the data warehouse Config Enter the Progress Explorer Configuration to use to start and stop the data warehouse Port Enter the port or service the data warehouse runs under This is defined in the Progress Explorer Configuration User Enter a user authorized to start and stop the data warehouse Password Enter the password of the user authorized to start and stop the data warehouse Notification The Notification tab includes information the system uses to send e mail messages when an error occurs during batch processing Fig 2 50 x Application Data Warehouse Table Linking Cognos Transformer Batch Process Settings Notification Tab v Enable Notification SMTP Server smtoServer somecompany com e mail Address smtoServer somecompany com Return Address fiohn_doe somecompeny com Enable Notification When this is selected e mail is sent when errors occur during batch processing SMTP Server Enter the name of the Simple Mail Transfer Protocol server to use to send the e
35. ossi sda law sa ada meds cR m RR HORE Rod 45 FAQAD iv User Guide QAD Business Intelligence Maintain Business Keys 0 0 0 cece ees 47 Preview Data 0 6 ee eee oe ek eee bee sea ee ea betes 49 Delete Target Data 2 00 54 cae tc br eee e 50 Validate Tables s ccacce4 sia a0 seda diana cene ian RP HR sas 51 Create Custom Tables sseleeee eese 54 Create Custom ETL Scripts 0 0 2c eee eee 55 Merge Data from Multiple Databases 0 004 58 Use System Tasks uoce be cede ebria b Rae edd 59 Use the Task List uec Deep Y E 61 Export Tables i isset eR RR Re EUR ERN ee 64 Rebuild Links eode e pas eee eta oh e ct e 64 Using the SQL Query Analysis Tool llle 66 Maintaining Parameters 0 eee eee en 67 Working with the Model 0 0 c eee eee eee 69 Using QAD Provided Model Data 0 0 cece eee 73 Load Metadata eese tee eed bea eee ea et 74 Bulk Generate and Validate Scripts 0 00 cece eee eee 76 Complete Model Setup 0 0 cece eee eee TI Modifying Application Settings lees TI Data Warehouse iiic seda cac nda eia dede niim eee ends 78 ODBC Connection ssseseeeee ete eee 80 Cognos Transformer socca seitei eia E ee 81 Replication 20 25 22h dacs d ea sp A CIE Rd iba ies 82 Batch PLocess 5er uices abut e eluted ee ess bubo de Re b doe d 83 ADDSeIVeE dareek oiii see cR DUO RR ee eee ba ea eta 86 Noti
36. passed validation A green triangle indicates a validated script a yellow exclamation point indicates a problem For example scripts FAQAD 164 User Guide QAD Business Intelligence shown in Figure 2 45 were validated before all the custom scripts were loaded A secondary custom script Script Call Lead Time2 failed validation Fig 2 45 Validation Icons zi B localhost 5 ES El Active Transactions H D Code Tables E Historic Transactions EE Master Files E Custom Scripts T Script Balance Sheet Totals Z Script Call Biling Detail f Script Call Lead Time2 ier Note You can check the script for an individual table by right clicking the table on the main screen then selecting Check Script Complete Model Setup After loading model data and checking scripts complete setup tasks by Updating the table links for all replication and custom tables see page 39 Scheduling automatic replication for each table custom script and cube as shown in the relevant sections above Optionally fine tuning the data warehouse structure and replication setup Use the following sections for reference Manage Domains on page 124 e Select Tables on page 126 e Set Up Replication on page 128 Modifying Application Settings Based on information entered during the installation most settings required to connect the components of QAD BI are in place when you begin using the application
37. process When a field is selected on this screen values are copied from the source database to the data warehouse during replication Important When you first set up table replication the DB NAME and REC ID for Progress data sources fields are automatically configured These system defined fields are used to maintain links between the data source and the data warehouse You cannot deselect them Using QAD BI Progress 9 Edition 131 Fig 2 17 oix Field Selection S 9s Source Name Label Description Datatype Format Extent 1 dex Sequence DWH Descriptio OF code_qadcO1 code qadcol character x 8 OES code desc code desc Description character x 24 Os code fldname code fldname Field Name The field for which t character x 32 Ll8scode useri code useri Ufldi User field 1 character x 8 OF code_user2 code_user2 Ufld2 User field 2 character x 8 E theDbName character x 32 RECID amp SRC DB integer gt gt gt gt gt gt 9 z code value Value The allowable or sug character 8 The allowable LAS a severity De code cmmt Comments The user s comment character x 40 The user s com Specifying Field Properties To define the properties associated with each field select it and click the Properties command Note You can also define custom calculated fields by clicking the Add Field command The system displays a Field Properties screen with all data fields blank and input enabled Fi
38. range in which log data will be archived and click OK If no start date is specified the check box next to the date is cleared all log data before the specified end date will be archived If no end date is specified all log data after the specified start date will be archived If neither start date nor end date is specified all log data will be archived Using QAD BI OpenEdge 10 Edition 95 3 An archiving progress bar displays When archiving completes you are returned to the BI Monitor main screen Installing and Configuring QAD Provided Dashboards If you purchased QAD provided dashboards you can implement dashboard reports in QAD 2007 1 or later using the applicable version of QAD NET UI Before installing QAD dashboards you must first install required QAD provided business models and generate corresponding cubes For dashboard Install model Generate cube PurchaseManager QAD BI Purchase Statistics Cube Purchasing Vendor Rating SalesManager QAD BI Sales Analysis Sales Analysis Cube QAD BI Forecasting Sales Forecast Performance Cube WarehouseManager QAD BI Inventory Current Inventory Analysis Cube Monitoring Inventory History Analysis Cube See Using QAD Provided Model Data 73 on page 5 and Working with the Model on page 69 for details on how to set up QAD provided business models and create cubes Perform the following steps to implement QAD provided dashboards Crea
39. screen directory tree by selecting the icon on the appropriate level and choosing an export command from the Tools menu For example to export the entire directory structure select the database icon and choose ToolsIExport Tables For an individual table select the table and choose ToolsIExport Table Meta Data The system prompts you to enter a directory path and file name for the exported data The resulting XML file includes the table structure of the selected items in the data warehouse as well as the contents of the replication scripts If your company has multiple instances of QAD BI installed you can use this feature to synchronize the setup data Additionally you can use the XML file as a backup To reload data from exported files use the Load Meta Data function described on page 74 Rebuild Links For Progress data sources QAD BI Data Transformer uses the DB NAME and REC ID fields to maintain links between the data source and the data warehouse However this approach is not always reliable in some circumstances When the user dumps and loads tables in the source database the REC ID values of the records in these tables are changed Data is archived and no longer exists in the source database In both cases the links between the data source and the data warehouse are broken and can no longer be used for replication purposes Continuing to perform data replication without first rebuilding the links may cause dat
40. scripts for each domain that you want to analyze Using QAD BI Progress 9 Edition 161 Perform the following tasks to load the model data and make it ready for use 1 Load metadata 2 Load custom scripts 3 Bulk validate custom scripts 4 Complete model setup Load Metadata To load the metadata that describes the data warehouse structure used in the models right click a database or a domain name if you are using an MFG PRO eB2 1 database on the main screen and select Import Meta Data The Choose Import File screen displays Fig 2 42 Choose Import File 2 x Chose Import File Look in amp 82 z ex E3 My Recent Documents 6 Desktop My Documents 2 Active Transactions xml 2 Code T ables xml 9 Custom Scripts xml amp Data Warehouse xml Historic Transactions xml 2 Master Files ml My Computer om LS My Network File name Active Transactions xml Places Files of type Source Files ml Cancel y Navigate to the directory where the model source files were placed during installation Typically this is qadbilnstallDirNdataNmetaNversion Where version is the MFG PRO database version for example c qadbi data meta eB2 1 FAQAD 162 Fig 2 43 Meta Filter Window FAQAD User Guide QAD Business Intelligence Select a file and click Open to load the metadata and create the data warehouse structure Th
41. sufficient access privileges to execute the Data Transformer on the target machine 6 Select the date and time for the task to begin 7 Click Finish to save the task AQAD 176 User Guide QAD Business Intelligence FAQAD 178 User Guide QAD Business Intelligence AQAD QAD BI Parallel Replication Setup This section provides the information required for configuring QAD BI for parallel replication It also provides QAD BI specific guidelines and information for configuring the Progress AppServer which is required to replicate tasks in parallel See the Progress document Building Distributed Applications Using the Progress AppServer for documentation describing the general setup and configuration of the AppServer Overview In order to improve replication performance during batch processing parallel or multi threaded processing of the replication tasks is supported using the Progress AppServer Parallel replication allows for more than one replication task to be processed concurrently by separate Progress processes In order to use the parallel replication strategy both Data Transformer and AppServer must be configured This section assumes the AppServer has been installed and configured using the default settings on the same server as Data Transformer Configure the AppServer The AppServer can be configured using the Progress Explorer application recommended or by directly modifying the ubroker properties file
42. the toolbar QLTool omm Fil Y 93 8 93 SQL Result EMI Create SQL query scripts under the SQL tab Click Check SQL Syntax icon on the toolbar to check if the syntax of the SQL statements you created is valid Using QAD BI OpenEdge 10 Edition 67 Click the Execute SQL icon on the toolbar to execute the SQL query and display the query results under the Result tab If the SQL query syntax is invalid an error message displays By default the query result table displays 50 records click the View All button to show all results Click the Export button to export the results to a CSV comma delimited file Click the View Execution Plan icon on the toolbar to view the query execution plan for the SQL query you created If the SQL query syntax is invalid an error message displays Click the Run and Export icon on the toolbar to execute the SQL query and directly export the query results to a CSV file Maintaining Parameters QAD BI provides a parameter maintenance tool for maintaining maintaining database level domain level or global parameters in custom scripts The parameter values can be retrieved in custom scripts using specific built in functions Click the Parameter Table icon on the toolbar to open the associated maintenance function Fig 1 42 Sij Parameter Table o Parameter Table Level Database gt Scope qaddb v Name Search DaysOfReplicationPeriod Global Global 20 w Goto
43. this page Select all on all pages lv Deselect all on all pages fl Note Toolbar commands are context sensitive they are enabled only when the function they represent is appropriate to the current task or selection Unavailable commands display in gray Other situations require you to right click to display and choose available functions For example to assign tables to the data warehouse structure you must right click the appropriate database and choose the Select Tables command There is no other access point for this function AQAD 16 User Guide QAD Business Intelligence Source System Icons Data Transformer distinguishes items in the left pane of the main screen the tree using icons Table 1 6 lists the icons and their descriptions Table 1 6 Source System Icon Description Icon Description Icons Server Table with script error g Fi Schema holder Subset Connected domain database Validated code table En Disconnected domain Non validated code table Xu database Fry Connected database or Code table with script error Er domain Fr Disconnected database or Validated custom script f domain hey Logical grouping Non validated custom script e Th Source table prior to Custom script with script error replication setup Validated table Cube Non validated table Custom table H 4 Audit based replication is Merged table e used but the table is not
44. to add a storage area using a database structure file provided with QAD BI prostrct add ICFDB qadbiinstalldir data utilities audit utilities addaud it st 2 Enable auditing for the database as shown proutil database name C enableauditing area Audit Data indexarea Audit Index deactivateidx When you do this the audit tables which are hidden tables are created in the database For more information see the OpenEdge document Getting Started Core Business Services Assign Audit Related Privileges 1 Connect to the database as the database administrator 2 Use Data Administration or the Data Dictionary character version to Set up any internal accounts User necessary to define audit administrators and audit data archives Set up audit security Create a database pass phrase so you can make sure audit data has not been tampered with AQAD 38 User Guide QAD Business Intelligence Fig 1 19 Table Properties Replication Tab AQAD Audit Enable Source Table and Configure Replication Settings Perform the following steps to audit enable a source table and configure its audit based replication settings 1 Set audit based as the replication method for the table See Define Replication Method on page 33 The table icon in the navigation tree changes to an icon with a capitalized A indicating that the table is audit enabled Right click the table icon and choose Enable Audit from the
45. to have automatic replication take place on the specified schedule When Activate is not selected you must replicate this table manually using ToolslExecute Now When a database is marked as Inactive you cannot change the Activate check box To set the database s Active Inactive status use the Database Properties screen Sequence Specify the relative order in which the task associated with this table should be replicated Tasks are executed from low sequences to higher sequences Tasks that share the same sequence may be executed in any order among themselves In general Tasks that pull data from sources into the data warehouse should be scheduled to precede tasks that operate on data within the data warehouse Tasks that generate Cognos Cubes should be scheduled to run after all other tasks For a more detailed discussion of setting the sequence for a replication task see Setting the Replication Task Sequence on page 189 Frequency Specify how often the replication task for this table should be run Options are Daily the default The table is replicated each day when the Windows scheduler starts up Weekly The system displays a list of weekdays select the days you want this table to be replicated Use Ctrl Click to select more than one list entry Monthly The system displays available dates as well as First of the Month and Last of the Month options Note The Windows Task Scheduler should be configur
46. to save the selected currency as the data warehouse currency Fig 3 9 m Seting the Data Data Warehouse Table Linking Cognos Transformer Batch Process Notification AppServer Warehouse Currency DWH Details Generated Scripts Directory e gadbitdwidetatscriptsigen Browse Custom Scripts Directory Jegadbitdw detatscripts custom Browse v Automatically Resort Fields Alphabetically v Automatically Check ETL Scripts Work Directory c asdtinet Browse Currency usp Configure Multicurrency Custom Scripts For source systems with differing base currencies defined Data Transformer supports conversion of currency values into a single currency for analysis that is the data warehouse currency To value multiple currencies in a single currency additional custom scripts must be added to the source metadata and configured for replication AQAD 186 User Guide QAD Business Intelligence Fig 3 10 Setting the Schedule Fields for Script Currency Fields Incremental p AQAD In addition to setting the data warehouse currency in the Application Settings you must perform the following steps to enable multicurrency support in QAD BI Note These steps assume the table tasks have all been configured with a Sequence value less than 10 by default they will have the value 00 and that the Custom Scripts Sequence values are greater than 15 1 Start Data Transformer and navigate to the Custo
47. tree Select Schema Images xj Available Schema Images Selected Schema Images i CorpExchRates Manage Domains When a domain database is first added Data Transformer retrieves all defined domains and creates additional connection records for each one These domains are created with the Active field set to false You must set this field to true for any domains you want to replicate before they can be scheduled for the batch process Select domains to include using either the standard Delete function or the Select Domains screen ou Data Transformer Fie Tools Settings ex BY 85 malme 3i f stoa fs Western S gade Domain m eva Domain Rladel Using QAD BI OpenEdge 10 Edition 27 Use the Select Domains screen to choose which domains you want to work with and remove those you do not need to replicate Move domains from the Available Domains list to the Selected Domains list to have them show up in the tree Fig 1 12 x Select Domains Available Domains Selected Domains QAD st92bmfg gt gt duds il Cancel Select Tables To begin constructing the data warehouse you first select the tables you want to add to the data warehouse from those in the connected data source Note If you loaded the table mapping structure from QAD provided metadata you do not have to use this procedure unless you want to
48. 0000 0 1040 5000000 0 0 0 5000000 0 1040 5000000 0 0 0 5000000 0 1040 5000000 0 0 0 5000000 0 1060 2449 93 2449 93 0 0 0 1060 500000 0 0 0 500000 0 1060 2449 99 2449 99 0 0 0 0 1060 500000 0 0 0 500000 0 1060 500000 0 0 0 500000 0 1060 1000000 0 oO 0 1000000 0 1060 500000 0 0 0 500000 0 1060 500000 0 0 0 500000 0 1060 1000000 0 0 0 1000000 0 1060 500000 0 iu 0 500000 0 1060 500000 0 0 0 500000 0 1060 500000 0 0 0 500000 0 3336 3 3336 3 oO 0 0 0 P Preview Number of Records 100 Refresh Delete Target Data You can delete data records after they have been replicated to the data warehouse You can delete all data records from a table while preserving its table mapping information AQAD Using QAD BI Progress 9 Edition 145 Choose Delete Target Data from the Data TransformerlTools menu to open the Record Deletion Status screen Fig 2 29 Delete Data Records from Data Warehouse x T Processing records 1000 to 2000 Total update time 00 00 00 Total deleted records 1501 Processed 1000 Records 01 53 14 Processed 900 Records 01 53 14 Processed 800 Records 01 53 14 Processed 700 Records 01 53 14 Processed 600 Records 01 53 14 gt cele 4 294 4 Stop To start the deletion process click Start If it is necessary to stop the deletion before completion click Stop Validate Tables The Validate Table function on the Data TransformerlTools menu lets you validate table and field m
49. 128 148 menu Data Transformer 13 114 merge data 58 152 metadata 28 74 127 161 MFG PRO database 7 109 connecting to 17 118 domain 23 121 Microsoft Access 111 166 database 110 server 64 154 multicurrency configuration 184 N navigation Data Transformer 12 113 notification 88 171 0 Oracle database 24 123 overview 6 108 P parallel replication 178 Progress DataServer 24 122 R rebuild links 64 155 recreating tables 53 147 reference information 178 197 replication automatic 39 134 data 39 49 134 143 fields 31 43 130 138 manual 42 136 method 33 132 multicurrency 187 parallel 178 FAQAD setup 29 128 table validation 51 145 tables 29 42 128 136 task sequence 189 S script validation 76 163 setting up BI 17 118 source system icons Data Transformer 16 117 system tasks 59 T tables custom 35 134 export 64 155 linking 80 166 mapping 29 54 128 148 re creation 53 147 removal 29 128 rename 42 137 selection 27 126 status 54 148 subsets 13 114 validation 51 145 toolbar Data Transformer 13 114 U user log in 11 112 records 88 173 using QAD BI 6 91 108 175 V validation scripts 76 163 tables 51 145 Ww Windows Task Scheduler 90 175
50. 142 User Guide QAD Business Intelligence FAQAD Add Business Key Indexes For Progress data sources QAD BI Data Transformer uses the DB NAME and REC ID fields to maintain links between the data source and the data warehouse However this approach is not always reliable in some circumstances When the user dumps and loads tables in the source database the REC ID values of the records in these tables are changed Data is archived and no longer exists in the source database In both cases the links between the data source and the data warehouse are broken and can no longer be used for replication purposes Continuing to perform data replication without first rebuilding the links may cause data corruption in the data warehouse This is where business key index comes into play You can designate indexes that can represent unique identifiers of records as business key indexes to construct more reliable links between the data source and the data warehouse You can then use the business key indexes to synchronize the REC ID values between the source and target databases to re establish the links based on the REC ID field for replication purposes Note REC ID cannot be designated as business key index due to the reason stated previously You can only define one business key index for a table If you are certain the business key index you define can uniquely identify each record designate it as a complete business key index otherwis
51. 20 Table Properties Scheduling Tab AQAD processors is created and each processor is assigned a task to process As a processor finishes a task it is assigned another task to be processed until all tasks have been performed with respect to sequence order Note The processor pool size is set to one less than the value of the Minimum and Maximum Servers settings on the AppServer properties screen See Pool Size of Script on page 86 for details In order to use parallel replication the Progress AppServer must be installed and configured on the same machine as the QAD BI Data Transformer For information on setting up and using parallel replication and configuring the AppServer see QAD BI Parallel Replication Setup on page 178 Use the Scheduling tab to define when replication takes place for each table in the data warehouse You can set up different scheduling methods for individual tables based on the frequency of changes to the source table Important Automatic scheduling uses the Windows Task Scheduler to start a QAD BI process to review the scheduled replication tasks on a periodic basis See Set Up Windows Task Scheduler on page 90 for information Table Properties x Table Replicati Replication Schedule Options IV Activate Sequence oo Monthly Update On 1 First of The Mor omen Using QAD BI OpenEdge 10 Edition 41 Activate Select this field
52. 36 User Guide QAD Business Intelligence FAQAD updated and deleted data in the source database is synchronized to the target database which significantly improves the replication performance This method requires that the source database is OpenEdge 10 1B or higher and is audit enabled and the user connecting to the source database has the authenticated audit administrator privileges for that database You must also perform additional steps to audit enable source tables you want to replicate data from and configure replication settings before you can successfully execute audit based replication tasks Use Audit Based Replication with Enhanced Controls If you use QAD BI s audit based replication feature and QAD ERP s auditing solution at the same time you must institute the following practices to preserve data integrity If you have already enabled BI audit based replication and then want to implement QAD ERP s Enhanced Controls module you must execute audit based replications first before you enable the audit trail functions of Enhanced Controls Archiving audit data moves data from audit data tables to a specially designated database If you archive audit data that has not been used by the BI audit based replication process audit information will be lost from the source database and changes will not be synchronized to the data warehouse database during subsequent audit based replications Therefore before audit data in t
53. Cognos PowerPlay and the data warehouse Use the Data Transformer Update Links command to synchronize table references in Access with the tables and fields available in the data warehouse Important You should update links anytime you add or update a table in the warehouse You can run the command either for an individual table or for the entire data warehouse by choosing the appropriate icon from the main screen directory tree and clicking the Update Links command For an individual table select its icon For the entire warehouse select the database icon After a metadata import scripts can be validated and links updated automatically based on settings in defined in SettingslApplication Settings See Automatically Refresh Linked Tables on page 167 for details Using QAD BI Progress 9 Edition 155 Export Tables You can export table information in XML format on any level of the main screen directory tree by selecting the icon on the appropriate level and choosing an export command from the Tools menu For example to export the entire directory structure select the database icon and choose ToolsIExport Tables For an individual table select the table and choose ToolslExport Table Meta Data The system prompts you to enter a directory path and file name for the exported data The resulting XML file includes the table structure of the selected items in the data warehouse as well as the contents of the replication scrip
54. Counter AS INTEGER NO LINDO i Set the KeepAlive variables DEFINE VARIABLE vKeepAlive AS INTEGER NO UNDO DEFINE BUFFER KeepAliveBuffer FOR amp SRC DB ld det Set the current Sequence number DEFINE VARIABLE theSeqNbr AS INTEGER NO LINDO dafina variable dCi weise ac date nio iinda When you choose SettingslTask List the system displays a separate ETL script for each database Task List TaskSets default v Create Delete lolx BOA Dateese far oatabases gt Tsk search T Only Display Selected adb_det_dwh Daily Service_Contract_Details_ Daily Budget_Details_ Daily Sales_Orders_ Daily Call_Detail_ Daily Sales_Order_Details_ Daily Calls Daily Purchase Orders Daily Current Costprices Daily Purchase Order Lines Daily Inventory Details Daily Purchase Order Lines Daily Inventory Details Daily Purchase Orders Daily Goto fo Page QPDIV1 QPDIVI QPDIV1 QPDIVI QPDIVI QPDIVI QPDIVI QPDIV1 QPDIV1 QPDIVI QPDIV1 QP QP QP Active Active Active Active Active Active Active Active Active Active Active Active Active Active aa Master Files Active Transactions Active Transactions Active Transactions Active Transactions Active Transactions Active Transactions Active Transactions Subset Active Transactions Active Transactions Merged Tables Merged Tables Merged Tables Banna d Table in EE f Apply OK Cancel Use System Task
55. CustomTables p file under the DWHWorkDirNappl directory to customize custom tables DWHWorkDir is the data warehouse work directory that you set during installation or in Application Settings See Modifying Application Settings on page 164 for details To rebuild links click the Rebuild Link icon on the Data Transformer main screen Click Start on the status screen to start The system runs the rebuilding task listing status messages to indicate its progress Rebuild Status Window loj x Account Totals table 200 records rebuilt at 19 40 17 Account_Totals_ table 150 records rebuilt at 19 40 17 Account Totals table 100 records rebuilt at 19 40 17 Account Totals table 50 records rebuilt at 19 40 17 Account Totals rebuilding began at 19 40 16 Accounts Receivable Details rebuilding began at 19 40 16 Budget Details rebuilding began at 19 40 16 The buttons at the bottom of the screen control these functions Click Start to begin update Click Stop to end update Click Close to exit the screen After rebuilding links is complete you can review the rebuild log log file generated in the DWHWorkDirNlog directory for more detailed processing and error information Important Every time you want to dump load table or archive data in the source database make sure you perform these operations in the following order 1 Perform replication to refresh the data warehouse tables with updated values from the source databa
56. D ERP metadata must be loaded for each active domain in the database Important If this is first time you have loaded the standard XML metafiles ensure that you load Custom Scripts xml last After the custom scripts are loaded they are automatically compiled Since the scripts refer to several tables in the data warehouse the table schemas must have already been loaded for successful compilation to take place If these schemas have not been created in the data warehouse compilation will fail ei Data Transformer File Tools Settings B YeSaalmes Beg 8 localhost E en2 Active Transactions H O Code Tables Historic Transactions C Master Files Custom Scripts Ce nen Cub L Custom Tables Bulk Generate and Validate Scripts After loading model data and custom scripts you can have the system regenerate and check all the scripts at the same time based on the completed data warehouse structure Select the database on the main screen directory tree and then choose ToolslCheck Scripts The system updates the table icons in the directory tree to show whether they passed validation A green triangle indicates a validated script a yellow exclamation point indicates a problem For example scripts Using QAD BI OpenEdge 10 Edition 77 shown in Figure 1 50 were validated before all the custom scripts were loaded A secondary custom script Script Call Lea
57. DWH ADMIN Network Specify the protocol that the administration database is configured to use for communication Default is TCP Appserver Log Directory Specify the directory to which all log files will be written AppServer Error Log Specify the name of the AppServer error log AppServer Replication Log Specify the name of the AppServer replication log For additional information on using parallel replication with Data Transformer see QAD BI Parallel Replication Setup on page 178 AQAD 88 User Guide QAD Business Intelligence Notification The Notification tab includes information the system uses to send e mail messages when an error occurs during batch processing Fig 1 57 Application Settings Data Warehouse ODBC Connection Cognos Transformer Replication Batch Process AppServer Notification Notification Tab v Enable Notification SMTP Server smtpserver somecompany com e mail Address smtpServer somecompany com Return Address fjohn_doe somecompany com cm Enable Notification When this is selected e mail is sent when errors occur during batch processing SMTP Server Enter the name of the Simple Mail Transfer Protocol server to use to send the e mail e mail Address Enter the e mail address of the person you want to receive the e mail notifications Return Address Enter the e mail address of the person you want to be used for the return address on the e mail Performin
58. Data Transformer defining a subset is similar to setting up a table for replication The main difference is that the subset definition includes a filter specification to select the area of interest in the source table Instead of ToolsIReplication Setup choose ToolslSubset Using QAD BI OpenEdge 10 Edition 45 x Table r Table Mapping SourceTable Name ac mstr Table Type Subset DWH Table Name Source Table i E Cancel Table Type defaults to Subset you cannot change it Specify the data warehouse table name Then enter Progress code to filter the records that should be replicated in the data warehouse Syntax for the filter always has the same format WHERE amp SRC DB tablename fieldname operator value AND OR amp SRC DB tablename fieldname operator value Operator can have the following values gt lt lt gt matches begins After defining the subset you can continue to define fields and replication settings just as in the standard replication setup Add Indexes When you select tables for the data warehouse the system disregards any indexes defined in the data source Instead you can custom design indexes for each table after adding it to the warehouse This lets you test the performance of your data models first then add indexes that are appropriate to your specific needs Note You can view a list of indexes defined in the data so
59. Det E Invoice Det Customer A Customer A E Customer A H E Base Currency E Customer C Customer Cl DB NAME Format Positi Account DB NAME Country Country Code Name City DB NAME Customer _ Customer Cla DB NAME Customer Re DB NAME Customer Typ Customer Type DB NAME Entity _ Entity Name DB NAME Site Invoice Sales Order Invoice Line Sales UM Item Qty Invoiced Gross Line R ac mstr ac mstr ac mstr Base Currency ad mstr ad mstr ad mstr ad mstr ad mstr ad mstr Code mstr Code mstr Code mstr Code mstr Code mstr Code mstr Code mstr Code mstr Code mstr en mstr en mstr en mstr idh_hist idh_hist idh_hist idh hist idh hist idh hist idh hist idh hist ac fpos ac code Custom Table ad country ad ctry ad name ad city ad addr code cmmt code value code cmmt code value code cmmt code value en entity en name idh site idh inv nbr idh nbr idh line idh_um idh_part idh qty inv Using QAD BI Progress 9 Edition 159 Specify Model Properties To display the Model Properties screen select a model and choose Toolsl Properties The screen contains three tabs Cube Details The Cube Details tab contains read only displays of the Cognos Transformer model name and path Fig 2 39 Cube Properties Cube Details Tab Schedule The Schedule tab is the same as the Table and Custom Script Schedule tabs Sel
60. FAYQAD User Guide QAD Business Intelligence Using QAD BI OpenEdge 10 Edition Using QAD BI Progress 9 Edition Reference 78 0704A Business Intelligence 2 7 MFG PRO 9 0 through QAD 2008 Standard and Enterprise March 2008 This document contains proprietary information that is protected by copyright and other intellectual property laws No part of this document may be reproduced translated or modified without the prior written consent of QAD Inc The information contained in this document is subject to change without notice QAD Inc provides this material as is and makes no warranty of any kind expressed or implied including but not limited to the implied warranties of merchantability and fitness for a particular purpose QAD Inc shall not be liable for errors contained herein or for incidental or consequential damages including lost profits in connection with the furnishing performance or use of this material whether based on warranty contract or other legal theory QAD and MFG PRO are registered trademarks of QAD Inc The QAD logo is a trademark of QAD Inc Designations used by other companies to distinguish their products are often claimed as trademarks In this document the product names appear in initial capital or all capital letters Contact the appropriate companies for more information regarding trademarks and registration Copyright 2008 by QAD Inc QAD Inc 100 Innovation Place Santa Barbara Californi
61. K to save all changes To use defined parameters in custom scripts include the task engine defs i file and use the following functions to return parameter values of corresponding data types getParamInteger getParamDecimal getParamString getParamLogical getParamDate Using QAD BI OpenEdge 10 Edition 69 Use the following example as reference define variable param as integer no undo Define a variable assign param getParamInteger param name Call the function using parameter name as the parameter if param then Check returned value if the parameter is not found return This error message will appear in log return a error message which will be logged and the status of this script will be Error Working with the Model Cognos models supporting multidimensional analysis of business process areas can be purchased from QAD The models map the contents of the data warehouse to concepts supported by the PowerPlay product such as dimensions levels categories and measures The product of executing a model against the data warehouse is a Cognos PowerCube The location of the models is referenced in the Cognos Transformer tab on the Application Settings screen See Figure 1 53 on page 81 for details Note Cubes can only be built after data has been replicated to the data warehouse database You can Create the Cube Check the model View the model properties to specify t
62. SQL corresponds to each query subject or dimension externalized for use in Transformer By default IQD files are located in the qadbiinstalldir data db directory You can change the IQD files directory in Application Settings You can create new IQD files or edit existing IQD files in Data Transformer using IQD Editor To create a new IQD file right click the IQD Files folder in the application tree on the main screen and choose Add IQD File then enter an IQD file name and click OK To edit an existing IQD file right click the IQD file under the IQD Files folder and choose Edit In IQD Editor click Check Syntax to check the syntax of the sql scripts in the IQD file Click the Execute SQL icon on the toolbar to execute the SQL query and display the query results Click the View Execution Plan icon on the toolbar to view the query execution plan for the SQL query you created If the SQL query syntax is invalid an error message displays Using QAD BI OpenEdge 10 Edition 73 Click the Run and Export icon on the toolbar to execute the SQL query and directly export the query results to a CSV comma delimited file Fig 1 46 EXIIOD Editor Blea IQD Editor BY 85 8 3 File Name caadbildataldbnventory Details qd Data Source awh SQL Result Entity as Entity Entity Name as Entity Name Sites Site Description as Site Description Inventory Details Site as Site Inventory De
63. Synchronized After audit based replication has been executed the status changes to Synchronized and the system only synchronizes data changes since the last replication based on audit data in the source database However you can change that replication time point or reset the status to Not Synchronized to change the amount of changes to be synchronized Replicate From Specify the time point after which to capture data changes in the source table The system only synchronizes data changes taking place after that specific time point in the source table to the data warehouse based on audit information Every time a replication task is executed for the table the time stamp is updated to the latest task run time 5 Click OK to save the settings The table is set up for audit based replication Replicate Data You can synchronize data between a data warehouse table and the associated QAD ERPERP table using two methods Automatically by setting up scheduling for each table Manually by selecting the table on the main screen then clicking Execute Now on the Tools menu The following sections describe these methods Scheduling Automatic Replication Automatic replication takes place using either a serial or parallel strategy In serial replication each task is processed sequentially based on its defined sequence that is its priority With parallel replication a pool of AQAD 40 User Guide QAD Business Intelligence Fig 1
64. The following fields display in the Field Mapping frame Status Status information is displayed as follows OK Both source and data warehouse fields exist and are consistent Invalid The source table field cannot be found and the field mapping must be deleted Mismatch The source table field or the data warehouse field has changed and are inconsistent This field mapping can be rebuilt by overriding the target field with the source field AQAD 54 User Guide QAD Business Intelligence FAQAD Description For mismatched or invalid fields a brief description of the error Source Field The name of the field in the source table DWH Field The name of the field in the data warehouse table DWH Expression The expression used to calculate the value that populates the data warehouse field Function Currently not used When you click Delete Field Mapping after selecting the relevant check boxes the selected field mapping information stored in data warehouse admin is deleted but the field data in the data warehouse tables is retained When you click Close after performing Delete Field Mapping the system performs the Recreate DWH Table function Create Custom Tables Not all tables in the data warehouse have to be based on table definitions from the connected data sources You can create custom tables that exist only in the data warehouse Custom tables are populated with data using custom scripts They can contain vi
65. To start the replication setup select a table from the main screen and choose ToolslReplication Setup The Map Table screen displays Figure 1 15 Important You cannot choose this option for a table that has already been set up Instead select a table and choose individual options from the Tools menu for the function you want to perform For example choose Properties to modify the table mapping structure If the table type is code table you can use the WHERE clause to define a filter Figure 1 14 AQAD 30 User Guide QAD Business Intelligence Fig 1 14 Table Properties Table Properties Mapping Filter You can configure replication for all of the records in the table or for a subset of the records in the table by applying a filter Filters can also be used to split a table into multiple table like entities known as subsets each of which can be a replication target See Create Subsets on page 44 for details Fig 1 15 Map Table Map Table ERU i 9 d om Source Table Name The system displays the source table name FAQAD Using QAD BI OpenEdge 10 Edition 31 Table Type Read only field that displays the table type active historic master or code The system categorizes the tables based on this value DWH Table Name Enter the name of a new data warehouse table that will be created to hold data from the source table Source Table Filter Enter Progress code to filter records that are
66. Transformer Extract Transform Load ETL tool Data warehouse BI Monitor Impromptu Query Definition iqd files The PowerPlay application from Cognos QAD provided business models Using QAD BI OpenEdge 10 Edition 7 QAD provided dashboards Figure 1 1 illustrates the overall structure of QAD BI Fig 1 1 CO Cognos QAD BI Overview Scheduler OLAP Cubes ia amp QAD ERP Batch Execute fo Progress IQD Files QAD BI Run SQL Data 4 Warehouse Transformer Progress L Other QAD BI Instances Data Warehouse Progress Configuration Progress XML Configuration Scripts The following sections summarize the role each element plays in QAD BI See Installation Guide QAD Business Intelligence for details on installing and configuring the individual components QAD ERP Databases QAD ERP databases are the primary source of the data that is made available within the data warehouse by the ETL process for analysis in Cognos PowerPlay In a multi database environment the data warehouse can be configured to populate the same table from more than one source database QAD BI Data Transformer Data Transformer is an ETL tool for building and maintaining a data warehouse specifically designed to support analysis You can use Data Transformer to configure tasks to structure the data warehouse or load and then modify predefined business models from QAD
67. a 93108 Phone 805 684 6614 Fax 805 684 1890 http www qad com About This Guide 20000 cece eee eee nnn 1 QVEINVICW 6243s eade fos ened eae cise vba paced ed RA A voa pda ur tes 2 Other Related Documentation 00 c ccc eee eens 2 OAD Web Site ices wi cose fore eh aid ere te deco used et an an sd wth ws a ko 3 Conventions i ror o UR dak dub hae awed e CUR ene ded 4 Chapter 1 Using QAD BI OpenEdge 10 Edition 5 Overview adi 5 chasse baee ea e e gad Sheed anc anb pa 6 QAD ERP Databases lslseeee e 7 QAD BI Data Transformer ssseeeee nes 7 Data Warehouse 0 cee nee ene ens 8 BI MOnttor eese ote ode Meda dead Oca ee bla 8 Impromptu Query Definition iqd files llle 8 Cognos PowerPlay 00 cee eee ee 9 QAD Provided Business Models 0c eee nee 9 QAD Provided Dashboards 00 c ees 10 Data Transformer User Interface 0 00 cee eens 11 SIartig Lloret te ERU E be ECLIPSE 11 Nayigating ss sa nian ein atone d ktew sds dicen eta doceo Du tu de 12 Setting Up the QAD BI Data Transformer 0 0 0 e eee ee 17 Define Database Connections 0 000 ccc cence een eee 18 Manage Domains 0 0 c eee ee eh 26 Select Tables 245 ges e RR RR race Rae Roe tr 27 Set Up Replication e 29 Rename Table hoec RI eG hee Eder dE s do 42 Create Subsets dede ecce dC ea de ee a D e aa CR 44 Add Indexes
68. a corruption in the data warehouse QAD BI Data Transformer lets you rebuild links by synchronizing the REC ID values between the source and target databases based on business keys so that you can continue to perform replication without data loss or corruption See Maintain Business Keys on page 47 for details on business keys Using QAD BI OpenEdge 10 Edition 65 Note The rebuild links function does not update custom tables You need to write Progress scripts in the updateCustomTables p file under the DWHWorkDirNappl directory to customize custom tables DWHWorkDir is the data warehouse work directory that you set during installation or in Application Settings See Modifying Application Settings on page 77 for details To rebuild links click the Rebuild Link icon on the Data Transformer main screen Click Start to start on the status screen The system runs the rebuilding task listing status messages to indicate its progress Rebuild Status Window loj xi Account Totals table 200 records rebuilt at 19 40 17 Account_Totals_ table 150 records rebuilt at 19 40 17 Account Totals table 100 records rebuilt at 19 40 17 Account Totals table 50 records rebuilt at 19 40 17 Account_Totals_ rebuilding began at 19 40 16 Accounts Receivable Details rebuilding began at 19 40 16 Budget Details rebuilding began at 19 40 16 The buttons at the bottom of the screen control these functions Click Start to begin update Click St
69. age indicates the nature of the error Important To modify field properties for tables that have already completed replication setup you cannot access the Field Mapping function through the Replication Setup screen You must first select the table on the main screen and then choose ToolslField Mapping Select the field you want to edit and click the Field Properties command Define Replication Method To specify how the system replicates data between the data source and the data warehouse select a table on the main screen and choose Toolsl Properties Then click the Replication tab Important The Properties tool is available only for tables that have completed the replication Using QAD BI Progress 9 Edition 133 Fig 2 19 x Table Propers Replication Tab Replication Method C Incremental Load Full Refresh C Custom Replication Script DEFINE VARIABLE delCounter AS INTEGER NO UNDO delCounter 0 DO TRANSACTION FOR EACH amp TRG DB Customers_ WHERE amp TRG DB Customers DB NAME theDbName EXCLUSIVE LOCK appl repl stl i omen Note If you loaded the table warehouse structure from QAD provided metadata you do not have to use this procedure unless you want to modify the default setup Choose one of three replication methods Incremental Load Only records that have never been replicated to the data warehouse are selected for replication This method is typically us
70. ail al mstr Allocation Account Master ald det Allocation Account Detail alm mstr Automatic Lot Master m Anahicie Cada Mackor oft E Available Code Tables Code Tables cah_hist Service Support Call History chm_mstr Service Support Call History Master ck_mstr Accounts Payable Check Master ckd_det Accounts Payable Check Detail gltr hist General Ledger Transaction History ac type ac type ad county ad county abd conv abd conv abl eq edc abl eq edc abl eq os abl eq os abl eq wq abl eq wq m Ed ahl on unn ca severity ca severity cm class cm class sl eo un em ranion em ranion The selection screen consists of six frames The two on the left side represent source tables from the source database The system separates standard table listings from fields controlled through generalized codes when connecting to a QADDB database The four frames on the right side represent destinations in the QAD BI data warehouse These are shown in three groups Master Files Active Transactions and Historic Transactions as well as a fourth division for fields using generalized codes Move tables from the left to the right side using drag and drop You can move tables from either frame on the left into any frame on the right If this is the first time you have selected tables from the database unless you added tables by loading QAD provided metadata the frames on the right will be blank Othe
71. al Name D qadbiz32 dwh dwh db Browse Logical Name pwnot v Active Data Source Type own 7 Version fesz 1 v Connection Details Remote Single User User Host Service Other Parameters Password Connection String Domain Selection DWH ADMIN Host localhost DWH ADMIN Service 24001 Import from DWH ADMIN NI Physical Name Specify the name of the database to connect Logical Name Specify a logical database name that will be used to uniquely identify the database and its data within the data warehouse This field can only be updated when a connection is first defined Important Do not use DWH as logical name The name is reserved by the system FAQAD 22 User Guide QAD Business Intelligence FAQAD Active Indicate whether this is an active connection Databases marked as active are automatically connected when Data Transformer is started Tasks associated with databases marked as inactive are not executed during batch non interactive replication Data Source Type Indicate if this is a QAD ERP database QADDB or a QAD BI data warehouse database DWH Version When the data source type is QADDB select the QAD version associated with the QAD ERP database See Domain Database Data Sources on page 23 Remote Indicate whether the database server is local or remote Host When Remote is Yes enter the host where the database server is running Service When Remote is Ye
72. alled Database Management Select the method to use to start and stop the data warehouse Progress Explorer or Scripts Depending on this setting you can update the fields in the appropriate frame Single User Batch Process Specify whether the data warehouse database is started in single user mode when running the batch process If selected no other connections to the data warehouse are accepted until the batch process completes This field is not available when parallel replication is selected on the AppServer tab that is the Use AppServer check box is selected Use non integrity parameter Specify whether the data warehouse is started in non integrity mode When this option is selected Progress runs without using the database integrity or database recovery features Important Selecting this option can improve system performance However if Progress fails for any reason you cannot recover the database Use this feature with caution and make sure you have an adequate database backup strategy Log Language Select the language in which the log information is written Log Level Select the level of log information to be captured Error Displays functional errors Warn Displays activities that may cause errors as well as errors Info Displays trace of user activities as well as errors and warnings Debug Displays debug information for developers as well as errors and warnings Start If Database Management is Scripts enter
73. analysis FAQAD 110 User Guide QAD Business Intelligence AQAD Microsoft Access Database Access is used as a query layer that makes data in the Progress warehouse available to Cognos PowerPlay The Access database itself does not store any data Instead it serves only to maintain relationships for the data warehouse and to provide an interface for the Cognos PowerPlay Transformer In the QAD BI architecture Access provides the connection to the data warehouse using ODBC and is referred to as the data warehouse proxy An Update Links command available in QAD BI Data Transformer is used to update the table links when changes take place in the data warehouse See the Microsoft user documentation for details on using Microsoft Access Cognos PowerPlay An important part of the QAD BI solution is supplied by Cognos PowerPlay an online analytical processing OLAP software application that lets users perform multidimensional analysis create reports and share them to make better decisions PowerPlay draws information from the data warehouse using the proxy to model and build PowerCubes or simply cubes the Cognos term for optimized multidimensional data sets that enable users to perform analysis with quick response times See the Cognos documentation for information on configuring and using PowerPlay as well as on how to configure PowerCubes QAD Provided Models QAD sells the following seven models to provide out o
74. appings between source tables and data warehouse tables Validation takes place in two phases The system first checks for valid tables then it checks each field mapping between source table and data warehouse table When checking for valid tables the system determines whether a table has changed is unchanged or is missing This information displays in the Source Table Status and DWH Table Status columns of the Table Mapping frame shown in Figure 2 30 When checking field mappings the system determines the field mapping status and displays this information in the Field Mapping frame If any field mapping status is Mismatching or Invalid the status Corrupted displays in the Status column of the Table Mapping frame You should fix the field mapping problems before attempting to recreate a table Using this status information you can detect and recover any corrupted mapping that may have occurred after activities such as database replication FAQAD 146 User Guide QAD Business Intelligence Fig 2 30 DWH Table Mapping Validation AQAD DWH Table Mapping Report 15 xl Table Mapping CORRUPTED qaddb C projectsqadbi buildtrunk Nd cm mstr Customers CHANGED UNCHANGED Delete Table Mapping Recreate DWH Table DWH Expression theDbName ook REC D RECID amp SRC DE cm mstr O MISMAT format mismatching x 8 to x 12 cm addr Customer CAPS C MISMAT DWHfield Biko Address is missing cm bil Bilt
75. are the primary source of the data that is made available within the data warehouse by the ETL process for analysis in Cognos PowerPlay In a multi database environment the data warehouse can be configured to populate the same table from more than one source database Note While you can have multiple data sources language combinations are restricted to compatible code pages See Installation Guide QAD Business Intelligence for supported MFG PRO versions See the user guides for your version of MFG PRO for information on its features QAD BI Data Transformer Data Transformer is an ETL tool for building and maintaining a data warehouse specifically designed to support analysis You can use the Data Transformer to configure tasks to structure the data warehouse or load and then modify predefined business models from QAD Data Transformer generates a set of Progress scripts to synchronize the data warehouse with the source databases Progress developers can customize the automatically generated scripts or create new scripts to extend the ETL process This guide includes detailed information on using the features of Data Transformer to configure your QAD BI environment Data Warehouse The data warehouse is a Progress database that is populated with data from MFG PRO and or other data sources during the ETL process The data warehouse is an environment separate from the source databases where data can be specially structured to support
76. ase Any number of domains can be set up in one physical database each domain with its own base currency chart of accounts and operating controls You add the domain database just as any other database indicating the version as eB2 1 The Data Transformer retrieves a list of all domains defined in the database each domain is displayed as a subnode of the AQAD 122 User Guide QAD Business Intelligence Fig 2 7 Domain Database in the Tree AQAD domain database From that point forward a domain is treated exactly as any other database in the system and represented with the Database Connected and Database Disconnected icons Important Only the domain database itself can be connected or disconnected Individual domains cannot be connected or disconnected because they are physically realized in a single database All domains are connected based on their domain database s status Text is appended to the logical names of the domain database and each domain in the tree to indicate the type of source system domain database or domain blade which represents the individual domains within the database Additionally the Database Properties screen for an individual domain blade allows only the Active field to be modified All other fields are read only and based on the information entered for the domain database et Data Transformer File Tools Settings i 5 S ee 5 WON J Local Connections E amp 3 ExchangeRat
77. ase server is local or remote Host When Remote is Yes enter the host where the database server is running Service When Remote is Yes specify the service name or TCP IP Port on which the remote database server is listening for database connections Single user When Remote is No indicate if this is a single user or a multi user connection Other Parameters Specify any additional Progress connection parameters to apply to this connection Connection String The system displays the connection string based on the settings entered on the screen after the connection configuration has been accepted QAD BI Data Warehouse Database Data Sources QAD BI Data Transformer can connect to other QAD BI 2 5 and up data warehouse databases as its data source Multiple data warehouse databases can be chained together where data residing in lower level databases are aggregated to the high level data warehouse for analysis Using QAD BI OpenEdge 10 Edition 21 Important If you want to connect to DWH databases of previous versions of QAD BI you must upgrade prior releases of QAD BI to QAD BI 2 5 first For information on upgrading QAD BI see Installation Guide QAD Business Intelligence To connect to QAD BI data warehouse databases select DWH as data source type in the Database Properties window Database Properties Eg Database Properties Fig 1 6 Database Properties DWH rDatabase Physic
78. atabase drop down list Records are displayed in a paginated view with a maximum of 50 records on each page You can use the navigation controls at the bottom of these screens to easily page through records or jump to a page by its number i i i is domain i is mod date is nettable Jis overissue is status i i yes QP no 06 02 32 yes yes no 0 QP no 10 22 02 no no consign no 0 QP no 07 19 01 no no DANGER no 0 QP no 01 09 01 yes yes DOCK no P no 04 21 04 yes no dummy yes 0 QP no 01 24 02 no yes EMT yes 0 QP no 12 15 92 yes yes mb no 0 QP no 04 27 32 no yes MAB 001 no 0 QP no 03 05 33 no no NNN no fi QP no 03 05 99 pes no N Y N no 0 QP no 01 09 01 no no QC HOLD no 0 QP no 11 28 00 yes no Reclnsp no QP no 05 27 82 no no REJECT yes P no 10 22 02 yes yes reserved yes 0 QP no 10 22 02 yes no sconsign no 0 QP no 06 03 36 no no SCRAP no 0 QP no 06 02 32 no no TOXIC yes QP no 03 05 93 yes no YYN yes P no 03 05 38 yes yes YYY M 4 gt Goto o Page EE ojo EIE ee E Delete Target Data You can delete data records after they have been replicated to the data warehouse You can delete all data records from a table while preserving its table mapping information Using QAD BI OpenEdge 10 Edition 51 Choose Delete Target Data from the Data TransformerlTool
79. atically Refresh Linked Tables is selected in Data Transformerl SettingslApplications Settings the validation and linking of scripts and tables is automatically performed See Modifying Application Settings on page 164 for details When the validation is successful and the linked table refreshed the new table name is displayed on the main directory tree and a green triangle placed on the table icon If there is an error a yellow exclamation point appears on the table icon After successfully renaming a table the field mapping screen is displayed W Field dwh admin fld map loj xl E 9 DWH Name SourceName Label Description Data Type Format Ext DP ai code_q code qadcoi character x 8 OF code desc code desc Description character x 24 OF code fld code fldname Field Name The field For w character x 32 OF code_userl code useri Ufld1 User Field 1 character x 8 OF code user2 code_user2 Ufld2 User field 2 character x 8 a Customer code_value Value The allowable character x 8 a Customer code cmmt Comments The user s com character x 40 E DB NAME theDbName character x 32 rapes REC ID RECID amp SRC recid gt gt gt gt gt gt 9 From the field mapping screen select the fields in the source table that you want to replicate to the data warehouse table Then click OK The system validates each field record for syntax and when successful places a green triangle on the table icon on the main s
80. ay not stop immediately when you click the button depending on the nature of the script being executed Click Close to exit the screen Rename Table When you perform Replication Setup tables are created in the data warehouse If necessary you can change a table name by using the Rename Table function on the Data TransformerlTools menu This function opens the Rename Table screen Using QAD BI OpenEdge 10 Edition 43 Fig 1 22 iBi xi Rename Table Old Table Name TETTE MESS New Table Name OK Cancel If the old table name cannot be found in the data warehouse an error message is displayed An error message also displays if a user enters A blank table name Reserved words A table name that is already in use When you click OK and Automatically Check ETL Scripts and Automatically Refresh Linked Tables is selected in Data Transformerl SettingslApplications Settings the validation and linking of scripts and tables is automatically performed See Modifying Application Settings on page 77 for details When the validation 1s successful and the linked table refreshed the new table name is displayed on the main directory tree and a green triangle placed on the table icon If there is an error a yellow exclamation point appears on the table icon After successfully renaming a table the field mapping screen is displayed AQAD 44 User Guide QAD Business Intelligence Fig 1 23 Field Mapping
81. bmfg Domain Blade localhost FPA Active Trancartinne Fig 1 7 Domain Database in the Tree FAQAD 24 User Guide QAD Business Intelligence FAQAD Non Progress Data Sources You can connect to non Progress relational databases using the Progress DataServer technology See the OpenEdge Data Management DataServer for Oracle Guide the Data Management DataServer for Microsoft SQL Server Guide or the Data Management DataServer for ODBC Guide to learn about setting up and configuring DataServers in Progress Once the DataServer has been set up you configure the connection to the schema holder database in Data Transformer as described previously leaving the QADDB check box cleared Data Transformer lists all of the schema images associated with the schema holder as child connections after the schema holder has been connected You can right click each of the child connections and choose Properties to configure connection information specific to the targeted schema image For example if the schema image references an Oracle database you will need to provide the following information in the Other Parameters field on the Connection Properties screen to use Oracle SQL Net networking U ORACLE USERNAME ORACLE SID P ORACLE PASSWORD Database Properties xi Database Properties Using QAD BI OpenEdge 10 Edition Fig 1 8 rDatabase Physical Name ExchRates Logi
82. cal Name CorpexchRates Browse v Active Data Source Type oannes x Version amp B2 1 im l Connection Details Remote IV Single User Host Service Other Parameters fu doe o92 P password Connection String Domain Selection ma When a schema holder is first added Data Transformer retrieves all defined schema images and creates additional connection records for each one You can limit the selection of schema images to include using either the standard Delete function or the Select Schema Images screen Schema Image Database Properties 25 Fie Tools Settings Ys c8 5 amp 2 ee a Cg E EB coliao Corp Domain Database E QAD Domain Blade st92bmfg Domain Blade Lt x Connect gaddb Database E dor 8 eve eve eve nei Properties Select Schema Images A E RH 8 8 MONS E cta2hmfn 1 Mamain Alade Fig 1 9 Select Schema Images Menu Item FAQAD 26 User Guide QAD Business Intelligence Fig 1 10 Select Schema Images Fig 1 11 Select Domains Menu Item AQAD Use the Select Schema Images screen to choose which schema images you want to work with and remove those you do not need to replicate Move schema images from the Available Schema Images list to the Selected Schema Images list to have them show up in the
83. ce table DWH Field The name of the field in the data warehouse table DWH Expression The expression used to calculate the value that populates the data warehouse field Function Currently not used When you click Delete Field Mapping after selecting the relevant check boxes the selected field mapping information stored in data warehouse admin is deleted but the field data in the data warehouse tables is retained When you click Close after performing Delete Field Mapping the system performs the Recreate DWH Table function Create Custom Tables Not all tables in the data warehouse have to be based on table definitions from the connected data sources You can create custom tables that exist only in the data warehouse Custom tables are populated with data using custom scripts They can contain virtually any data the designer wants to put in them To create a new table right click the Custom Tables folder on the main screen and select Create Table The Map Table screen displays Using QAD BI Progress 9 Edition 149 x Table Table Mapping SourceTable Name Table Type Custom Table DWH Table Name Source Table Filter While the overall process is similar to mapping an MFG PRO table to the data warehouse you can only access one field DWH Table Name Table Type is automatically set to Custom Table and you cannot define code to filter records because a custom table is not directly based on any s
84. ck box associated with the replication tasks you want to reuse When you choose SettingslTask List the system displays a separate ETL script for each database Using QAD BI Progress 9 Edition 153 Use the Task List The Task List provides a consolidated view of all scheduled tasks whether activated or not on a single screen It is an alternative to selecting each element in the application tree and scheduling it using the Properties function Use it to select and deselect elements scheduled for automated replication To view the task list click the Task List command on the toolbar You can select or deselect tasks individually To select or deselect the entire list at once use the Select All and Select None commands Fig 2 35 inxs Task List n 3 Service Inventory Transactions Daily Active Subset Escalations Daily eB2 Active Master Files Base Currency Daily eB2 Active Custom Exchange Rate Daily eB2 Active Custom Currency Fields Incremental Daily eB2 Active Custom Format Positions Daily eB2 Active Custom Invoice Trailers Daily eB2 Active Custom Call Lead Time Daily eB2 Active Custom Engineer Hours Available Daily eB2 Active Custom Stock History Month Daily eB2 Active Custom Sales Statistics Daily eB2 Active Custom Sales Order Trailers Daily eB2 Active Custom Sales Order Discounts Daily eB2 Active Custom Repeat Calls Daily eB2 Active Custom Purchase Statistics Daily eB2 Active Custom Forecast Daily eB2 Active Cu
85. creen to indicate that replication setup is completed for that table If validation fails a message is displayed to indicate the nature of the error and the table icon is set to a yellow exclamation point Using QAD BI Progress 9 Edition 139 Create Subsets Within the data source data from different functional areas is sometimes stored in the same table For example the MFG PRO transaction history table tr_hist stores data related to sales orders but it also contains many other types of business documents as well You can define subsets to isolate the data of interest in a data warehouse table In QAD BI Data Transformer defining a subset is similar to setting up a table for replication The main difference is that the subset definition includes a filter specification to select the area of interest in the source table Instead of ToolslReplication Setup choose ToolslSubset Fig 2 24 Map Tabie 0x Subset Definition Table r Table Mapping SourceTable Name ac mstr Table Type Subset DAH Table Name Source Table Table Type defaults to Subset you cannot change it Specify the data warehouse table name Then enter Progress code to filter the records that should be replicated in the data warehouse Syntax for the filter always has the same format WHERE amp SRC DB tablename fieldname operator value AND OR amp SRC DB tablename fieldname operator value Opera
86. criptions Icon Description Icon Description Server Table with script error Fr Schema holder Subset Connected domain database Validated code table Fr Disconnected domain Non validated code table database En Connected database or domain E Code table with script error Disconnected database or domain ci Validated custom script Logical grouping cu Non validated custom script Source table prior to replication setup Hi je scu om i uU Custom script with script error Validated table Cube E gi Non validated table Custom table E ES Lr Merged table Table 2 6 Source System Icons AQAD 118 User Guide QAD Business Intelligence FAQAD Setting Up QAD BI Data Transformer This section describes the steps necessary to connect to an MFG PRO database that provides source data to the QAD BI analysis tools as well as define the tables and fields that are included in the analysis models Additionally you control how and when data is replicated between this database and the data warehouse which serves as the actual source of data used in analysis You can use one of the following methods to set up the Data Transformer Perform a manual setup by defining tables and fields based on the MFG PRO database schema If you purchased one or more of the QAD developed business models load table definitions and scripts then use the Data Tra
87. d Time2 failed validation Fig 1 50 zi B localhost Validation Icons 5 9 mm E Active Transactions H D Code Tables E D Historic Transactions O Master Files E Custom Scripts T Script Balance Sheet Totals E Script Call Billing Detail f Script Call Lead Time2 ier Note You can check the script for an individual table by right clicking the table on the main screen then selecting Check Scripts Complete Model Setup After loading model data and checking scripts complete setup tasks by Updating the table links for all replication and custom tables see page 39 Scheduling automatic replication for each table custom script and cube as shown in the relevant sections above Optionally fine tuning the data warehouse structure and replication setup Use the following sections for reference Manage Domains on page 26 e Select Tables on page 27 e Set Up Replication on page 29 Modifying Application Settings Based on information entered during the installation most settings required to connect the components of QAD BI are in place when you begin using the application Note An important exception to this is the currency setting which defaults from the QAD ERP base currency unless You are connecting to a domain database and the base currencies in the domains are different AQAD 78 User Guide QAD Business Intelligence Fig 1 51 Application Settings Data War
88. d custom data Set up replication by adding ERP database tables and fields to data warehouse Create subsets i Schedule replication Add or adjust indexes f Modify application settings 1551 Optional andperform administrative iis tasks i Define Database Connections Note Many required connection settings are specified during QAD BI installation See Modifying Application Settings on page 77 if you need to modify any of this information To add a new source database connection select FilelAdd Database or click the Add Database command To edit an existing source database connection right click the database on the main Data Transformer screen and select Properties The data warehouse and administration databases were deployed using the utf 8 code page by default Data Transformer can connect to multiple source databases using different code pages and data is automatically converted to utf 8 when replicated to the data warehouse FAQAD Using QAD BI OpenEdge 10 Edition 19 If you update existing properties you must disconnect and reconnect to the database to use the new settings QAD ERP Database Data Sources Before you begin mapping fields from the source QAD ERP database to the data warehouse you must define the connection parameters for the QAD ERP database Data Transformer reads the source schema which are used to define the tables and fields that make up the data warehou
89. e designate it as an incomplete business key index to have the system validate uniqueness and maximize accuracy when rebuilding data links Note When processing incomplete business key indexes the system maximizes accuracy by performing additional operations during data link update which may require longer execution time To designate an index as a business key index in a table select the table on the main screen and choose ToolslIndexes The Indexes screen displays any existing indexes Using QAD BI Progress 9 Edition 143 Ce 6 6 l x E Invoice_ Ei Effective_Date_ Ey Curr Valued E DB NAME Unique Ea DB NAME Eg REC ID fl Open Gf Type_ Fa DB NAME Fa Type_ BK Accounts Receiv Complete BK Ef Curr valued Primary Use the commands at the top left to designate an index as a complete business key index or incomplete business key index To designate an index as a complete business key index select the index and click the Set to BK icon Use the Complete BK and Incomplete BK icons to switch the business key index between complete and incomplete To remove the business key index select the index and click the Cancel BK icon Note Custom tables do not support business key indexes Business key index command icons are always grayed out in the Indexes window of a custom table Preview Data You can view data both after it has been r
90. e Cognos cube security and Transformer see the Cognos Transformer online help and user documentation Replication Use the settings on the Replication tab to determine the replication strategy Application Settings xi Data Warehouse ODBC Connection Cognos Transformer Replication Batch Process AppServer Notification Host Made Connection Mode Local Host DWH Shared Memory Mode C Remote Host DWH C Client Server Mode User Mode Replication Mode ____ C Single User Serial Replication Multiple Users Parallel Replication cm Host Mode Choose how QAD BI is deployed Local Host DWH Choose this if the data warehouse database is located on the same server as the QAD BI Data Transformer Remote Host DWH Choose this if the data warehouse database and QAD BI Data Transformer are installed on different servers Connection Mode Choose which method QAD BI Data Transformer uses to exchange data Using QAD BI OpenEdge 10 Edition 83 Shared Memory Mode The application exchanges data through shared memory Choose this if QAD BI is deployed in local host mode since it is a faster way of exchanging data Using a designated area of shared memory the data can be made directly accessible to both client and server processes without having to use the system services Client Server Mode The application transfers data through TCP IP Note If QAD Bl is deployed in rem
91. e Meta Filter Window displays Use the check box to Indicate that you want to import the object information for each object Object Name Displays the name of an object Meta Filter Window inl x m Account_Totals_ O amp ccounts Payable O Accounts_Receivable_ Accounts Receivable Details O Budget Details Call Detail Calls O Current Costprices Inventory Inventory Details I Price Lists Purchase rder Lines Purchase rders L153ales Order Details Sales rders Service Contract Details Service Contracts Standard Costprices SRC TABLE SRC TABLE SRC TABLE SRC TABLE SRC TABLE SRC TABLE SRC TABLE SRC TABLE SRC TABLE SRC TABLE SRC TABLE SRC TABLE SRC TABLE SRC TABLE SRC TABLE SRC TABLE SRC TABLE CRPTARIF u sf b c The Meta Filter Window presents a list of objects and the following Object Type Displays the type of the database object SRC TABLE DWH TABLE MERGE TABLE SCRIPT or CUBE New Object Indicates whether the object already exits in the database or domain Yes This is a new object that has not been imported before No The object has already been imported Use the check box to the left of the object name to indicate whether to import the object The check box is selected by default when the object is new If the object is not new you can select the check box to reimport the object Using QAD BI Progress 9 Edition 163 Review the list to ve
92. e Reference Guide QAD Business Intelligence Key Performance Indicators This is provided on the installation CD for each model you purchased A model is realized by the following components AQAD 9 10 User Guide QAD Business Intelligence FAQAD Replication tasks to extract transform and load selected data from QAD ERP into the data warehouse Relationship definitions for the model tables in the data warehouse defined in IQD files Cognos OLAP model defining the multidimensional representation of the tables in the data warehouse Building the OLAP model creates an OLAP cube that can be analyzed using the Cognos reporting tools See Using QAD Provided Model Data on page 73 for information on how to implement models QAD Provided Dashboards A dashboard is a collection of navigable reports accessible from a dashboard home page These reports graphically represent business critical KPIs in the form of charts Dashboards add an interactive element to reports They let you Drill up and down to see higher and lower levels of detail Include multiple charts derived from different data sources in a single report Note You can only view dashboards in QAD 2007 1 or later using the applicable version of QAD NET UI QAD sells the following three dashboards to provide out of the box navigable reports for standard QAD ERP installations Sales Management a collection of reports on sales revenue gross margin
93. e eee ee nee 164 Modifying Application Settings 0 0 0 eee eee ee eee 164 Data Warehouse eich okt eere Pa e E ee 165 Table Linking o cia ccce caa cita Ree ber he er eR d 166 Cognos Transformer 0 0c eee eee dan aiaia 168 Batch PFOCeSS i sedo wend Ria densi dicen bs P cr E ace 169 Notification i 2i sscedclu eel BI IRE E a E ur E EET E 171 ADpSerVet saren enk Vinge eniei oie be ER e Pt et oe 172 Performing Administration Tasks 0 0 0 cee cece eee eee 173 Update User Records 0 0 0 cece eee eee nee 173 Set Up Windows Task Scheduler 0 00 0 02 ee eee 175 Chapter3 Reference 0200c cece eee eee eee eee 177 QAD BI Parallel Replication Setup 0 0 0 eee eee eee 178 OVERVIEW ina a Since dette ease ba Sach taeda E se 178 Configure the AppServer 0 0 0 0 cece eee ee eee nee 178 Configure Data Transformer 0 0 0 0 eee ee eee eee 183 Multicurrency Configuration 1 0 0 0 0 0 eee eee eee 184 OVELVIEW situs ae bbe ee dy bate ad ou wash dentin waa ntu b 184 Configure the Data Warehouse Currency 000 5 185 Configure Multicurrency Custom Scripts 005 185 Modifying Currency 0 0 0 cece cette ene 187 Setting the Replication Task Sequence 0 0 0 0 ee eee eee eee 189 OVERVIEW Season ined de winds ede wad aot oul a EE tailed 189 Single Currency Sequence Dependencies 189 Multicurrency Sequence De
94. e source database Source DB The physical path to the source database Source Table The name of the table from the source database Source Table Status and DWH Table Status These status fields indicate if mappings between the source and data warehouse are new unchanged or missing Unchanged Field mappings are unchanged Changed Field mappings have changed Either source table fields or data warehouse fields have changed Using QAD BI OpenEdge 10 Edition 53 Missing Either the source table or the data warehouse table is missing Use the two buttons in the Table Mapping frame to delete tables or recreate them Deleting Table Mapping Clicking the Delete Table Mapping button lets you delete both table and field mapping information The data warehouse table is also deleted Recreating Data Warehouse Table You can use the Recreate DWH Table button to resolve any mismatching between source and data warehouse tables You can also use it to delete data from the data warehouse table When you click the Recreate DWH Table button the system 1 Deletes all mapped data from the data warehouse table and deletes the data warehouse table 2 Creates a data warehouse table based on the original table mapping stored in data warehouse admin Before recreating a data warehouse table ensure that you have deleted any field mappings with the status of Invalid shown in the Field Mapping frame Field Mapping Display and Functions
95. ect Active and enter a sequence number to enable cube generation within the automated replication Fig 2 40 Cube Properties Schedule Tab AQAD 160 User Guide QAD Business Intelligence Fig 2 41 Cube Properties Languages Tab AQAD Languages The Languages tab allows for the selection of the language or languages in which multilingual cubes should be created Note Single language cubes ignore these settings Cubes are created in subfolders of the Cube directory as specified in the Application Settings Cognos Transformer tab Subfolders are named using the two character language identifier for the selected languages If no language is selected the cube is built directly within the Cube directory Cube Properties xj Cube Details Schedul 3l Deutsch English Espafiol Latino Francais Nederlands em For information on creating or modifying language tokens for translated Cognos Transformer model files see Creating Translatable Cognos Transformer Models on page 191 Using QAD Provided Model Data If your QAD BI configuration includes one or more of the seven predefined models purchased from QAD load the schema using the Data Transformer s Load Meta Data function instead of building the data warehouse tables manually from MFG PRO schema You can then use the table and field tools to customize the schema for the predefined models as needed Note In MFG PRO eB2 1 you must load metadata and
96. ed for tables that contain historical records such as tr hist and ih hist or a combination of historical and active transaction records such as wod det or pod det Incremental load generally is not as time consuming as a full refresh Important The replication process for incremental load does not determine whether a record has been modified since the last replication only that it has been created Be aware that using this method for records that are commonly updated master records for example can create data synchronization problems between MFG PRO and the data warehouse Full Refresh The system clears all existing fields in the data warehouse for the data source and completely replaces the contents This is typically used to replicate master data often including tables that do not contain more than 10 000 records It is the default for newly defined tables FAQAD 134 User Guide QAD Business Intelligence FAQAD Custom Whether a table is replicated depends on some user defined condition for example replication only takes place when an item is associated with a product line The system displays a default Progress replication script based on the selected method If you modify the code Replication Mode is automatically set to Custom When you click OK the system validates the syntax If there are errors the table icon on the main screen displays a yellow warning indicator to indicate that a problem exists Repl
97. ed to run the QAD BI process at least as frequently as the most frequent replication task For example if the Windows Task Scheduler is set to run every week even if a table replication task is scheduled to run every day it will only be run every week AQAD 42 User Guide QAD Business Intelligence Fig 1 21 Record Processing Status AQAD Running Manual Replication In addition to setting up scheduled replication processes you can refresh a data warehouse table with updated values from the source database at any time Select the table on the main screen and choose ToolslExecute Now Click Start on the Record Processing Status screen The system runs the replication task listing status messages to indicate its progress Note The moving status bar at the top of the screen will not necessarily fill completely to the right when the replication task completes Since each new status message is added to the top of the list you can determine that replication is finished when the Total Update Time message displays Record processing status E Processing records 300 to 400 Total update time 00 00 00 Total processed records 301 Processed 300 Records 08 52 27 Processed 200 Records 08 52 27 Processed 100 Records 08 52 27 Total deleted records 0 Starting at 08 52 27 The buttons at the bottom of the screen control this function e Click Start to begin replication e Click Stop to end replication Note Replication m
98. eduler You can use Data Transformer to define the replication schedule for each individual task See Scheduling Automatic Replication on page 39 for information on defining task schedules in Data Transformer However you also must set up an operating system level scheduling task to look at the individual task schedules and run the processes accordingly Note This procedure is based on Windows XP Professional It may vary for other versions of Windows 1 Torun the Windows Task Scheduler Wizard select StartlControl PanellScheduled TaskslAdd Scheduled Tasks 2 Click Next to continue 3 Click Browse and select the Run Tasks shortcut located in the directory where program files were installed 4 Choose the task schedule Daily Weekly Monthly One time only When my computer starts WhenIlog on Depending on your choice you may be prompted for further scheduling information Important The schedule you define here describes when Windows launches its own scheduling task which in turn looks at Data Transformer scheduling tasks You should be sure to define the Windows task so that it is appropriate to the schedule granularity defined for Data Transformer For example it would not make sense to define a monthly Windows task if you want to replicate tables every day 5 Enter the name of a Windows account with sufficient access privileges to execute Data Transformer on the target machine 6 Select the date and time for
99. ee eee 112 NaVigatitg 2 3 sagst ee pte go d tuse pee Me Oe ee 113 Setting Up QAD BI Data Transformer ssesleeeeeee eee 118 Define Database Connections 0 00 cee cee eee eens 119 Manage Domains 0 0 c cece eee eee eee 124 Select Tables 1 sed eda Re Ae Re eub Rd a RS 126 Set Up Replication secere segrai adna a E eee eens 128 Rename Table 12e Lb ur LR re dor oS RD ee era 137 Create Subsets i uos tont EEr do eer doa ever deed diese Red 139 Add Indexes ie 4 oe ces dette er Quo S Rt ote e 140 Add Business Key Indexes leleleeeeeeeeeess 142 Preview Dala sessed cpi sce e OR et ee ee ek See a ee os 143 Delete Target Data oso ceo E gana dees 144 Validate Tables 2 0 0 0 ccc ce ene eh 145 Create Custom Tables 0 0 0 0 0 ccc eee ankas 148 Create Custom ETL Scripts 0 0 eee eee eee 149 Merge Data from Multiple Databases 004 152 Use the Task List ese cett ek a es 153 Update Links eminere ete ees i Eee Er decens 154 V vi User Guide QAD Business Intelligence Export Tables keep Ere TERR PU ERE eels 155 Rebuild Links leeeeeeeeeeeee e 155 Working with the Model 0 0 0 cee 157 Using QAD Provided Model Data 0 0 2c eee eee ee 160 Load Metadata o ocs ccoo cereti serred etia dekie ie a e a 161 Bulk Generate and Validate Scripts 2 000005 163 Complete Model Setup 0 0 cece ee
100. een illustrated in Figure 2 26 displays Fig 2 26 x Add Index Name sales orders us Available Fields Index Fields File Description Ey Credit Order Determines if the Sales Order Ti By Credit_Terms_ A code indicating the credit terr Ey Curr Valued By Currency_TX_ The currency in which to pay fo Fa DB NAME E Discount Pct The discount percentage to be Ej Due Date The date the order is to be ship E Exchange R is Invoice Date Ej Invoiced A flag indicating an invoice has Ey Order_Date_ The date the order was enterec Fa Order Type Code to distinguish normal quo By Project_ Project number By Purchase O The customer purchase 8 gt Eol Bilto_Address_ The address code for t Py 02 Customer_ The address code for tt By 03 Invoice_ The invoice number tha The date that the invoice For th Enter a name for the new index The left pane displays all the fields in the data warehouse table or subset that are available for indexing Drag and drop fields to the right pane to include them in the index Important After adding or deleting an index you should validate the synchronization script using these steps 1 Select the updated table on the main screen 2 Choose ToolslGenerate ETL script 3 Choose Check Script If the new index validates properly a green triangle displays on the table icon Otherwise a yellow warning icon displays AQAD
101. efault Create Delete OME Databasefal Databases v Task Search eraty tak meses petes sets Lebe A Account Totals Daily QAD Active Active Transactions Work Order Route Daily QAD Active Active Transactions Accounts_ Daily QAD Active Master Files Accounts_Payable_ Daily QAD Active Active Transactions Accounts Receivable Daily QAD Active Active Transactions Accounts Receivable Details Daily QAD Active Active Transactions Banks Daily QAD Active Master Files Budget Details Daily QAD Active Active Transactions Suppliers Daily QAD Active Master Files Call Billing History _ Daily QP Active Historic Transactions Call_Detail_ Daily QAD Active Active Transactions Call Events Daily QP Active Historic Transactions Call History Daily QP Active Historic Transactions Call Item Detail History Daily QP Active Historic Transactions Call_Status_ Daily QAD Active Master Files Call Types Daily QAD Active Master Files Calls Active Transactions FAQAD 62 User Guide QAD Business Intelligence FAQAD Task Sets Tasks can be grouped into task sets and scheduled respectively to run in separate batches When you first use QAD BI Transformer a Default task set exists You can create new task sets to contain groups of selected tasks To create a new task set 1 Click the Create button next to the Task Set drop down list 2 Inthe New Task dialogue box enter a task set name and select an existing task set to c
102. eferred to as the AppServer name AppServer Host Enter the name of the server where the AppServer has been installed NameServer Port Specify the port to which the NameServer has been configured to listen The default is 5162 AQAD Using QAD BI Progress 9 Edition 173 AppServer Pool Size Specify the number of AppServer processes that will be available to process replication tasks Four is a good starting value for testing The value entered here must be one less than the value of the Minimum and Maximum Servers settings on the AppServer properties screen See QAD BI Parallel Replication Setup on page 178 for details Note Do not set this value too high Entering a value that is too large can reduce performance on hardware that is insufficient to handle the processing load DWH ADMIN Host Specify the name of the server where the administration database is running DWH ADMIN Service Specify the port to which the administration database is configured to listen The default is 24001 DWH ADMIN Network Specify the protocol that the administration database is configured to use for communication Default is TCP AppServer Log Directory Specify the directory to which all log files will be written AppServer Error Log Specify the name of the AppServer error log AppServer Replication Log Specify the name of the AppServer replication log For additional information on using parallel replication with the Data Transformer
103. ehouse Tab AQAD You are connecting to multiple databases and the base currencies in the databases are different In both of these cases the currency setting is blank and you must manually set the currency for the data warehouse database See Currency on page 79 If for any reason you need to update any of those settings choose SettingslApplication Settings from the main menu The system displays a screen with the following tabs Data Warehouse ODBC Connection Cognos Transformer Replication Batch Process AppServer Notification The following sections describe the functions of each tab Data Warehouse The Data Warehouse tab includes parameters applicable to the data warehouse database Application Settings x Data Warehouse opc Connection Cognos Transformer Replication Batch Process AppServer Notification DWH Details Generated Scripts Directory cadbidatalsciptsipgn Browse Custom Scripts Directory cgadbidataiscptsicustom Browse System Tasks Directory cgadbidataiscptsisystem Browse IQD Files Directory lgadbidata db SS Browse Work Directory maie Browse v Automatically Resort Fields Alphabetically v Automatically Check ETL Scripts IQD Source awh Currency z a Using QAD BI OpenEdge
104. em database can be either active or inactive as defined on the Database Properties screen When a source system is marked as inactive none of its tasks will execute under the batch replication process You can determine if a task is from an inactive database by viewing the Active Inactive column in the Task List or by observing the icon for the task Inactive tasks display a grayed out icon See page 20 for details When a database is marked as Inactive you cannot change the Activate check box To set the database s Active Inactive status use the Database Properties screen Fig 1 39 Cea o Database Status in Task Sets fee x Create Delete the Task List i Oo Database ll Databases z Task search Account_Totals_ il Inactive Active Work_Order_Route_ il Inactive Active Accounts il Inactive Maste Accounts Payable il Inactive Active Accounts _Receivable_ Inactive Active Accounts Receivable Details il Inactive Active Banks il Inactive Budget Details il Inactive Suppliers il Inactive Call Billing History i Active Call Detail il Inactive Call_Events_ il Active Call History il Active Call Item Detail History il Active Call Status il Inactive Inactive Inactive Charge_Codes_ il Inactive Check_Details_ il Active Checks_ il Active Service Contracts il Inactive FAQAD 64 User Guide QAD Business Intelligence FAQAD Export Tables You can export table information in XML format on any level of the main
105. ement 118 108 User Guide QAD Business Intelligence Overview The QAD Business Intelligence QAD BI product lets users extract and restructure data from MFG PRO and other data sources into a Progress based data warehouse presenting a business oriented view of the enterprise Analysts can use Cognos 8 to perform powerful multidimensional analysis against the data warehouse The QAD BI solution includes One or more existing data sources The Data Transformer Extract Transform Load ETL tool Data warehouse A Microsoft Access database used as a database proxy between the data warehouse and PowerPlay The PowerPlay application from Cognos Figure 2 1 illustrates the overall structure of QAD BI Fig 2 1 QAD BI Overview RAE CRUS Scheduler OLAP Cubes Batch Execute MFG PRO E ODBC Data ODBC Warehouse Analysis Warehouse Proxy Progress Access PowerPlay COM Table Links Progress QAD BI Data Transformer be Progress E 4 K K QAD BI Configuration Progress XML Configuration Scripts D The following sections summarize the role each element plays in QAD BI See Installation Guide QAD Business Intelligence for information on installing and configuring the individual components FAQAD Using QAD BI Progress 9 Edition 109 MFG PRO MFG PRO databases
106. entory_ Inventory_Details_ OPrice_Lists_ Purchase_Order_Lines_ Purchase_Orders_ O Sales_Order_Details_ Sales_Orders_ Service_Contract_Details_ Service_Contracts_ Standard_Costprices_ SRC TABLE SRC TABLE SRC TABLE SRC TABLE SRC TABLE SRC TABLE SRC TABLE SRC TABLE SRC TABLE SRC TABLE SRC TABLE SRC TABLE SRC TABLE SRC TABLE SRC TABLE SRC TABLE SRC TABLE CRPTARIF n sf b c The Meta Filter Window presents a list of objects and the following Object Type Displays the type of the database object SRC TABLE DWH TABLE MERGE TABLE SCRIPT or CUBE New Object Indicates whether the object already exits in the database or domain Yes This is a new object that has not been imported before No The object has already been imported Use the check box to the left of the object name to indicate whether to import the object The check box is selected by default when the object is new If the object is not new you can select the check box to reimport the object FAQAD 76 User Guide QAD Business Intelligence Fig 1 49 Metadata Loaded AQAD Review the list to verify that the selected objects are those you want to import Then click OK to activate the import The created data structure then displays on the main screen directory tree The display includes a green triangle beside a valid object or a yellow exclamation point beside an invalid object Note In MFG PRO eB2 1 and later releases of QA
107. eplicated to the data warehouse and in its original form in the data source First select a table on the main screen Then To view the data records in the data warehouse choose ToolslPreview Target Data To view the source database records choose ToolslPreview Source Data Fig 2 27 Business Key Indexes FAQAD 144 User Guide QAD Business Intelligence Figure 2 28 shows the contents of the address master ad mstr table in MFG PRO as well as the corresponding Address table in the data warehouse Note that the MFG PRO table shows all fields in the schema identified by the schema field names the data warehouse displays only the mapped fields To sort the records based on values in a specific column click the column heading Fig 2 28 Table Preview x Account Cost Center cur Valued___ DB NAME EA i S acd acc acd amt acd ec acd cr amt acd cr_curr_amt acd curr amt EE x 0 0 12308 3 0 1040 22259 22000 0 0 44259 0 1040 700000 0 0 700000 0 1040 233333 o 0 233333 0 1040 90000 104373 8 o 0 194373 8 0 1040 5000000 0 0 0 5000000 0 1040 150000 153198 9 0 0 303198 9 0 1040 5158900 0 0 5158900 0 1040 10000000 0 0 10000000 0 1040 25000 27217 81 0 0 52217 81 0 1040 125000 0 0 0 125000 0 1040 10000 11123 0 0 21123 0 1040 5000000 0 0 5000000 0 1040 39 94 0 0 0 39 94 0 1040 54272 5 52149 0 0 106421 5 1040 5969775 1 6000 0 0 5975775 1 0 1040 10000000 0 0 0 1000
108. erver broker This is the same value that appears in the Progress Explorer tree under the AppServer folder Also referred to as the AppServer name AppServer Host Enter the name of the server where the AppServer has been installed NameServer Port Specify the port to which the NameServer has been configured to listen The default is 5162 Pool Size of Script Specify the number of AppServer processes that will be available to execute scripts Four is a good starting value for testing The value entered here must be one less than the value of the Minimum and Maximum Servers settings on the AppServer properties screen See QAD BI Parallel Replication Setup on page 178 for details Using QAD BI OpenEdge 10 Edition 87 Pool Size of Cube Specify the number of AppServer processes that will be available to generate cubes Four is a good starting value for testing The value entered here must be one less than the value of the Minimum and Maximum Servers settings on the AppServer properties screen See QAD BI Parallel Replication Setup on page 178 for details Note Do not set this value too high Entering a value that is too large can reduce performance on hardware that is insufficient to handle the processing load DWH ADMIN Host Specify the name of the server where the administration database is running DWH ADMIN Service Specify the port to which the administration database is configured to listen The default is 24001
109. es Schema Holdei CorpExchRates ODBC Sche C3 Custom Scripts E Western Custom Scripts BB Corp Domain Database E E QAD Domain Blade Ez st92bmfg Domain Blade localhost FAFA Active Trancartinne Non Progress Data Sources You can connect to non Progress relational databases using the Progress DataServer technology See the Progress DataServer for Oracle Guide the Progress DataServer for Microsoft SQL Server Guide or the Progress DataServer for ODBC Guide to learn about setting up and configuring DataServers in Progress Once the DataServer has been set up you configure the connection to the schema holder database in the Data Transformer as described previously leaving the QADDB check box cleared The Data Transformer lists all of the schema images associated with the schema holder as child Using QAD BI Progress 9 Edition 123 connections after the schema holder has been connected You can right click each of the child connections and choose Properties to configure connection information specific to the targeted schema image For example if the schema image references an Oracle database you will need to provide the following information in the Other Parameters field on the Connection Properties screen to use Oracle SQL Net networking U ORACLE USERNAME ORACLE SID P ORACLE PASSWORD Fig 2 8 x Schema Image Database Properties Database Properties
110. es de cuentas P y L Rekeningsoort Account Type Type de compte Kontenart Nederlands du English en Francais fr Deutsch ge Espa ol Latino 1s Nederlands du English en Francais fr Deutsch ge 3 Enter the information to define your translatable token on the Add Label screen as follows Field Enter the token used in the Cognos Transformer model Example If the token entered in the Cognos Transformer model is Customer enter Customer in this field Languages Select the language you are defining for the token from the drop down list Label Enter the text that should be used to replace the token 4 Repeat Steps 2 and 3 for all languages you want to define When finished the Labels screen should contain multiple records for the token one for each language defined English en Current Week Deutsch ge Current Week Customer Espa ol Latino Is Neck s du English TS Customer Customer Frangais fr Deutsch ge Customer Customer Class Espafiol Latino Is Nederlands du Customer Class English en Customer Class Francais fr Customer Class Deutsch ge Reference 193 Fig 3 15 Add Label Fig 3 16 Multiple Languages Defined for Customers Token AQAD 194 User Guide QAD Business Intelligence Fig 3 17 Add a Customer Script AQAD Manually Loadin
111. escribed in this section 1 o a A O 10 11 12 13 14 15 Start Data Transformer and navigate to and expand the Custom Scripts folder in the tree Right click the Script_Currency_Fields_Incremental_ p custom script and select Properties Select the Schedule tab and clear the Activate check box Click OK to save the changes Right click the Custom Scripts folder and select Add Script Click Browse on the Add Script screen and navigate to the QADBIDataDir scripts custom directory select the Script Currency Fields Full p custom script and click Open Select the Schedule tab and set the fields as follows Activate selected Sequence 15 Frequency any item in the list Exit Data Transformer and execute the batch replication Start Data Transformer and navigate to and expand the Custom Scripts folder in the tree Right click the Script Currency Fields Incremental p custom script and select Properties Select the Schedule tab and select the Activate check box Click OK to save the changes Right click the Script Currency Fields Full p custom script and select Properties Select the Schedule tab and clear the Activate check box Click OK to save the changes Reference 189 QAD BI has now been configured to use the new source base currency or data warehouse currency Batch replication can continue in the normal manner Setting the Replication Task Sequence This section provides detailed informati
112. f the box analysis for standard MFG PRO installations The seven models together provide support for more than 100 key performance indicators KPIs Inventory monitoring for insight into current and historic inventory trends Using QAD BI Progress 9 Edition 111 Manufacturing performance including statistics on work orders production costs productivity and throughput Forecasting performance Sales analysis including statistical trends in sales and revenue margins Financial controls encompassing gauges for profitability investments and the effectiveness of financial management Purchasing controls from patterns in purchase price and cost to materials delivery Service performance insights including service profitability and trends in customer calls and contract selection For information on the models and KPIs see Reference Guide QAD Business Intelligence Key Performance Indicators This is provided on the installation CD for each model you purchased A model is realized by the following components Replication tasks to extract transform and load selected data from MFG PRO into the data warehouse Relationship definitions for the model tables in the data warehouse defined in the data warehouse proxy Access Cognos OLAP model defining the multidimensional representation of the tables in the data warehouse Building the OLAP model creates an OLAP cube that can be analyzed using the Cognos reporting to
113. fi Page EE o gt roperties j Name DaysOfReplicationPeriod Level Global Description Scope Global si z Value 20 Update Add Delete Apply OK Cancel FAQAD 68 User Guide QAD Business Intelligence FAQAD Name Specify the parameter name You can assign identical names to parameters at different levels and scopes but the name must be unique for parameters with the same combination of level and scope values Level Specify at which level the parameter is declared Global The parameter is declared globally and is available at all levels Domain The parameter is declared at the domain level and is only available within specific domains Database The parameter is declared at the database level and is only available within specific databases Scope Specify the scope within which the parameter is available For global parameters Global is the only available option and is automatically selected For domain level parameters select a specific domain within which the parameter is available For database level parameters select a specific database within which the parameter is available Value Assign a value to the parameter Description Optionally provide a description of the parameter To maintain parameters search and select parameters in the parameter list and perform corresponding actions in the lower Properties frame Changes will be reflected in the parameter list When finished click Apply or O
114. fication coi cia cee ea n ba ETE SERE SET I PR Edd 88 Performing Administration Tasks 0 0 0 cece cece 88 Update User Records 0 0 88 Set Up Windows Task Scheduler 0 0 0 0 ee eee eee 90 Using BLMONITOT uu zusenden ep ek ees 91 BI Monitor User Interface 0 0 eee eee eee 91 Monitor and Manage Currently Running Tasks 93 View Tasks and Task Sets Logs 0 0 0 cece ee eee eee 94 Archive Tasks and Task Sets Logs 0 0 0 0 eee 94 Installing and Configuring QAD Provided Dashboards 95 FAQAD Contents Create Data Source Connections 0 0 000 c cece eens 95 Install QAD Provided Dashboards llle 97 Configure Cognos Server Settings 0 0 0 0c ee eee eee 101 Perform Report Synchronization 0 0 0 0c eee eee eee 103 Create Menu Entries for the Dashboards 0005 105 Chapter 2 Using QAD BI Progress 9 Edition 107 OVERVIEW 4124 ao Reged ed ated bbe eed dae ed Edd dod 108 MECGUPRO 1 22 don see ad bee a ee tees x eae eae a eae 109 QAD BI Data Transformer 0 0 00 cece eee 109 Data Warehouse 0 00 cee nee eens 109 Microsoft Access Database 0 00 c cece eee 110 Cognos PowerPlay 2i eesarckeels ped saa cian bos aad 110 QAD Provided Models 0 0 ccc cence eee 110 Data Transformer User Interface 0 0 00 111 Star ng no saa da ee Se be ee dee
115. g 2 18 x Field Properties Field Field Properties DWH Field Name Bitto Adress Field Type Jeharacter z Convert String to Upper Case Characters s X Description Expression icm bill Cancel DWH Field Name Enter the name used to represent this field in the data warehouse The default is the MFG PRO schema field name Note You also can rename a field directly on the field selection screen by selecting its name and overtyping it AQAD 132 User Guide QAD Business Intelligence FAQAD Field Type The system displays the field data type from the MFG PRO schema You cannot change it Convert String to Upper Case If the field type is Character set this to Yes to convert the field values to upper case in the ETL script This can be used to rationalize data that is entered in mixed cases in the data source for use in Cognos PowerPlay a case sensitive tool Description Enter a text description of the field Expression The system displays the field name or formula associated with the field Click OK to save the properties of an individual field When you finish specifying field properties for the table click OK to save your configuration The system validates each field record for syntax Valid A green triangle on the table icon on the main screen indicates that replication setup is complete for the table Invalid A yellow exclamation point displays on the table icon and a mess
116. g Administration Tasks Update User Records During installation a single default user is defined with database administrator rights in the Progress Administration database Default user ID dwh Default password admin AQAD Using QAD BI OpenEdge 10 Edition You can modify this default information as well as create additional users or delete existing ones with the following procedure 1 Start Progress Data Administration 2 Select DatabaselConnect and enter connection information on the Connect Database screen If the default installation was performed the connection parameters are Physical Name dwh Logical Name dwh Network TCP Multiple Users checked Host Name localhost User ID dwh e Password admin 89 Fig 1 58 x Updating User Data Physical Name jn Browse Logical Name dah 0 Cancel Database Type PROGRESS Options lt lt Help Network TCP M Multiple Users Host Name flocalhost Service Name po User ID mh Password NEED Trigger Letin Browse Parameter fief Browse Other CONNECT Statement Parameters 3 Select AdminlSecuritylEdit User List 4 On the Edit User List screen click Add Modify or Delete to manage users For additional information on managing users in a Progress database see the Progress System Administration Guide FAQAD 90 User Guide QAD Business Intelligence FAQAD Set Up Windows Task Sch
117. g Custom Scripts Overview You can manually load custom scripts This section provides information on the interdependencies between custom scripts as well as instructions for loading them Alternately the predefined business models offered for sale by QAD include prebuilt custom scripts and tables to transform data in the data warehouse into the data required by the Cognos cubes To load custom scripts in this fashion see the sections under Using QAD Provided Model Data on page 73 Load Custom Scripts To manually load custom scripts right click the Custom Scripts folder on the main screen directory tree and select Add Script Data Transformer Fie Tools Settings AIEEE localhost eB2 t2 Active Transactions H O Code Tables H O Historic Transactions Cubes a Custom Tables I check scripts e Export MetaData The Add Script screen displays Click Browse to navigate to the directory where the custom script files were placed during installation Typically this is qadbilnstallDirNdataNscriptsNcustom For example c qadbi data scripts custom Reference 195 Fig 3 18 Selecting Custom Scripts GyData Transformer Fie Tools Settings I BS im ey B em a g localhost E e2 Pig Add Script xj E serpt pete ETL Scri Look jn C3 custom er E2 O Cube C3 Custe Fe My Recent Documents _ Scr
118. g this status information you can detect and recover any corrupted mapping that may have occurred after activities such as database replication FAQAD 52 User Guide QAD Business Intelligence Fig 1 30 DWH Table Mapping Validation AQAD DWH Table Mapping Report 15 xl Table Mapping CORRUPTED qaddb C projectsqadbi build trunk Nd cm mstr Customers CHANGED UNCHANGED Delete Table Mapping Recreate DWH Table DWH Expression theDbName ook REC D RECID amp SRC DE cm mstr O MISMAT format mismatchingx 8 to x 12 cm addr Customer CAPS C MISMAT DWHfield Bito Address is missing cm bil Bilto Address Ook cm class Customer Class CAPS Ook cm cr hold Credit Hold Ook cm region Customer Region Ook cm sispsn 1 Salesperson 1 CAPS Ook cm sispsn 2 Salesperson_2_ CAPS Ook cm sispsn 3 Salesperson_3_ CAPS Ook cm slspsn 4 Salesperson_4_ CAPS Ook cm sot Sot Name INVALID Source field cm type is missing em type Customer Type CAPS Delete Field Mapping Close Table Mapping Display and Functions The following fields display in the Table Mapping frame Status Displays the status of the table based on the status of associated fields OK No invalid or mismatched field mappings exist Corrupted At least one invalid or mismatched field mapping exists Description This field displays detailed table error information such as a table is missing Source DB Logical Name The alias for th
119. he Progress Explorer Host Enter the Progress server defined in the Progress Explorer and assigned to manage the data warehouse Config Enter the Progress Explorer Configuration to use to start and stop the data warehouse Port Enter the port or service the data warehouse runs under This is defined in the Progress Explorer Configuration User Enter a user authorized to start and stop the data warehouse Password Enter the password of the user authorized to start and stop the data warehouse AQAD 86 User Guide QAD Business Intelligence Fig 1 56 Application Settings AppServer Tab AQAD AppServer The AppServer tab includes the AppServer configuration information when parallel replication is used For more details on parallel replication see QAD BI Parallel Replication Setup on page 178 Note When serial replication is used all the fields under the AppServer tab are disabled Application Settings xi Data Warehouse ODBC Connection Cognos Transformer Replication Batch Process AppServer Notification AppService fasbroker DWH ADMIN Host localhost AppServer Host localhost DWH ADMIN Service 24001 NameServer Port 5162 DWH ADMIN Network TCP Pool Size Of Script In Pool Size Of Cube 4 AppServer Log Directory ctaadbitethlog AppServer Error Log Jappserver error log AppServer Replication Log appserver replication log P AppService Enter the name of the AppS
120. he destination frames that have had tables added Set Up Replication After selecting the tables to be included in the data warehouse you must define how data should be extracted from the source table into the data warehouse Activities include Map the source table to a new data warehouse table and apply filters Select fields to be replicated and create calculated fields Define replication and scheduling modes Map Tables to Warehouse Tables To start the replication setup select a table from the main screen and choose ToolslReplication Setup The Map Table screen displays Figure 2 16 Important You cannot choose this option for a table that has already been set up Instead select a table and choose individual options from the Tools menu for the function you want to perform For example choose Properties to modify the table mapping structure If the table type is code table you can use the WHERE clause to define a filter Figure 2 15 Using QAD BI Progress 9 Edition 129 Fig 2 15 Table Properties x Table Properties Mapping Filter You can configure replication for all of the records in the table or for a subset of the records in the table by applying a filter Filters can also be used to split a table into multiple table like entities known as subsets each of which can be a replication target See Create Subsets on page 139 for details Fig 2 16 Map Table Source Table Name The
121. he replication schedule and select the languages in which Cubes are generated Create the Cube You can create the Cube by using the Create Cube function of Data Transformer or by using a batch task Cubes can only be built after data has been replicated to the data warehouse database FAQAD 70 User Guide QAD Business Intelligence Fig 1 43 Cube Properties Cube Details Tab AQAD Check the Model The Cognos PowerPlay Transformer includes a function that verifies the correctness of the model file Select a cube in the Cubes folder on the main QAD BI screen and choose FilelCheck Model The system uses the Cognos function to verify the model and displays an appropriate message Specify Model Properties To display the Model Properties screen select a model and choose Toolsl Properties The screen contains three tabs Cube Details The Cube Details tab contains read only displays of the Cognos Transformer model name and path ET Cube Details schedule Languages l Cube Model Name Accounts Receivable Model Path Accounts Receivable mdl Schedule The Schedule tab is the same as the Table and Custom Script Schedule tabs Select Active and enter a sequence number to enable cube generation within the automated replication Using QAD BI OpenEdge 10 Edition 71 Cube Properties E xj Cube Details 5 y Replication Schedule rOptions v Activate Sequence 90 Daily z Update On
122. he source database is archived make sure that all audit information has been processed by BI audit based replication and corresponding data changes have been synchronized It is safe practice to always archive audit data in the source database to the point in time before recent BI audit based replications were performed for the database For example if you schedule BI audit based replication to take place on a daily basis modify the audit data archive scripts to archive two days worth of data so that no audit information will be moved away before it is processed Enabling disabling auditing for a table in the Enhanced Controls module may change identifying fields of the table If this happens data changes to the table since the last audit based replication up until this event will not Using QAD BI OpenEdge 10 Edition 37 be synchronized during the next replication Therefore it is important to avoid any data changes or audit data generation between audit based replication and an audit configuration change A possible solution is to perform audit configuration maintenance for the source table immediately after performing audit based replication and make sure no changes are made to the table during this time interval Enable the Source Database for Auditing Perform the following steps to audit enable the source database 1 Add a storage area to the source database to hold audit data Execute the following command in a command prompt
123. hist and ih hist or a combination of historical and active transaction records such as wod det or pod det Incremental load generally is not as time consuming as a full refresh Important The replication process for incremental load does not determine whether a record has been modified since the last replication only that it has been created Be aware that using this method for records that are commonly updated master records for example can create data synchronization problems between the QAD ERP database and the data warehouse Full Refresh The system performs the following actions Updates records that already exist in the data warehouse Copies records that have never been replicated to the data warehouse Deletes all other records in the data warehouse that are neither updated nor newly created in this full refresh Using QAD BI OpenEdge 10 Edition 35 A new integer field Updated_Seq_ is available for each table in the data warehouse to identify each full refresh This is typically used to replicate master data often including tables that do not contain more than 10 000 records It is the default for newly defined tables Audit Based The system replicates data based on audit information stored in the source database Only created updated and deleted data in the source database is synchronized to the target database which significantly improves replication performance When the audit based replication met
124. hod is selected for a table the table icon in the navigation tree changes to an icon with a capitalized A indicating that the table is audit enabled Important This method requires that the source database is OpenEdge 10 1B or higher and is audit enabled and the user connecting to the source database has the authenticated audit administrator privileges for that database You must also perform additional steps to audit enable source tables you want to replicate data from and configure replication settings before you can successfully execute audit based replication tasks See Set Up for Audit Based Replication on page 35 Custom Whether a table is replicated depends on some user defined condition for example replication only takes place when an item is associated with a product line The system displays a default Progress replication script based on the selected method If you modify the code Replication Mode is automatically set to Custom When you click OK the system validates the syntax If there are errors the table icon on the main screen displays a yellow warning sign to indicate that a problem exists Set Up for Audit Based Replication If you want to perform data replication from a source database that is OpenEdge 10 1B or higher you can take advantage of OpenEdge auditing features and replicate data based on audit information stored in the source database Using the audit based replication method only created AQAD
125. icate Data You can synchronize data between a data warehouse table and the associated MFG PRO table using two methods Automatically by setting up scheduling for each table Manually by selecting the table on the main screen then clicking Execute Now on the Tools menu The following sections describe these methods Scheduling Automatic Replication Automatic replication takes place using either a serial or parallel strategy In serial replication each task is processed sequentially based on its defined sequence that is its priority With parallel replication a pool of processors is created and each processor is assigned a task to process As a processor finishes a task it is assigned another task to be processed until all tasks have been performed with respect to sequence order Note The processor pool size is set to one less than the value of the Minimum and Maximum Servers settings on the AppServer properties screen See AppServer Pool Size on page 173 for details In order to use parallel replication the Progress AppServer must be installed and configured on the same machine as QAD BI Data Transformer For information on setting up and using parallel replication and configuring the AppServer see QAD BI Parallel Replication Setup on page 178 Using QAD BI Progress 9 Edition 135 Use the Scheduling tab to define when replication takes place for each table in the data warehouse You can set up different schedul
126. ication Method of a table replication task is set to Custom It moves data into the data warehouse using custom extraction logic Script Guidelines For a custom script to be usable by QAD BI it should follow several guidelines Field references must include the full namespace reference to the field from the database level down for example databasename tablename fieldname The database name must be the logical database name for the data warehouse followed by the letter q for example dwhq The following shows an example of a correct field reference dwhq Balance Sheet totals Cost Center For scripts that reference the data warehouse include the following alias as the first line to be used in the script implementation amp SCOPED DEFINE TRG DB DWHQ Similarly for scripts that reference the source database include the following alias amp SCOPED DEFINE SRC DB SRCDB Check the syntax of the saved script using the Progress tools or use the Check Scripts function after adding it Using QAD BI Progress 9 Edition 151 Figure 2 32 shows an example of a completed script Fig 2 32 BCOPED DEFINE TRG DB DUHQ Sample Custom define variable tooearly like dwhq Sales Performance Too early no undo ETL Script define variable ontime like duhq Sales Performance 0n time no undo define variable toolate like dwhq Sales Performance Too late no undo for each dwhq Sales Performa
127. il al mstr Allocation Account Master ald det Allocation Account Detail alm mstr Automatic Lot Master El e Aelio Cada harkar cah_hist gltr hist Available Code Tables Code Tables abd conv abd conv ac type abl eq edc abl eq edc abl eq os abl eq os abl eq wq abl eq wq m Ed ahl on unn EP Account Total Detail ap mstr Accounts Payable Master ar mstr Accounts Receivable Master ard det Accounts Receivable Detail Fi Rudnick Net sil Service Support Call History chm_mstr Service Support Call History Master ck_mstr Accounts Payable Check Master ckd_det Accounts Payable Check Detail General Ledger Transaction History ac_type ad_county ad_county ca_severity ca severity cm class cm class Using QAD BI Progress 9 Edition abs mstr ASN BOL Shipper Master ad_mstr Address Master absc_det Shipment Carrier Detail bk_mstr Bank Master abscc_det Sales Order Detail Container Charges cas_mstr Service Support Call Status Master absd_det Shipment Line Item Detail F ec mete Laet Cantar Mactar an The selection screen consists of six frames The two on the left side represent source tables from the source database The system separates standard table listings from fields controlled through generalized codes when connecting to a QADDB database The four frames on the right side represent destinations in the QAD BI data warehouse These are shown in three groups Master Files Ac
128. ing Table Mapping Clicking the Delete Table Mapping button lets you delete both table and field mapping information The data warehouse table is also deleted Recreating Data Warehouse Table You can use the Recreate DWH Table button to resolve any mismatching between source and data warehouse tables You can also use it to delete data from the data warehouse table When you click the Recreate DWH Table button the system 1 Deletes all mapped data from the data warehouse table and deletes the data warehouse table 2 Creates a data warehouse table based on the original table mapping stored in data warehouse admin Before recreating a data warehouse table ensure that you have deleted any field mappings with the status of Invalid shown in the Field Mapping frame Field Mapping Display and Functions The following fields display in the Field Mapping frame Status Status information is displayed as follows OK Both source and data warehouse fields exist and are consistent Invalid The source table field cannot be found and the field mapping must be deleted Mismatch The source table field or the data warehouse field has changed and are inconsistent This field mapping can be rebuilt by overriding the target field with the source field AQAD 148 User Guide QAD Business Intelligence FAQAD Description For mismatched or invalid fields a brief description of the error Source Field The name of the field in the sour
129. ing methods for individual tables based on the frequency of changes to the source table Important Automatic scheduling uses the Windows Task Scheduler to start a QAD BI process to review the scheduled replication tasks on a periodic basis See Set Up Windows Task Scheduler on page 175 for information Fig 2 20 x Table Properties Table Replication Schedulin g Tab Replication Schedule Options IV Activate Sequence oo Monthly Update On 1 First of The Mor omen Activate Select this field to have automatic replication take place on the specified schedule When Activate is not selected you must replicate this table manually using ToolslExecute Now When a database is marked as Inactive you cannot change the Activate check box To set the database s Active Inactive status use the Database Properties screen Sequence Specify the relative order in which the task associated with this table should be replicated Tasks are executed from low sequences to higher sequences Tasks that share the same sequence may be executed in any order among themselves In general Tasks that pull data from sources into the data warehouse should be scheduled to precede tasks that operate on data within the data warehouse AQAD 136 User Guide QAD Business Intelligence FAQAD Tasks that generate Cognos PowerCubes should be scheduled to run after all other tasks For a more detai
130. ipt Pre 4 Desktop fH e Script Balance Sheet Totals p Script Format Positions p ei Script Base Currency p ej Script Invoice Discounts p ei Script Call Billing Detail p E Script_Invoice_Trailers_ p ei Script Call Lead Time2 p m Script_Multi_Currency p i Script Call Lead Time p 28 Script Purchase Statistics p ei Script Currency Fields p C Script_Repeat_Calls2_ p ei Script Currency Fields Full p ej Script Repeat Calls p ei Script Currency Fields Incremental p ej Script Sales Order Discounts p ei Script Engineer Hours Available2 p C Script Sales Order Trailers p a Script Engineer Hours Available3 p a Script Sales Statistics p ei Script Engineer Hours Available p ej Script Stock History p a Script_Exchange_Rate_ p E Script Stock History Month p Script Forecast p ej Script Stock History Week p ei Script Forecast Simulation p i Script Validate Exchange Rates p ei Script Format Positions2 p ei Set Exchange Rate p 9 My Documents J PE My Computer E DITE MEM File name Script Balance Sheet Totals p gt Open Places Files of type Source Files p 7 Cancel Data Transformer version 2 2 0 Select the first script on the list and click Open The system populates the Script Details frame with the path the default script name which you can update as needed
131. lation Directory Metadata Model Deploy Report Deployment Pre Install Summary Gcesem2 H mAai Cognos Version ICognos8MR2 e Install Complete Operating System windows InstallAnywhere by E T M M n ers Cognos Installation Directory Enter the directory where Cognos is installed Cognos Version Indicates the version of Cognos installed Operating System Indicates the server operating system Click Next to continue On the Cognos Metadata Model Publish screen select the dashboard metadata models you want to deploy on the Cognos Server Fig 1 63 Metadata Model Publishment Meran Model Metadata SalesManager Report Deployment WarehouseManager Pre Install Summary Installing Install Complete is Select all Deselect All InstallAnywhere by Macrovision Cancel AQAD 100 User Guide QAD Business Intelligence If you want to deploy a dashboard metadata model using different package and data source names than the default values click the Advanced button next to the dashboard You can then modify package and data source names in the Alias fields Click Save to save your changes Fig 1 64 Metadata Model f 7 Publish Metadata Model Publishment Metadata Report Deployment Pre Install Summary installing Install Complete EP Select all Deselect All InstallAnywhere by Macrovision Cancel Click Next to continue 7 On the Report Deplo
132. lay Transformer includes a function that verifies the correctness of the model file Select a cube in the Cubes folder on the main QAD BI screen and choose FilelCheck Model The system uses the Cognos function to verify the model and displays an appropriate message AQAD 158 User Guide QAD Business Intelligence Fig 2 38 Check Model Relations FAQAD Check the Model Relations The Check Model Relations function provides detail information about the data structure of a cube You can see the relationship between data warehouse and source tables and fields EE GER SRCDB Table SRCDBField Transaction Sales Order Trailers B 5ales Order Trailers Eg Trailer Ej Trailer Amount SRC js Trailer Amount TX Ej Trailer Amount DW Es Currency TX CX Sales Order Trailers DB NAME Dimension Sales On c sales Order Trailers Sales Order Dimension Sales CX Sales Order Trailers DB NAME Base Currency DB a Dimension Sales Orders E5 Customer Customer Customer Transaction Sales Order Discounts Customer _ Transaction Invoice Discounts Customer C Customer Re Transaction Sales Order Lines E5 Customer R Transaction Invoice Lines E gt Customer R Customer T Transaction Invoice Trailers Customer F Customer 7 Customer T EP Entities E gt Entities_ Entities_ Invoice Det Invoice Det E5 Invoice Det Invoice Det Invoice Det Invoice Det Invoice
133. le QAD 2008 1 Standard The Service Pack 5 and 6 releases were branded as QAD 2007 and QAD 2007 1 respectively The Enterprise Financials version of the product is called QAD 2008 Enterprise and follows the same convention of identifying individual releases with a decimal number This document uses QAD ERP throughout to refer to those products except in situations where it discusses a specific release Other Related Documentation For information on how to install QAD Business Intelligence see Installation Guide QAD Business Intelligence About This Guide 3 For information about Cognos 8 see the documentation from Cognos For information on the QAD provided models and key performance indicators KPIs see Reference Guide QAD Business Intelligence Key Performance Indicators This is provided on the installation CD for each model you purchased For information about functions in QAD ERP see the relevant user guide For information about implementing the QAD NET user interface see Installation Guide QAD User Interfaces For information about QAD ReportNet Bundle see QAD NET UI 2 7 Release Notes For information about Access see the documentation from Microsoft Corporation QAD Web Site QAD s Web site provides a wide variety of information about the company and its products You can access the Web site at http www qad com For users with a QAD Web account product documentation is available for viewing or
134. led discussion of setting the sequence for a replication task see Setting the Replication Task Sequence on page 189 Frequency Specify how often the replication task for this table should be run Options are Daily the default The table is replicated each day when the Windows scheduler starts up Weekly The system displays a list of weekdays select the days you want this table to be replicated Use Ctrl Click to select more than one list entry Monthly The system displays available dates as well as First of the Month and Last of the Month options Note The Windows Task Scheduler should be configured to run the QAD BI process at least as frequently as the most frequent replication task For example if the Windows Task Scheduler is set to run every week even if a table replication task is scheduled to run every day it will only be run every week Running Manual Replication In addition to setting up scheduled replication processes you can refresh a data warehouse table with updated values from the source database at any time Select the table on the main screen and choose ToolslExecute Now Click Start on the Record Processing Status screen The system runs the replication task listing status messages to indicate its progress Note The moving status bar at the top of the screen will not necessarily fill completely to the right when the replication task completes Since each new status message is added to the t
135. lelAdd Database or click the Add Database command To edit an existing source database connection right click the database on the main Data Transformer screen and select Properties If you did not enable support for multiple data sources during installation the code page of the source database must match the value specified for default encoding during installation If you did enable this support the code page of the source database must be either ISO8859 1 or the same code page that you selected during installation See Installation Guide QAD Business Intelligence for details on installing QAD BI Data Transformer Files If you update existing properties you must disconnect and reconnect to the database to use the new settings x Database Properties Database Physical Name C gadbiidatasources eB2yaddb do Browse Logical Name eB2 iV Active V GADDB Version eB2 Connection Details v Remote I Single User Host focahost Service fsooo Other Parameters NENNEN Connection String CONNECT db C qadbi datasources eB2 qaddb db Id eB2 H localhost S 26000 N TCP i j Cancel Physical Name Specify the name of the database to connect Logical Name Specify a logical database name that will be used to uniquely identify the database and its data within the data warehouse This field can only be updated when a connection is first defined Using QAD BI Progre
136. lick Test Click Finish Repeat steps 4 through 10 to create data source connections for all the QAD BI business model cubes required for implementing the QAD provided dashboards Using QAD BI OpenEdge 10 Edition 97 Install QAD Provided Dashboards 1 Launch install exe from the QAD Dashboard installation CD 2 An introduction screen displays Choose Next to continue 3 The BI Admin Server Configuration screen displays Enter the correct information for connecting to the QAD BI administration database Fig 1 60 BI Admin Server Configuration BI Admin Server Configuration BI Admin Server Configuration Host finer NR Port Bei 3 Database Mvadmm User mm SY Password reer e Cognos Server Configura Metadata Model Deploym Report Deployment Pre Summary X Installing Install Complete InstallAnywhere by Macrovision Cancel Host Enter the host where the QAD BI administration database is running Port Enter the TCP IP port on which the QAD BI administration server is listening for database connections Database Displays the QAD BI administration database name User and Password Displays the username and password for connecting to the QAD BI administration database Click Next to continue 4 On the Cognos Server Configuration screen enter the correct Cognos Server configuration information FAQAD 98 User Guide QAD Business Intelligence
137. ll be used To support multiple MFG PRO base currencies Script_Base_Currency_ p None Script_Exchange_Rate_ p None The additional scripts shown in Table 3 4 are required for multiple MFG PRO base currencies Primary Scripts Secondary Scripts Script_Base_Currency_ p None Script_Exchange_Rate_ p None Important The custom script Script_Multi_Currency p is a utility script that is not a primary or secondary script and should not be scheduled by users Reference 197 Fig 3 19 x Add Script Script Details schedule rETL Script Script Name Script Balance Sheet Totals 0000 Balance Sheet Totals ETL Script ED E build qadbitdw scriptsicustomtscript Balance Sheet Totals p Browse _Script Preview d appltask engine defs i amp SCOPED DEFINE SRC DB SRCDB j amp SCOPED DEFINE TRG DB DWHQ z TAA a A E EEE mi Click the Schedule tab to specify when this script will run to replicate data For information on scheduling see Scheduling Automatic Replication on page 39 Note To ensure that custom scripts are run after any standard that is generated scripts set Sequence to a value that is greater than the highest standard script s sequence For additional information regarding the use of multicurrencies see Multicurrency Configuration on page 184 Repeat this task for each primary script in the directory AQAD 198 User Guide QAD Business Inte
138. lligence FAQAD A application settings 77 164 186 B batch process 83 169 178 187 189 business key indexes 47 142 business models 9 17 69 110 118 157 C code pages 120 code table filter 29 128 Cognos 8 6 108 Cognos PowerPlay Transformer 81 110 168 Cognos Transformer models 191 currency 185 custom compiled scripts 76 163 fields 32 131 indexes 45 47 140 142 scripts 55 76 149 163 194 tables 35 55 134 149 D Dashboard 10 Install 95 data merge 58 152 replication 49 143 subsets 44 139 target 50 144 Data Transformer 7 109 configuring 183 menu 13 114 navigation 12 113 setting up 17 118 source system icons 16 117 toolbar 13 114 translatable Cognos models 191 Index user interface 11 111 data warehouse 6 7 78 108 109 165 currency 185 fields 31 130 tables 27 29 53 126 128 147 databases connections 18 119 merge data 58 152 replication 29 128 status 63 153 domains 23 26 121 124 E error messages 88 171 F fields custom 32 131 mapping 31 43 51 130 138 145 properties 32 131 filter code table 29 128 l icons Data Transformer source system 16 117 Impromptu Query Definition 8 indexes 45 47 140 142 IQD Editor 72 K key performance indicators 9 110 L links rebuilding 64 155 updating 154 log in user 11 112 FAQAD 200 User Guide QAD Business Intelligence M mapping fields 31 43 51 130 138 145 tables 29 54
139. located in ProgressInstallDir properties This section describes only the process of configuring the AppServer using the Progress Explorer application 1 Open the Progress Explorer application 2 Connect to the localhost Progress Server 3 Expand the AppServer folder in the tree in the left pane 4 Right click the asbroker1 item in the expanded AppServer folder and select Properties Reference 179 Fig 3 1 p proexp12 Console Root Progress Explorer localhost AppServer asbroker1 Selecting the Properties for the AppServer Progress Explorer ag localhost H A Databases WebSpeed g AppServer 3 Oracle Data I ODBC Datase MSS DataSer E SonicMQ Adal Properties H AppServer In HA NameServer H Messengers 5 Expand the Broker item in the tree and select the General item 6 Set Operating Mode to State reset uu Fig 3 2 asbroker1 Properties xj Configuring the Broker s General Parameters Owner Information Controlling NameServer AppService Name List Logging Setting dvanced Features Server Environment Variables FAQAD 180 User Guide QAD Business Intelligence 7 Select Logging Setting under the Broker 8 Modify the Broker log filename setting to the following ETLInstallDirNlogNasbrokerl broker log where ETLInstallDiris the value supplied for the program files during the installation Fig 3 3 Configuring the x Broker s Logging B
140. m Scripts folder in the tree 2 Right click the Custom Scripts folder and select Add Script 3 Click Browse on the Add Script screen and navigate to the QADBIDataDir scripts custom directory select the Script Currency Fields Incremental p custom script and click Open 4 Select the Schedule tab and set the fields as follows Activate selected Sequence 15 Frequency any item in the list x Script Details Replication Schedule Options IV Activate Sequence fis Frequency Daily Update On 5 Again choose to add a custom script see steps 2 and 3 above Reference 187 6 Click Browse on the Add Script screen and navigate to the QADBIDataDir scripts custom directory select the Script_Base_Currency_ p custom script and click Open 7 Select the Schedule tab and set the fields as follows Activate selected Sequence 10 Frequency any item in the list Fig 3 11 Setting the Schedule Fields for Script Base Currency p 8 Repeat steps 5 through 7 for Script Exchange Rate p Modifying Currency After the first batch replication has run in a multicurrency enabled configuration the base currency may change in either the source system or the data warehouse To accommodate this change a custom script must be executed AQAD 188 User Guide QAD Business Intelligence FAQAD Note This procedure assumes that multicurrency replication has been set up as d
141. mail FAQAD 172 User Guide QAD Business Intelligence e mail Address Enter the e mail address of the person you want to receive the e mail notifications Return Address Enter the e mail address of the person you want to be used for the return address on the e mail AppServer The AppServer tab includes information the system uses to determine the replication strategy that is serial or parallel via the Use AppServer check box and when parallel replication is used the AppServer configuration information For more details on parallel replication see QAD BI Parallel Replication Setup on page 178 Fig 2 51 Application x Settings Data Warehouse Table Linking Cognos Transformer Batch Process Notification AppServer Tab JV Use AppServer AppService Jasbroker DWH ADMIN Host localhost AppServer Host localhost D WH ADMIN Service 24001 NameServer Port ft 62 DWH ADMIN Network TCP AppServer Pool Size fa AppServerLogDirectoy fe gadbetNog AppServer Error Log fappserverenonlog 0000 AppServer Replication Log fappserverreplcationlog 000000000 omen Use AppServer When this is selected parallel replication is used for the batch replication When not selected serial replication is used AppService Enter the name of the AppServer broker This is the same value that appears in the Progress Explorer tree under the AppServer folder Also r
142. modify the default table selections To view the available tables right click the database icon in the left pane of the main Data Transformer screen Then choose Select Tables The system scans the schema definitions of the connected database and displays the Select Source Tables screen illustrated in Figure 1 13 AQAD 28 User Guide QAD Business Intelligence Fig 1 13 Select Source Tables AQAD Select SourceTables xi Available Tables Master Files un NM Asset Book Deta ac mstr Account Master ad mstr Address Master bk mstr Bank Master cas mstr Service Support Call Status Master E ec mete Cock Cantar Macar abs mstr ASN BOL Shipper Master absc_det Shipment Carrier Detail abscc_det Sales Order Detail Container Charges absd det Shipment Line Item Detail absi mstr Shipper Information Master absl det Shipment Detail Line Charges absr det Shipment Requirement Detail abss det Shipment Sequence Detail accd det Asset Cost Change Detail acdf mstr Account Default Master Active Transactions acd det Account Total Detail ap mstr Accounts Payable Master ar mstr Accounts Receivable Master d d acm mstr Co product By product Average Cost Master z ard det aoe Recelvable Detall acod mstr Application Code Master Fi i DII act mstr Alternate Container Master Historic Transactions acx mstr Account Cross Reference Master adc ctrl Address Control adx det External Address Cross Reference Det
143. must set the Log to DB option to Yes under the Batch Process tab of Application Settings See Batch Process on page 83 FAQAD 92 User Guide QAD Business Intelligence Fig 1 59 QAD BI Monitor Main Screen FAQAD SIBI Monitor Control Settings Eus Rhus E China Status Icons BI Monitor distinguishes tasks and task sets using icons DY Charge_Codes_ g Sales Orders F Sales Drder Details E6 Reason Codes E Cost Centers 00 03 02 00 03 10 Ej Current Costpiices y Customers There are three task set statuses Ej Customer Addresses Task TaskStatus Database All Status AllDatabases Y Search Running The task set is being executed Finished Finished Finished Finished Running Pending Pending Pending Pending Pending Pending Pending BEES Finished All tasks in the task set have been executed successfully without any errors Error One or more errors occurred during execution of tasks in the task set There are six task statuses e Running The task is being executed Error Error occurred during execution of the task Stopped The task has stopped Pending The task is waiting to be executed Stopping The task is being stopped Finished The task has been executed successfully without any errors Using QAD BI OpenEdge 10 Edition 93 The statuses are represented by the following icons
144. nce Available Fields Business Key Fields Ei Effective_Date_ Pa Type_ By Voucher Se Ba Amount_TX_ The dollar amount For tt Py Bank_ The code identifying the Fa Cost_Center_ The cost center of the By Currency_TX_ The currency in which tl Fs Entity _ If central the entity For Ey Open_ 4 flag indicating the vou By Sub_Account_ The general ledger sub b Supplier _ The address code of thr 3 When finished click OK to save changes You can also store business key definitions in the metadata file for reuse by exporting metadata Preview Data You can view data both after it has been replicated to the data warehouse and in its original form in the data source First select a table on the main screen Then To view the data records in the data warehouse choose ToolslPreview Target Data To view the source database records choose ToolslPreview Source Data Figure 1 28 shows the contents of the address master ad mstr table in QAD ERP as well as the corresponding Address table in the data warehouse Note that the QAD ERP table shows all fields in the schema identified by the schema field names the data warehouse displays only the mapped fields 49 FAQAD 50 User Guide QAD Business Intelligence Fig 1 28 Table Preview FAQAD To sort the records based on values in a specific column click the column heading To filter records by database select a database from the D
145. nce exclusive lock delete dwhq Sales Performance end for each dwhq Transaction History NO0 LOCK where dwhq Transaction History tr type I55 50 each dwhq Invoice Lines NO LOCK where dwhq Invoice Lines Invoice dwhq Transaction History tr rmks and dwhq Invoice Lines idh line dwhq Transaction History tr line and dwhq Invoice Lines idh due date lt gt break by dwhq Transaction History rder number by dwhq Transaction History tr line if first of dwhq Transaction History tr line then do ASSIGN tooearly 0 ontime 0 toolate 0 end if dwhq Transaction_History_ tr_effdate lt dwhq Invoice_Lines_ idh_due_date then tooearly tooearly l dwhq Transaction History tr qty chg dwhq Invoice Lines idh qty ord else if dwhq Transaction History tr effdate duhq Factuur Regels idh due date then ontime ontime l dwhq Transaction History tr qty chg duhq Invoice Lines idh qty ord else toolate toolate 1 dwhq Transaction History tr qty chg dvhq Invoice Lines idh qty ord Adding Scripts to Data Transformer To add a custom script right click the Custom Scripts folder on the main screen and select Add ETL Script Fig 2 33 Add Script Add Custom Script Script Details Schedule rETL Script ETL Script C qedbiethdatascrints custom Scrigt_Balance_Sheet_Totals_p Browse Script Name ecrit Balance Sheet Totals Script Preview itappl repl def iy itappl gdefs iy
146. needed However you cannot update the content of the script itself on the preview screen On the Schedule tab specify when this script is run This is the same as scheduling replication for standard tables For a more detailed discussion of setting the sequence for a system task see Setting the Replication Task Sequence on page 189 Using QAD BI OpenEdge 10 Edition 61 Validate System Task Scripts After loading custom system task scripts you can have the system regenerate and check all the scripts at the same time based on the data warehouse structure Select the System Tasks folder on the main screen directory tree and then choose ToolslCheck Scripts The system updates the script icons in the directory tree to show whether they passed validation A green triangle indicates a validated script a yellow exclamation point indicates a problem You can check an individual system task script by right clicking the script under the System Tasks folder then selecting Check Scripts Use the Task List The Task List provides a consolidated view of all scheduled tasks whether activated or not on a single screen It is an alternative to selecting each element in the application tree and scheduling it using the Properties function Use it to select and deselect elements scheduled for automated replication To view the task list click the Task List command on the toolbar Fig 1 38 L Task List olx Task List Task Sets d
147. no longer exists in the source database In both cases the links between the data source and the data warehouse are broken and can no longer be used for replication purposes Continuing to perform data replication without first rebuilding the links may cause data corruption in the data warehouse This is where business keys comes into play You can designate key fields that can represent unique identifiers of records to construct more reliable links between the data source and the data warehouse You can then use the business keys to synchronize the REC ID values between the source and target databases to re establish the links based on the REC ID field for replication purposes If you purchased one or more of QAD provided business models default business key definitions for each standard table are included in metadata files and are loaded into the administration database when metadata is imported You can also modify default business keys or create new business key definitions To maintain business keys for a table 1 Right click a table in the directory tree and choose Business Keys from the shortcut menu 2 The business key maintenance window displays Double click a field in the Available Fields frame to move it to the Business Key Fields frame and designate it as a business key field Move a field in the opposite direction to remove it as a business key field Using QAD BI OpenEdge 10 Edition Fig 1 27 Business Key Maintena
148. nsformer tools to fine tune your environment as needed See Using QAD Provided Model Data on page 160 for information Using QAD BI Progress 9 Edition 119 Figure 2 5 summarizes the work flow used in the setup tasks Subsequent paragraphs describe each task Fig 2 5 QAD BI Setup Work Flow Define database connections Load QAD defined models Select tables from MFG PRO from meta data database If required adjust table and field properties create additional subsets and custom data Set up replication by adding MFG PRO tables and fields to data warehouse Create subsets i Schedule replication Add or adjust indexes f Modify application settings man d and perform administrative 1___ Optional tasks i Define Database Connections Note Many required connection settings are specified during QAD BI installation See Modifying Application Settings on page 164 if you need to modify any of this information Before you begin mapping fields from the source MFG PRO database to the data warehouse you must define the connection parameters for the MFG PRO database The Data Transformer reads the source schema which are used to define the tables and fields that make up the data warehouse structure FAQAD 120 User Guide QAD Business Intelligence Fig 2 6 Database Properties AQAD To add a new source database connection select Fi
149. ntrol to configure Cognos server settings in QAD ERP 1 Log in to the ERP and go to Report Control 36 4 21 24 2 Enter the correct information that matches the Cognos server settings configured in Cognos Fig 1 67 ini xi Report Control j 36 4 21 24 ontro o7 36 4 21 24 Category COGNOS Gateway fittp coli23 80 cognos8 cgi bin cognos cgi Dispatcher http coli23 9300 p2pd servlet dispatch WSA WSDL http coli23 9300 wsa services RroImpl wsdl Logon Namespace sunone Logon Username luojun Update Password No Namespace Type NTLM Report Runner Namespace CJPAA Report Runner Username luojun Fi Go 2 Help 3 Ins 4 End 6 Menu 7 Rel 8 Clr 11 Paste Gateway Enter the gateway URL to connect to the Cognos server FAQAD 102 User Guide QAD Business Intelligence FAQAD A gateway is an extension of a Web server program that transfers information from the Web server to another server Gateways are often CGI programs but may follow other standards such as ISAPI and Apache Modules Dispatcher Enter the internal dispatcher URI to connect directly to the dispatcher WSA WSDL Enter the WSDL Web Services Definition Language file URL This is configured when the WSA Web Services Adapter is installed Logon Namespace Enter the logon namespace to log on to the Cognos server as a full authentication provider Namespace is a configured instance of an authentication provider that all
150. o Address Ook cm class Customer Class CAPS Ook cm cr hold Credit Hold Ook cm region Customer Region Ook cm sispsn 1 Salesperson 1 CAPS Ook cm sispsn 2 Salesperson_2_ CAPS Ook cm sispsn 3 Salesperson_3_ CAPS Ook cm slspsn 4 Salesperson_4_ CAPS Ook cm sot Sot Name INVALID Source field cm type is missing em type Customer Type CAPS Delete Field Mapping Close Table Mapping Display and Functions The following fields display in the Table Mapping frame Status Displays the status of the table based on the status of associated fields OK No invalid or mismatched field mappings exist Corrupted At least one invalid or mismatched field mapping exists Description This field displays detailed table error information such as a table is missing Source DB Logical Name The alias for the source database Source DB The physical path to the source database Source Table The name of the table from the source database Source Table Status and DWH Table Status These status fields indicate if mappings between the source and data warehouse are new unchanged or missing Unchanged Field mappings are unchanged Changed Field mappings have changed Either source table fields or data warehouse fields have changed Using QAD BI Progress 9 Edition 147 Missing Either the source table or the data warehouse table is missing Use the two buttons in the Table Mapping frame to delete tables or recreate them Delet
151. og in to the ERP and go to Menu System Maintenance 36 4 4 On the Menu System Maintenance screen assign a menu label and a report code to the menu number you want to set up for the dashboard home page Fig 1 70 Menu System pu p 1 36 4 4 Menu System Maintenance 08 3 Maintenance Language ID S 36 4 4 Menu Selection Exec Procedure Help File Fi Go 2 Hlp 3 Ins 4 End 6 Mnu 7 Rel 8 Clr 9 Prev 10 Next 11 Buf Language ID Specify a language code that uniquely identifies the language you want to use for the menu label Menu Enter a number associated with the menu from which to access the dashboard home page Selection Enter a menu selection number associated with the dashboard home page Label Enter a label name for this menu selection Name Optionally enter a short symbolic name associated with the dashboard Exec Procedure Specify the report code of the dashboard home page in the following format urn qad report cognos ReportCode FAQAD 106 User Guide QAD Business Intelligence FAQAD The report code was specified when you synchronized the dashboard home page from the Cognos server to QAD ERP See Perform Report Synchronization on page 103 Press Go to save the record You have set up the menu for accessing a QAD BI dashboard from the QAD NET 2 7 user interface in QAD ERP Repeat the previous step to create menu entries for all the dashboards you want to impl
152. ols See Using QAD Provided Model Data on page 160 for information on how to implement models Data Transformer User Interface Important This guide is limited mainly to descriptions of the features of QAD BI Data Transformer For information on user interfaces for associated tools such as Cognos PowerPlay and Microsoft Access see the user documentation for those products QAD BI Data Transformer features a Windows based graphical user interface Windows conventions are used throughout the product for example drop down menus shortcut menus drag and drop and so on AQAD 112 User Guide QAD Business Intelligence Fig 2 2 QAD BI Data Transformer Log in Screen AQAD This section describes user interface features specific to QAD BI Data Transformer Starting As part of the initialization process the Data Transformer prompts for user credentials i Welcome To The QADBI Data Transformer _ 15 xj User Password r Language English en x QAD A Passion For Manufacturing The credentials provided are authenticated against the users configured in the data warehouse database When installed the data warehouse database is configured with a single user User dwh Password admin You can add new users or change the default user with the Progress Data Dictionary tool connected to the data warehouse database See Update User Records on page 173 for details The language selected from the
153. on for configuring replication task sequences in Data Transformer Overview In order to configure replication tasks to run automatically in batch mode the Schedule tab for Tables Custom Scripts and Cubes must be properly set up This section discusses the setting of the Sequence field on these screens so that the dependencies for each task type Tables Custom Scripts and Cubes are met Fig 3 12 xj Setting the Sequence for a LIE Replication Task Replication Scl ptions mes v Activate Sequence foo Frequency Daily Update On Dependencies exist between the Table Custom Script and Cube tasks In general all Table tasks must run before the Custom Script tasks and all Custom Script tasks must run before the Cube tasks Single Currency Sequence Dependencies Table 3 1 lists the dependencies between the task types and the recommended values that should be used in a standard single currency installation Note All dependencies require that prior dependencies have also been met FAQAD 190 User Guide QAD Business Intelligence Table 3 1 Single Currency Dependencies Table 3 2 Multicurrency Sequence Dependencies FAQAD Recommended Script or Task Type Dependencies Sequence Setting Table None 00 Script_Currency_Fields_ Table 15 Incremental_ p Custom Script Script_Currency_Fields_ 20 Incremental_ p Cube Custom Script 90 Multicurrency Se
154. op of the list you can determine that replication is finished when the Total Update Time message displays Using QAD BI Progress 9 Edition 137 Fig 2 21 x Record Processing See Status Processing records 300 to 400 Total update time 00 00 00 Total processed records 301 Processed 300 Records 08 52 27 Processed 200 Records 08 52 27 Processed 100 Records 08 52 27 Total deleted records 0 Starting at 08 52 27 The buttons at the bottom of the screen control this function Click Start to begin replication Click Stop to end replication Note Replication may not stop immediately when you click the button depending on the nature of the script being executed Click Close to exit the screen Rename Table When you perform Replication Setup tables are created in the data warehouse If necessary you can change a table name by using the Rename Table function on the Data TransformerlTools menu This function opens the Rename Table screen Fig 2 22 iBi xi Rename Table Old Table Name ETSI NUES New Table Name OK Cancel If the old table name cannot be found in the data warehouse an error message is displayed An error message also displays if a user enters A blank table name Reserved words FAQAD 138 User Guide QAD Business Intelligence Fig 2 23 Field Mapping AQAD A table name that is already in use When you click OK and Automatically Check ETL Scripts and Autom
155. op to end update Click Close to exit the screen After rebuilding links is complete you can review the rebuild loglog file generated in the DWHWorkDir 1log directory for more detailed processing and error information Important Every time you want to dump load table or archive data in the source database make sure you perform these operations in the following order 1 Perform replication to refresh the data warehouse tables with updated values from the source database See Replicate Data on page 39 for details 2 Perform table dump load or data archiving 3 Rebuild links Fig 1 40 Rebuild Links AQAD 66 User Guide QAD Business Intelligence Fig 1 41 SQL Query Analysis Tool AQAD If you do not exactly follow this order or if you perform other operations that affect either the source database or the data warehouse in between these steps data integrity may be compromised and data corruption may occur in future replications Using the SQL Query Analysis Tool QAD BI provides a built in SQL query analysis tool that you can use to execute SQL queries against the data warehouse database display and export query results and view query execution plans A query execution plan outlines step by step how a SQL query is run This information is very useful when it comes to finding out why a query is running slowly and seeking to optimize the query To launch the SQL query analysis tool click the SQL Tool icon on
156. opy from 3 Click OK The screen displays the tasks in the new task set as selected 4 Select tasks to add them to the current task set or deselect tasks to remove them from the current task set then click Apply to save the task set To delete a task set select the task from the Task Set drop down list and click the Delete button Note The Default task set is the system task set and can not be deleted Selecting a task set from the Task Set shows the corresponding task set view Tasks in the task set are displayed as selected Note Ifatable is deleted from the data warehouse database the corresponding task is removed from all task sets it belongs to Tasks Tasks are displayed in a paginated view with a maximum of 50 records on each page Use the navigation controls at the bottom of the screen to easily page through records or jump to a page by its number You can select or deselect tasks individually To select or deselect all the tasks on the current page at once use the Select All on This Page and Deselect All on This Page commands To select or deselect all the tasks on all pages use the Select All on All Pages and Deselect All on All Pages commands Using QAD BI OpenEdge 10 Edition 63 Enter the whole task name or part of one in the Task text box and choose from the Database drop down list then click Search to display tasks that meet the search criteria or filter tasks by databases Database Status A source syst
157. ote host mode only client server mode can be used and the connection mode options are grayed out User Mode Choose whether the application runs in single user or multiple user mode Single User Only one user can exclusively connect to the data warehouse database Multiple Users Multiple users can connect to the data warehouse database simultaneously Note If the connection mode is client server only the multiple user mode can be used and the user mode options are grayed out Replication Mode Choose the replication strategy Serial Replication Each task is processed sequentially based on its defined sequence that is its priority Parallel Replication Choose this strategy to allow for more than one replication task to be processed concurrently by separate Progress processes With parallel replication a pool of processors is created and each processor is assigned a task to process As a processor finishes a task it is assigned another task to be processed until all tasks have been performed with respect to sequence order Batch Process Use settings on the Batch Process tab to customize QAD BI in the following areas Improve performance Allow concurrent access to the data warehouse while the batch is run Use the Progress supplied database server administration AQAD 84 User Guide QAD Business Intelligence Fig 1 55 Application Settings Batch Process Tab AQAD Use custom scripts batch files
158. ource table Click OK to display the field mapping screen Again navigation is similar to standard table setup Use the Field Properties and Add Field commands in the upper left corner of the screen to update an existing field or add a new one You can define a field to be any data type supported by Progress However once you have initially specified a data type you cannot change it Instead delete the field and add a new one with the correct data type Once you have created the custom table and defined its fields you can define indexes for the table using the Indexes right click option while the custom table is selected in the tree Create Custom ETL Scripts Because of the way some types of data are stored in data sources data is not always directly usable as management information In such cases Data Transformer lets you reference custom ETL scripts that you have Fig 2 31 Custom Table Setup AQAD 150 User Guide QAD Business Intelligence FAQAD written to convert MFG PRO data into warehouse data more appropriate for use in analysis Custom scripts are associated with custom tables which were described in the previous section Important You must be familiar with writing Progress queries to create custom scripts See the Progress documentation for information Note Custom ETL scripts are used to restructure data in the data warehouse A related type of script a custom replication script is used when the Repl
159. ows access to user and group information for authentication and access control You can configure Cognos 8 components to use the Windows native security NT LAN Manager NTLM as the authentication source or configure a new LDAP namespace for use with Sun ONE Directory Server See the related Cognos 8 documentation for information about how to configure namespaces in Cognos Logon Username Enter a username that is authorized to access applications and resources on the Cognos server The username is part of the credential created on the Cognos server for user authentication Note This does not have to be a valid system user ID Update Password Indicate whether you want to change the password associated with Logon Username Yes When you press Go you will be prompted to enter and confirm the new password No The user s password will not be changed Namespace Type Specify the type of the namespace configured as a third party trusted signon provider Two namespace types are supported by the Cognos server LDAP LDAP version 3 directory servers and NTLM Windows Native Security Using QAD BI OpenEdge 10 Edition The authentication namespace configuration is part of the Cognos Content Manager installation on the Cognos server Report Runner Namespace Enter the namespace configured as a third party trusted signon provider to perform user authentication to run reports Heport Runner Username Enter a username that is authori
160. pendencies 00000 190 Creating Translatable Cognos Transformer Models 191 OVetVIeW 25 daonan Aloe sta iau ee dieu sed diaeateng O dias 191 FAQAD Contents Cognos Transformer Model selle 191 Data Transformer User Defined Labels 2 04 192 Manually Loading Custom Scripts 0 2 0 cece eee eee 194 OVELVIEW cese vos aene eee Sa ge ane de EEEE gutes 194 Load Custom Scripts 0 0 eee ee eee 194 vii AQAD viii User Guide QAD Business Intelligence FAQAD 2 User Guide QAD Business Intelligence AQAD Overview This document describes how to implement and use QAD Business Intelligence The product is available in Progress 9 and OpenEdge 10 editions For ease of use the document provides a stand alone chapter that covers implementation and use of each edition Chapter 1 Using QAD BI OpenEdge 10 Edition on page 5 Chapter 2 Using QAD BI Progress 9 Edition on page 107 Additionally Chapter 3 Reference on page 177 provides reference information that applies to both editions This guide applies to multiple versions of QAD s Enterprise Resource Planning ERP application from MFG PRO 9 0 through the current release of QAD 2008 Enterprise During the product life cycle QAD changed the name of the former MFG PRO eB2 1 product to QAD 2008 Standard Individual releases are identified by a decimal number for examp
161. quence Dependencies Some special considerations must be observed if Data Transformer is being used in multicurrency mode Table 3 2 lists the dependencies between the task types and the recommended values that should be used in a standard multicurrency installation Note All dependencies require that prior dependencies have also been met Recommended Script or Task Type Dependencies Sequence Setting Table None 00 Script_Base_Currency_ p Table 10 Script_Exchange_Rate_ p Script_Currency_Fields_ Script_Base_Currency_ p and 15 Incremental_ p Script_Exchange_Rate_ p Custom Script Script_Currency_Fields_ 20 Incremental_ p Cube Custom Script 90 Reference 191 Creating Translatable Cognos Transformer Models This section provides information for creating translatable Cognos Transformer models Overview The QAD BI Data Transformer supports creating Cognos Transformer models using tokens to define text for dimensions levels categories and measures These tokens are translated during replication using user defined replacement text specified using Data Transformer Label function Cognos Transformer Model The model is created using tokens of the form token for any text that requires translation for example dimension names level names and so on Keep in mind that when a language is not defined in the Languages tab of Cube Properties the value of token is used for the text the and is s
162. ransformer to connect to the data warehouse database through IQD files Fig 1 52 Application Settings ODBC Data Warehouse ODBC Connection Cognos Transformer Replication Batch Process AppServer Notification Connection DBC Connection Dsn faan User ba ooo Password Co Schema pul 0 ooo EN Note In general this field should not be changed DSN Enter the Data Source Name created for the data warehouse proxy ODBC connection User Enter the user name for the data warehouse proxy By default this is dwh Password Enter the password for the specified user By default this is admin Schema Enter the data warehouse Progress schema name The default set during installation is PUB FAQAD Using QAD BI OpenEdge 10 Edition 81 Cognos Transformer The Cognos Transformer tab includes path preferences used by the Cognos PowerPlay Transformer The ETL tool uses these settings to set the values in the Cognos Transformer Application Settings Lx Data Warehouse ODBC Connection Cognos Transformer Replication Batch Process AppServer Notification Default Directories Models fes gadbijdata cognos models 00 Browse Data Temp cadiekemp Browse Madel Temp cmadieemp Browse Cubes cadbidaakogeskubes Browse Log cmadiehog Browse Login O Password NEN User
163. redentials provided are authenticated against the users configured in the data warehouse database When installed the data warehouse database is configured with a single user User dwh Password admin FAQAD 12 User Guide QAD Business Intelligence Fig 1 3 QAD BI Data Transformer Main Screen AQAD You can add new users or change the default user with the Progress Data Dictionary tool connected to the data warehouse database See Update User Records on page 88 for details The language selected from the drop down list determines the language in which text labels and menus display Navigating Navigation within Data Transformer provides up to three methods of accessing individual functions depending on the type of action being performed Menus on the Data Transformer main screen Toolbar commands Context sensitive shortcut menus The primary view of Data Transformer is the main screen which includes a toolbar a menu bar and a two pane navigation tree The left pane shows the current structure of the data warehouse When completely minimized the top level tree is a series of category folders When completely maximized it lists all the tables and custom scripts in the warehouse Select a table to display its fields in the right pane fj Data Transformer Jox Il File Tools Settings t amp SS Se leugera E H dyz sh pc E qaddb Domain Database 56658 E
164. rify that the selected objects are those you want to import Then click OK to activate the import The created data structure then displays on the main screen directory tree The display includes a green triangle beside a valid object or a yellow exclamation point beside an invalid object Note In MFG PRO eB2 1 metadata must be loaded for each active domain in the database Important If this is first time you have loaded the standard XML metafiles ensure that you load Custom Scripts xml last After the custom scripts are loaded they are automatically compiled Since the scripts refer to several tables in the data warehouse the table schemas must have already been loaded for successful compilation to take place If these schemas have not been created in the data warehouse compilation will fail Fig 2 44 Metadata Loaded File Tools Settings jt g seram ess E g localhost E53 E Active Transactions H O Code Tables H Historic Transactions C Master Files H O Custom Scripts tag m D L Custom Tables Bulk Generate and Validate Scripts After loading model data and custom scripts you can have the system regenerate and check all the scripts at the same time based on the completed data warehouse structure Select the database on the main screen directory tree and then choose ToolslCheck Scripts The system updates the table icons in the directory tree to show whether they
165. roker Parameters General Owner Information Controlling NameServer AppService Name List Broker logging level Logging Setting Terse hd Advanced Features Server Environment Variables Append to broker log file Cancel Reset Help 9 Expand the Server item in the tree and select the General item 10 Modify the Server startup parameters setting to the following pf ETLInstallDirNappserver pf Where ETLInstallDiris the value supplied for the program files during the installation FAQAD asbroker1 Properties E XI Owner Information Startup DLC bin _proapsy exe Controlling NameServer AppService Name List Logging Setting dvanced Features c qadbi etl C gadbi dw winChar Startups PROPATH Logging Setting Pool Range dvanced Features 2002 Environment Variables 11 Modify the PROPATH setting to include the following at the beginning ETLInstallDir DataInstallDir Where ETLInstallDiris the value supplied for the program files and DataInstallDir isthe value supplied for the data files during the installation asbroker1 Properties EN Owner Information Startup DLC bin _proapsy exe Controlling NameServer eene EUM prctgdbiaNppeer 7 Logging Setting ie pf inChar Startup PROPATH Logging Setting Pool Range Advanced Features Environment Variables 12 Selec
166. rtually any data the designer wants to put in them To create a new table right click the Custom Tables folder on the main screen and select Create Table The Map Table screen displays Using QAD BI OpenEdge 10 Edition 55 x Table Table Mapping SourceTable Name Table Type Custom Table DWH Table Name Source Table Filter While the overall process is similar to mapping a QAD ERP table to the data warehouse you can only access one field DWH Table Name Table Type is automatically set to Custom Table and you cannot define code to filter records because a custom table is not directly based on any source table Click OK to display the field mapping screen Again navigation is similar to standard table setup Use the Field Properties and Add Field commands in the upper left corner of the screen to update an existing field or add a new one You can define a field to be any data type supported by Progress However once you have initially specified a data type you cannot change it Instead delete the field and add a new one with the correct data type Once you have created the custom table and defined its fields you can define indexes for the table using the Indexes right click option while the custom table is selected in the tree Create Custom ETL Scripts Because of the way some types of data are stored in data sources data is not always directly usable as management information In such cases Da
167. rvers AppService Name List Logging Setting Maximum servers Advanced Features E Server General Logging Setting Pool Range Advanced Features Environment Variables I Important If changes were made to the AppServer s configuration the server must be shut down and restarted for the modifications to take effect Configure Data Transformer 1 2 Start the Application Settings dialog for Data Transformer Switch to the AppServer tab Select the Use AppServer check box to enable processing of tasks in parallel When this check box is not selected the default serial processing will be used Enter information for your setup for every field on the tab The following graphic depicts the AppServer default settings See AppServer on page 86 for details AQAD 184 User Guide QAD Business Intelligence Fig 3 8 Configuring AppServer Parameters AQAD Application Settings x Data Warehouse Table Linking Cognos Transformer Batch Process Notification v Use AppServer AppService asbroker DWHADMiNHost ocahos AppSewerHost ocahost DWH ADMIN Service 24001 NameServer Port p DWH ADMIN Network fice AppServer Pool Size hc AppServerLogDitectoy fe qadbetNog AppServer Error Log apseweremrho 00000 AppServer Replication Log opsewerepictionlg See detailed field descriptions for the
168. rwise tables you moved in earlier sessions display in the frames where they were placed Once a table has been moved it no longer displays in the source frame Using QAD BI OpenEdge 10 Edition 29 Note Drag and drop works in both directions If you decide you do not want a table to be replicated to the data warehouse you can move it back to the left side of the screen If you save your changes and then decide you want to remove a table that has not yet been set up for replication reopen the Select Tables screen and drag the table to its source location Important Removing a table from the right to the left once replication setup has been completed has no effect on the structure of the data warehouse To remove a replicated table from the data warehouse select it on the main screen and choose ToolslDelete When you finish selecting tables close the screen The system prompts you to save your changes The directory tree on the main screen updates to display a folder for each of the destination frames that have had tables added Set Up Replication After selecting the tables to be included in the data warehouse you must define how data should be extracted from the source table into the data warehouse Activities include Map the source table to a new data warehouse table and apply filters Select fields to be replicated and create calculated fields Define replication and scheduling modes Map Tables to Warehouse Tables
169. s the Progress System Administration Guide Using QAD BI Progress 9 Edition 175 Set Up Windows Task Scheduler You can use Data Transformer to define the replication schedule for each individual task See Scheduling Automatic Replication on page 134 for information on defining task schedules in Data Transformer However you also must set up an operating system level scheduling task to look at the individual task schedules and run the processes accordingly Note This procedure is based on Windows XP Professional It may vary for other versions of Windows 1 To run the Windows Task Scheduler Wizard select StartlControl PanellScheduled TaskslAdd Scheduled Tasks 2 Click Next to continue 3 Click Browse and select the Run Tasks shortcut located in the directory where program files were installed 4 Choose the task schedule Daily Weekly Monthly One time only When my computer starts WhenIlog on Depending on your choice you may be prompted for further scheduling information Important The schedule you define here describes when Windows launches its own scheduling task which in turn looks at the Data Transformer scheduling tasks You should be sure to define the Windows task so that it is appropriate to the schedule granularity defined for Data Transformer For example it would not make sense to define a monthly Windows task if you want to replicate tables every day 5 Enter the name of a Windows account with
170. s QAD BI provides two system tasks to manage ETL log files and BI monitor archives Fig 1 35 Merge Table Replication Method Fig 1 36 Task List with Merged Item Tables FAQAD 60 User Guide QAD Business Intelligence Fig 1 37 Add Custom System Task Script AQAD This function also lets you reference custom scripts that you have written to perform system maintenance tasks such as updating statistical data in the data warehouse Important You must be familiar with writing Progress queries to create custom system task scripts See the Progress documentation for information Adding Custom System Task Scripts To add a custom system task script right click the System Tasks folder on the main screen and select Add Script x Script Details schedule r Script Script C gadbiethdetalscrints customisystem Script_Update_Statistics_ Browse Script Name Update Statistics _Script Preview I Update Statistics p Used as a System Task that update statistics of dwh database xj I Copyright 1986 2006 QAD Inc Carpinteria CA USA bal d All rights reserved worldwide This is an unpublished work I Revision 1 1 2 2 wp hk I This procedure connects to database in the proper mode e zi Click Browse to navigate to the directory containing the Progress script The system displays the contents of the script You can update the default script name as
171. s specify the service name or TCP IP Port on which the remote database server is listening for database connections Single user When Remote is No indicate if this is a single user or a multi user connection User and Password Enter the user name and password for connecting to the database server if required Other Parameters Specify any additional Progress connection parameters to apply to this connection Connection String The system displays the connection string based on the settings entered on the screen after the connection configuration has been accepted DWH ADMIN Host Enter the host where the data warehouse administration database server is running DWH ADMIN Service Specify the service name or TCP IP Port on which the data warehouse administration database server is listening for database connections Domain Selection Click the Import from DWH ADMIN button to retrieve a list of all domains defined in the data warehouse database then from the domain list select the domains you want to connect to Using QAD BI OpenEdge 10 Edition 23 Domain Database Data Sources The introduction of the domain concept in the QAD ERP database provides flexible implementation options for supporting multiple business operations within a single database and eliminates the need for a single database wide base currency or database wide control settings The domain is essentially a logical partition within a single database Any number
172. s menu to open the Record Deletion Status screen Fig 1 29 Delete Data Records from Data Warehouse x T Processing records 1000 to 2000 Total update time 00 00 00 Total deleted records 1501 Processed 1000 Records 01 53 14 Processed 900 Records 01 53 14 Processed 800 Records 01 53 14 Processed 700 Records 01 53 14 Processed 600 Records 01 53 14 gt cele 4 294 4 Stop To start the deletion process click Start If it is necessary to stop the deletion before completion click Stop Validate Tables The Validate Table function on the Data TransformerlTools menu lets you validate table and field mappings between source tables and data warehouse tables Validation takes place in two phases The system first checks for valid tables then it checks each field mapping between source table and data warehouse table When checking for valid tables the system determines whether a table has changed is unchanged or is missing This information displays in the Source Table Status and DWH Table Status columns of the Table Mapping frame shown in Figure 1 30 When checking field mappings the system determines the field mapping status and displays this information in the Field Mapping frame If any field mapping status is Mismatching or Invalid the status Corrupted displays in the Status column of the Table Mapping frame You should fix the field mapping problems before attempting to recreate a table Usin
173. s screen you must click a command to add or delete an index or to define a primary index Toolbar Commands and Menu Functions Table 1 1 through Table 1 5 list the menu functions and related toolbar commands Command Menu Function Replication Setup Subset Ef Check Scripts lt Execute Now Si Preview Data data warehouse o Refresh Field Mapping EJA Properties Table 1 1 Main Screen Toolbar Commands FAQAD 14 User Guide QAD Business Intelligence Table 1 2 Field Selection Toolbar Commands Table 1 3 Indexes Toolbar Commands FAQAD Command Menu Function BE Task List Add Database ia A Application Settings ze Rebuild Links F Internal Progress Editor EN SQL Query Analysis Tool Parameter Maintenance Table Command Menu Function Field Properties Add Field Ra E kA Delete Field custom tables only Command Menu Function Add Index Using QAD BI OpenEdge 10 Edition 15 Command Menu Function Delete Index kA Designate as Primary Index Table 1 4 Command Menu Function Import Metadata Toolbar Commands Open Cancel Open and load metadata file Exit screen without loading metadata Table 1 5 Command Menu Function Tasklist Toolbar Commands O Select all on this page Deselect all on
174. se See Replicate Data on page 134 for details 2 Perform table dump load or data archiving 3 Rebuild links Using QAD BI Progress 9 Edition 157 If you do not exactly follow this order or if you perform other operations that affect either the source database or the data warehouse in between these steps data integrity may be compromised and data corruption may occur in future replications Working with the Model Cognos models supporting multidimensional analysis of business process areas can be purchased from QAD The models map the contents of the data warehouse to concepts supported by the PowerPlay product such as dimensions levels categories and measures The product of executing a model against the data warehouse is a Cognos PowerCube The location of the models is referenced in the Cognos Transformer tab on the Application Settings screen See Figure 2 48 on page 168 for details Note Cubes can only be built after data has been replicated to the data warehouse database You can Create the cube Check the model Check the model relations View the model properties to specify the replication schedule and select the languages in which cubes are generated Create the Cube You can create the cube by using the Create Cube function of the Data Transformer or by using a batch task Cubes can only be built after data has been replicated to the data warehouse database Check the Model The Cognos PowerP
175. se structure Fig 1 5 Database Properties 1861 x Database Properties Database Properties QADDB Database Physical Name Jaaddb Browse Logical Name qaddb v Active Data Source Type oannes z Version eB2 1 7 Connection Details IV Remote Single User Host fing sh pe Service 26000 Other Parameters Connection String CONNECT db qaddb Id qaddb H Ing sh pc 5 26000 N TCP Domain Selection cm Physical Name Specify the name of the database to connect Logical Name Specify a logical database name that will be used to uniquely identify the database and its data within the data warehouse This field can only be updated when a connection is first defined FAQAD 20 User Guide QAD Business Intelligence FAQAD Active Indicate whether this is an active connection Databases marked as active are automatically connected when Data Transformer is started Tasks associated with databases marked as inactive are not executed during batch non interactive replication Data Source Type Indicate if this is a QAD ERP database a QAD BI data warehouse database DWH or other type of data source such as ODBC See QAD BI Data Warehouse Database Data Sources on page 20 Version When the data source type is QADDB select the QAD version associated with the QAD ERP database See Domain Database Data Sources on page 23 Remote Indicate whether the datab
176. shortcut menu Note The source database must be already audit enabled or you will get an error message Note You can audit enable all tables in a table group domain or database by right clicking the group domain or database icon in the directory tree and choosing Audit Enable from the shortcut menu Right click the table icon and choose Replication Settings from the shortcut menu Note You can configure replication settings for all tables in a table group domain or database by right clicking the group domain or database icon in the directory tree and choosing Replication Settings from the shortcut menu An audit based replication settings window displays Change the settings as needed Audit Replication Control Panel Call Billing History E3 Initial Status C Synchronized Time Stamp Date Time oo 00 00 anil Using QAD BI OpenEdge 10 Edition 39 Initial Status Set the initial table replication status to determine how far back to synchronize changes to the data warehouse based on the audit information stored in the source database Not Synchronized This is the default initial status when you set up audit based replication for the table and no replication task has been executed yet When the initial status is Not Synchronized the system synchronizes all the changes recorded by the source database audit information to the data warehouse during the next replication
177. solidate data from multiple data sources that share the same underlying table definitions After defining replication for one database use the Merge command to configure additional databases to reuse the replication configuration of the configured database You can then redefine the replication method for each table in the merged database as needed To merge in data from a data source right click the data source and select Merge Tables The system displays the data warehouse structure associated with all other data sources BOs E 3 2 H0O Active Transactions Do Historic Transactions amp D1C3 Master Files To use the same replication configuration as another data source select the check box associated with the replication tasks you want to reuse After you have merged tables from additional databases you can then redefine the replication method for each table in the merged database To change the replication method for a merged table right click the table and choose Properties from the shortcut menu In the Table Properties window select Self Defined Script script type under the Replication tab then select the new replication method you want for the table Using QAD BI OpenEdge 10 Edition 59 Table Properties x Replication Scheduling C Incremental Load Full Refresh Audit Based Custom Replication Method Script Type C Reference Script Replication Script DEFINE VARIABLE del
178. ss 9 Edition 121 Active Indicate whether this is an active connection Databases marked as active are automatically connected when the Data Transformer is started Tasks associated with databases marked as inactive are not executed during batch non interactive replication QADDB Indicate if this is an MFG PRO database Yes or not No See Domain Database Data Sources on page 121 Version Select the MFG PRO version associated with this database Remote Indicate whether the database server is local or remote Host When Remote is Yes enter the host where the database server is running Service When Remote is Yes specify the service name or TCP IP Port on which the remote database server is listening for database connections Single user Indicate if this is a single user or a multi user connection Other Parameters Specify any additional Progress connection parameters to apply to this connection Connection String The system displays the connection string based on the settings entered on the screen after the connection configuration has been accepted Domain Database Data Sources The introduction of the domain concept in the MFG PRO database provides flexible implementation options for supporting multiple business operations within a single database and eliminates the need for a single database wide base currency or database wide control settings The domain is essentially a logical partition within a single datab
179. ss to which the specified log in user belongs and which will be used to regenerate the models For additional information regarding the use of the Cognos cube security and Transformer see the Cognos Transformer online help and user documentation Batch Process Use settings on the Batch Process tab to customize QAD BI in the following areas Improve performance Allow concurrent access to the data warehouse while the batch is run Use the Progress supplied database server administration Use custom scripts batch files to start and stop the data warehouse Fig 2 49 x Application Data Warehouse Table Linking Cognos Transformer Batch Process Notification AppServer Settings Batch Process Tab Database c iyadbiidataidbidwh db Browse Repository c iyadbitdataidbidwh admin db Browse Database Management Progress Explorer Use non integrity Parameter Log Language Sn Log Level nto Scripts Start Stop Progress Explorer Database fawn Port Post Host flocathast User Contig detauitcontiguration Password Database Enter or browse to the location of the data warehouse file By default this is the dwh db file in the directory where QAD BI is installed FAQAD 170 User Guide QAD Business Intelligence FAQAD Repository Enter or Browse to the location of the data warehouse repository By default this is the dwh admin db file in the directory where QAD BI is inst
180. stom Forecast Simulation Daily eB2 Active Custom Call Billing Detail Daily eB2 Active Custom Eg 20 Invoice Discounts Daily eB2 Active Custom E2175 20 Balance Sheet Totals Daily eB2 Active Custom Ie 9n Inventory History Analysis Daily Cube General Ledger Daily Cube y Engineer Utilization Dail Cube 4 gt ues Database Status A source system database can be either active or inactive as defined on the Database Properties screen When a source system is marked as inactive none of its tasks will execute under the batch replication process FAQAD 154 User Guide QAD Business Intelligence Fig 2 36 Database Status in the Task List AQAD You can determine if a task is from an inactive database by viewing the Active Inactive column in the Task List or by observing the icon for the task Inactive tasks display a grayed out icon See page 121 for details When a database is marked as Inactive you cannot change the Activate check box To set the database s Active Inactive status use the Database Properties screen lox MO i gh SalesOrderHea Daily QAD Inset OE oo Accounts_ Daily qaddb Active DE 00 Accounts X Daly QAD Inactive 2 10 Exchange Rate Daily gaddb Active E Base Currency Daly qaddb Active Dus Currency Field Daly gaddb Active is Currency Field Daly gaddb Active FAR on Trwnire liereiinte Maly nadh Artive Update Links In QAD BI Microsoft Access serves as the query layer between
181. system displays the source table name FAQAD 130 User Guide QAD Business Intelligence FAQAD Table Type Read only field that displays the table type active historic master or code The system categorizes the tables based on this value DWH Table Name Enter the name of a new data warehouse table that will be created to hold data from the source table Source Table Filter Enter Progress code to filter records that are replicated to the data warehouse Syntax for the filter always has the same format WHERE amp SRC DB tablename fieldname operator value AND OR amp SRC DB tablename fieldname operator value Operator can have the following values 2 gt lt lt gt matches begins When this is blank all records are replicated When you save your changes the system validates the syntax Click OK to save your changes and display the field mapping screen Map Fields to Warehouse Fields Use the Field Selection frame to select the fields from the source table that will be replicated to the data warehouse table Note After initial replication setup access this function by selecting a table and choosing ToolslField Mapping Note If you loaded the database structure from QAD provided metadata you do not have to use this procedure unless you want to modify the default field mapping structure The first column indicates whether a field is included in the replication
182. t Logging Setting under the Server Reference 181 Fig 3 4 Configuring the Server s General Server Startup Parameter Fig 3 5 Configuring the Server s General PROPATH Parameter FAQAD 182 User Guide QAD Business Intelligence Fig 3 6 Configuring the Server s Logging Parameters FAQAD 13 Modify the Server log filename setting to the following 14 15 ETLInstallDir log asbrokerl server log Where ETLInstallDir is the value supplied for the program files during the installation asbroker1 Properties x Broker Server log filename T UM od C gadbi etl logasbrokerl server log EDT Logging Setting l Pool Range Server logging level Advanced Features Meese o 3OTt lt is S i x Environment Variables Append to server log file Cancel Reset Help Select Pool Range under the Server Modify the initial number of servers to start maximum servers and minimum servers settings to the integer value equal to your AppServer Pool Size setting 1 For example this document indicates that the value 4 should be used So use 5 for the Initial number of servers to start setting See Pool Size of Script on page 86 and for details Reference 183 Fig 3 7 Configuring the Server s Pool Range Parameters asbroker1 Properties xj Broker General Initial number of servers to start Owner Information Controlling NameServer Minimum se
183. t first select the table on the main screen and then choose ToolslField Mapping Select the field you want to edit and click the Field Properties command Define Replication Method To specify how the system replicates data between the data source and the data warehouse select a table on the main screen and choose Toolsl Properties Then click the Replication tab Important The Properties tool is available only for tables that have completed the replication AQAD 34 User Guide QAD Business Intelligence Fig 1 18 Table Properties Replication Tab AQAD Table Replication Scheduling Replication Method Cy Load Falke C Audit Based C Custom Replication Script DEFINE VARIABLE delCounter AS INTEGER NO UNDO i Set the KeepAlive variables IDEFINE VARIABLE vKeepAlive AS INTEGER NO UNDO DEFINE BUFFER Keep AliveBuffer FOR amp 5RC DB sfh_hist Set the current Sequence number DEFINE VARIABLE theSeqNbr AS INTEGER NO UNDO dafina variable eCuwrDiaka ac daka na unda zl Note If you loaded the table warehouse structure from QAD provided metadata you do not have to use this procedure unless you want to modify the default setup Choose one of four replication methods Incremental Load Only records that have never been replicated to the data warehouse are selected for replication This method is typically used for tables that contain historical records such as tr
184. t of the QAD BI solution is supplied by Cognos PowerPlay an online analytical processing OLAP software application that lets users perform multidimensional analysis create reports and share them to make better decisions PowerPlay draws information from the data warehouse using the proxy to model and build Cubes the Cognos term for optimized multidimensional data sets that enable users to perform analysis with quick response times See the Cognos documentation for information on configuring and using PowerPlay as well as on how to configure Cubes QAD Provided Business Models QAD sells the following seven models to provide out of the box analysis for standard QAD ERP installations The seven models together provide support for more than 100 key performance indicators KPIs Inventory monitoring providing insight into current and historic inventory trends Manufacturing performance including statistics on work orders production costs productivity and throughput Forecasting performance Sales analysis including statistical trends in sales and revenue margins Financial controls encompassing gauges for profitability investments and the effectiveness of financial management Purchasing controls from patterns in purchase price and cost to materials delivery Service performance insights including service profitability and trends in customer calls and contract selection For information on the models and KPIs se
185. ta Transformer lets you reference custom ETL scripts that you have Fig 1 31 Custom Table Setup AQAD 56 User Guide QAD Business Intelligence FAQAD written to convert the QAD ERP data into warehouse data more appropriate for use in analysis Custom scripts are associated with custom tables which were described in the previous section Important You must be familiar with writing Progress queries to create custom scripts See the Progress documentation for information Note Custom ETL scripts are used to restructure data in the data warehouse A related type of script a custom replication script is used when the Replication Method of a table replication task is set to Custom It moves data into the data warehouse using custom extraction logic Script Guidelines For a custom script to be usable by QAD BI it should follow several guidelines Field references must include the full namespace reference to the field from the database level down for example databasename tablename fieldname The database name must be the logical database name for the data warehouse followed by the letter q for example dwhq The following shows an example of a correct field reference dwhq Balance_Sheet_totals_ Cost_Center For scripts that reference the data warehouse include the following alias as the first line to be used in the script implementation amp SCOPED DEFINE TRG DB DWHQ e Similarly for scrip
186. tails Location as Location Item as Item Date Created as Date Created Date Expired as Date Expired Qty Allocated as Oty Allocated Lot as Lot Reference as Reference Qty On Hand as Qty On Hand ientory Status as Inventory Status Inventory Details value Standard Costprice SRC as zl Columns Column Number Entity Entity Name Site Description Site Location Item Date Created Date Expired zi Nh Allecatad OK Cancel 5 Oc OD GO cCO Using QAD Provided Model Data If your QAD BI configuration includes one or more of the seven predefined models purchased from QAD load the schema using Data Transformer s Load Meta Data function instead of building the data warehouse tables manually from QAD ERP schema You can then use the table and field tools to customize the schema for the predefined models as needed Note In MFG PRO eB2 1 and later releases of QAD ERP you must load metadata and scripts for each domain that you want to analyze FAQAD 74 User Guide QAD Business Intelligence Perform the following tasks to load the model data and make it ready for use 1 Load metadata 2 Load custom scripts 3 Bulk validate custom scripts 4 Complete model setup Load Metadata To load the metadata that describes the data warehouse structure used in the models right click a database or a domain name if you are using a
187. te Data Source Connections nstall QAD Provided Dashboards Configure Cognos Server Settings Perform Report Synchronization Create Menu Entries for the Dashboards Create Data Source Connections You must create data source connections to the required QAD BI business model cubes in Cognos to use them in dashboard reports FAQAD 96 User Guide QAD Business Intelligence FAQAD To create data source connections to QAD BI business model cubes 1 2 3 4 5 10 11 Log in to Cognos Connection On the toolbar choose Tools Directory Click the Data Sources tab Click the new data source button In the Name box type one of the following and then click Next Purchase Statistics Cube Sales Analysis Cube Sales Forecast Performance Cube Current Inventory Analysis Cube Inventory History Analysis Cube Important The name is case sensitive and you must type exactly the same name as listed above In the connection page under Type click Cognos PowerCube and then click Next The connection string page for the selected database appears In the Read cache size MB field enter the cache size of the cube in megabytes If you leave this field blank or enter 0 Cognos Connection uses the default value in the ppds c g xm1 file in the configuration folder In the Windows location field enter the location and name of the cube file for the data source connection To test whether the parameters are correct c
188. the task to begin Using QAD BI OpenEdge 10 Edition 91 7 Click Finish to save the task Using BI Monitor BI Monitor User Interface This section describes user interface features specific to BI Monitor To start BI Monitor launch the program from the Windows Start menu By default choose All ProgramsIQAD BIIMonitor The BI Monitor main screen includes a menu bar a task set pane and a task pane The task set pane on the left shows execution information of currently running task sets or task sets history logs The upper part of the task pane on the right displays execution information of all tasks pertaining to the selected task set in the task set pane Tasks are displayed in a paginated view You can use the navigation controls at the bottom of these screens to easily page through records or jump to a page by its number The lower part of the task pane displays task details including task type sequence number of deleted and copied records and error messages In both panes you can use the search box at the top to filter records Use vertical scroll bars to scroll through records and horizontal scroll bars to scroll through details Clicking the lt I button on the upper left corner of the task pane hides the task set pane and the button becomes lI Clicking the button again displays the task set pane To exit BI Monitor click the Exit button at the bottom Important To use BI Monitor to monitor and manage tasks you
189. the tree x Available Schema Images Selected Schema Images CorpExchRates Manage Domains When a domain database is first added the Data Transformer retrieves all defined domains and creates additional connection records for each one These domains are created with the Active field set to false You must set this field to true for any domains you want to replicate before they can be scheduled for the batch process Using QAD BI Progress 9 Edition 125 Database Properties Database Database Properties 22242 1 x Physical Name eb21 Logical Name demot Browse Active JV QADDE Version fesz z Connection Details VV Remote I Single User Host localhost Service 25005 Other Parameters Connection String CONNECT db eb21 Id demo1 H localhost 5 25005 N TCP Select domains to include using either the standard Delete function or the Select Domains screen Use the Select Domains screen to choose which domains you want to work with and remove those you do not need to replicate Move domains from the Available Domains list to the Selected Domains list to have them show up in the tree File Tools Settings tz 5 BY os 85 coliao eg QAD Connect f st92 Disconnect 5 fs Western cB gade Domain E m eva Domain Rlade Fig 2 11 Database Properties for an Individual
190. tion DAO Version phoDBEngne38 psn fawho User FO Password FO Schema fewn Using QAD BI Progress 9 Edition 167 Database Enter or browse to the location of the data warehouse proxy Access mdb file Automatically Refresh Linked Tables Select this option to automatically update data warehouse proxy link data when mapping tables or after importing metadata DAO version Enter the Database Access Objects database engine version Note In general this field should not be changed DSN Enter the Data Source Name created for the data warehouse proxy ODBC connection This is initially set during installation See Installation Guide QAD Business Intelligence for details on updating the ODB account User Enter the user name for the data warehouse proxy Note By default no user name is required Password Enter the password for the specified user Note By default no password is required Schema Enter the data warehouse Progress schema name The default set during installation is dwh AQAD 168 User Guide QAD Business Intelligence Fig 2 48 Application Settings Cognos Transformer Tab AQAD Cognos Transformer The Cognos Transformer tab includes path preferences used by the Cognos PowerPlay Transformer The ETL tool uses these settings to set the values in the Cognos Transformer Application Settings x rDefault Directories Models Jegadhitdwicognosmodels
191. tive Transactions and Historic Transactions as well as a fourth division for fields using generalized codes Move tables from the left to the right side using drag and drop You can move tables from either frame on the left into any frame on the right If this is the first time you have selected tables from the database unless you added tables by loading QAD provided metadata the frames on the right will be blank Otherwise tables you moved in earlier sessions display in the frames where they were placed Once a table has been moved it no longer displays in the source frame 127 FAQAD 128 User Guide QAD Business Intelligence FAQAD Note Drag and drop works in both directions If you decide you do not want a table to be replicated to the data warehouse you can move it back to the left side of the screen If you save your changes and then decide you want to remove a table that has not yet been set up for replication reopen the Select Tables screen and drag the table to its source location Important Removing a table from the right to the left once replication setup has been completed has no effect on the structure of the data warehouse To remove a replicated table from the data warehouse select it on the main screen and choose ToolslDelete When you finish selecting tables close the screen The system prompts you to save your changes The directory tree on the main screen updates to display a folder for each of t
192. tor can have the following values gt lt lt gt matches begins FAQAD 140 Fig 2 25 Indexes AQAD User Guide QAD Business Intelligence After defining the subset you can continue to define fields and replication settings just as in the standard replication setup Add Indexes When you select tables for the data warehouse the system disregards any indexes defined in the data source Instead you can custom design indexes for each table after adding it to the warehouse This lets you test the performance of your data models first then add indexes that are appropriate to your specific needs Note You can view a list of indexes defined in the data source by selecting the table on the main screen and choosing ToolslSource indexes To add an index to a table select the table on the main screen and choose ToolslIndexes The Indexes screen displays any existing indexes Note By default the system creates a primary index for each table called DB NAME It includes the system maintained DB NAME field and REC ID for Progress data sources You cannot modify or delete this index unless you create a new index and set it as the primary index Ce 6 6 x Ea Bank w DB NAME Primary Close Use the commands at the top left to add or delete an index or designate it as primary Using QAD BI Progress 9 Edition 141 When you click the Add Index command the scr
193. tripped from the token Fig 3 13 ax Defining Cognos EE z Transformer Model Dimension name Customers Text Using a Token General Calculation Allocation Time Description Dimension type Regular C Time Prohibit automatic creation of new categories Exclude the dimension from auto partitioning FAQAD 192 User Guide QAD Business Intelligence Fig 3 14 Labels AQAD The token is replaced by the value defined using Data Transformer Label function described in the next section for the language being processed Data Transformer User Defined Labels In addition to defining your Cognos Transformer model using tokens Data Transformer s configuration database must be updated with the token and its language dependent translations This is accomplished using the following steps 1 In Data Transformer choose SettingslLabels from the menu This displays the Labels screen 2 On the Labels screen click the New button on the toolbar ini x D es Account Rekening Nederlands du Account Account English en Account Compte Francais Fr Account Konto Deutsch ge Account Cuenta Espa ol Latino ls Account Totals P amp L Account Totals P amp L Account Totals P amp L Account Totals P amp L Account Totals P amp L Account Type Account Type Account Type Account Type Rekeningtotalen P amp L Account Totals P amp L Comptes totaux P amp L Kontosummen P amp L Total
194. ts If your company has multiple instances of QAD BI installed you can use this feature to synchronize the setup data Additionally you can use the XML file as a backup To reload data from exported files use the Load Meta Data function described on page 161 Rebuild Links For Progress data sources QAD BI Data Transformer uses the DB NAME and REC ID fields to maintain links between the data source and the data warehouse However this approach is not always reliable in some circumstances When the user dumps and loads tables in the source database the REC ID values of the records in these tables are changed Data is archived and no longer exists in the source database In both cases the links between the data source and the data warehouse are broken and can no longer be used for replication purposes Continuing to perform data replication without first rebuilding the links may cause data corruption in the data warehouse QAD BI Data Transformer lets you rebuild links by synchronizing the REC ID values between the source and target databases based on the REC ID field so that you can continue to perform replication without data loss or corruption See Add Business Key Indexes on page 142 for details on business key index AQAD 156 User Guide QAD Business Intelligence Fig 2 37 Rebuild Links AQAD Note The rebuild links function does not update custom tables You need to write Progress scripts in the update
195. ts that reference the source database include the following alias amp SCOPED DEFINE SRC DB SRCDB Check the syntax of the saved script using the Progress tools or use the Check Scripts function after adding it Using QAD BI OpenEdge 10 Edition 57 Figure 1 32 shows an example of a completed script Fig 1 32 BCOPED DEFINE TRG DB DUHQ Sample Custom define variable tooearly like dwhq Sales Performance Too early no undo ETL Script define variable ontime like duhq Sales Performance 0n time no undo define variable toolate like dwhq Sales Performance Too late no undo for each dwhq Sales Performance exclusive lock delete dwhq Sales Performance end for each dwhq Transaction History NO0 LOCK where dwhq Transaction History tr type I55 50 each dwhq Invoice Lines NO LOCK where dwhq Invoice Lines Invoice dwhq Transaction History tr rmks and dwhq Invoice Lines idh line dwhq Transaction History tr line and dwhq Invoice Lines idh due date lt gt break by dwhq Transaction History rder number by dwhq Transaction History tr line if first of dwhq Transaction History tr line then do ASSIGN tooearly 0 ontime 0 toolate 0 end if dwhq Transaction_History_ tr_effdate lt dwhq Invoice_Lines_ idh_due_date then tooearly tooearly l dwhq Transaction History tr qty chg dwhq Invoice Lines idh qty ord else if dwhq Transaction History tr effdate duhq Factuur Regels idh due
196. urce by selecting the table on the main screen and choosing ToolslSource indexes Fig 1 24 Subset Definition FAQAD 46 User Guide QAD Business Intelligence Fig 1 25 Indexes FAQAD To add an index to a table select the table on the main screen and choose ToolslTarget Indexes The Indexes screen displays any existing indexes Note By default the system creates a primary index for each table called DB NAME It includes the system maintained DB NAME field and REC ID for Progress data sources You cannot modify or delete this index unless you create a new index and set it as the primary index D x Bl Indices For Table Call Billing History Fy Curr _Yalued_ DB NAME Primary idx_db_suid_ Unique Invoice_ Invoice Call Line Service Type Llsed Updated Seq Close Use the commands at the top left to add or delete an index or designate it as primary Using QAD BI OpenEdge 10 Edition 47 When you click the Add Index command the screen illustrated in Figure 1 26 displays Fig 1 26 x Add Index Name sales orders us Available Fields Index Fields Fie Seq Fie Description Ey Credit Order Determines if the Sales Order Ii By Credit_Terms_ code indicating the credit terr Ey Curr Valued Fs Currency TX The currency in which to pay fo Fa DB NAME E Discount Pct The discount percentage to be Ej Due Date
197. usiness Intelligence QAD BI product lets users extract and restructure data from the QAD ERP and other data sources into a Progress based data warehouse presenting a business oriented view of the enterprise Analysts can use Cognos 8 to perform powerful multidimensional analysis against the data warehouse QAD BI Data Transformer can also connect to other QAD BI data warehouse databases as its data source Multiple data warehouse databases can be chained together where data residing in lower level databases are aggregated to the high level data warehouse for analysis This multi layer data warehousing architecture has several benefits Data residing in legacy databases that can not be directly accessed by QAD BI Data transformer can be retrieved through intermediate data warehouse databases For example if you installed QAD BI on OpenEdge 10 QAD BI Data Transformer can not directly connect to legacy Progress 8 data sources due to Progress backward compatibility limitations However you can overcome this by connecting to a QAD BI Progress 9 edition data warehouse that stores data extracted form Progress 8 databases Data replication performance is boosted Data Transformer can extract data from physically distributed data sources more efficiently QAD BI can work in conjunction with previous versions of QAD BI in extracting transforming and loading data The QAD BI solution includes One or more existing data sources Data
198. y archive 13 QAD Domain 56659 Py Date_Tag_ E QP Domain 56660 Bp DB NAME C3 Active Transactions Fa Item_Type_ The allowable or suggested value for the field EM Code Tables Fa Item_Type_Description_ The user s comments regarding this entry FP Customer_Classes_ Bp REC ID Fy Customer_Regions_ Bp SUID E cete He SS RENENRNNNNRRRRRN Item Groups Item Types Location Type Supplier Types m Updated Seq a c H O Historic Transactions H O Master Files ic Custom Scripts B 8 QPDIV1 Domain 56661 amp unicode Domain Database 15625 E43 Cubes i J Accounts Receivable Call statistics Contract statistics o Current Inventory Analysis Engineer Utilization WB General Ledger e Inventory History Analysis Oo Production Downtime Statistics zl pata Transformer version 2 7 Using QAD BI OpenEdge 10 Edition 13 Some functions are available using any of these methods For example when a table is selected in the main menu you can define subsets by clicking Subset from the Tools menu clicking the Subset command on the toolbar or right clicking the table name and selecting Subset Whichever method you use the same Map Table screen displays Note Some screens include their own toolbars typically containing two or three additional commands that apply only to the current function Other functions are available using only one method For example on the Indexe
199. yment screen select the dashboard reports you want to deploy on the Cognos Server Note To successfully implement reports for a dashboard you should also deploy its metadata model in the previous step Fig 1 65 Report Deployment Report Deployment ELV El PurchaseManager er OMG ELM dashboards ew C3 purchasemanagerdashboard Pre Install Summary PurchaseManagerHomepage Installing DeiiverQualty v _DeliveryPerformance Install Complete _DeliveryPerformance2 tw _DeliveryPerformance3 r Iv Pril2DotNetBrowser Vv _OnTimeDelivery r iv _PurchaseCostStatistics Iv _PurchaseStatistics InstallAnywhere by Macrovision e NINE AQAD Using QAD BI OpenEdge 10 Edition 101 Click Next to continue 8 An installation summary screen displays If all information is correct click Install to proceed with the installation Fig 1 66 Installation S Please review the following before continuing Summary B fibt dopo NR BI Admin Server Configuration Host biperf e Port 38001 Installing oe pria Install Complete Cognos Server Configuration Gateway URL http localhost 9300 p2f Namespace NTLM User kvz Cognos Installation Directory C Program Files Cognos Cognos Version Cognos8MR2 ThstallAnywhete by Macrovision E 9 When installation is complete exit the installation program Configure Cognos Server Settings Use Report Co
200. zed to run reports on the Cognos server The username is part of the credential created on the Cognos server for user authentication Press Go Do not change anything on the second screen and exit the program Perform Report Synchronization Use Report Synchronization to synchronize the deployed dashboard reports from the Cognos server to the ERP as report resources 1 2 In QAD ERP go to Report Synchronization 36 4 21 2 Accept the defaults on the first screen and press Go Choose Yes when prompted to confirm the information 103 Fig 1 68 Report rprpsync p 1 36 4 21 2 Report Synchronization 08 29 07 Synchronization 36 4 21 2 Dispatcher http coli23 9300 p2pd servlet dispatch WSA WSDL http coli23 9300 wsa services RroImpl wsdl List Mismatching Reports on Server fies List Mismatching Report Resources No The next screen lists all the reports that do not have matching report resources in the system If installed you can find the following QAD provided dashboard home pages PurchaseManagerHomepage e SalesManagerHomepage WarehouseManagerHomepage Note Dashboard reports accessible from these home pages are not displayed and will be synchronized along with associated home pages AQAD 104 User Guide QAD Business Intelligence Fig 1 69 Mismatching Reports List FAQAD f coli41 qad com PuTTYtel 36 4 21 2 Report hronization Mismatching Reports on Server

User Guide: QAD Business Intelligence

Contents

Download Pdf Manuals

Related Search

Related Contents