BI User Guide (4.0), Version 1.1 4.0

User Manual:

Open the PDF directly: View PDF PDF.
Page Count: 69

DownloadBI User Guide (4.0), Version 1.1 4.0
Open PDF In BrowserView PDF
BI User Guide
Release 2014, Version 1.1
March 1, 2014
NEWSCYCLE Solutions – Confidential & Proprietary

Copyright
© 2014 NEWSCYCLE Solutions. All rights reserved. All brands and their products are trademarks or
registered trademarks of their respective holders and should be noted as such.
newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

ii

Contents
Getting Started --------------------------------------------------------------------------------------------------------- 1
BI Architecture Scenarios
1
BI Database Sizing
3
Oracle® Database Specific Recommendations
4
Setting up the BI Database ------------------------------------------------------------------------------------------ 5
Identify Data Sources
5
Create BI Database Tables
7
Log in to the Database Table Utility
7
Create tablespaces and ADBASE_BI user for Oracle® databases
8
Create a blank BI database
11
Initialize BI
11
Import Data into BI
13
Importing Ad Order Records
14
Importing Insertion Records
17
Importing GL Records
21
Using BI Importer Arguments
23
Reading the Log File
23
Populate the BI Database (Initial Load)
23
Loading Customer Accounts
24
Loading Contracts
24
Loading Orders
24
Loading GL
25
Audit the Initial BI Load
27
Enable BI Feeder Table Processing
27
Updating the BI Database ----------------------------------------------------------------------------------------- 29
Running the BI Populator
29
Updating BI Manually
29
Updating BI Periodically
30
Scheduling BI Updates
30
Using Execution Criteria
31
Using the Object ID Filters
35
Running the BI Interface App
37
How to Balance Orders in AdBase and BI
40
How to Balance AR in AdBase and BI
46
Checking Balances
50
newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

iii

Using the BI Utility
51
Using the Execution Criteria Dates
52
Viewing Unpopulated Items
53
Balancing Ad Orders
55
Viewing User Entries
57
Purging the BI Database-------------------------------------------------------------------------------------------- 58
Dropping the BI Database Tables -------------------------------------------------------------------------------- 59
FAQ ---------------------------------------------------------------------------------------------------------------------- 60

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

iv

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

v

Getting Started
This document explains how to use Mactive’s Business Intelligence (BI) database and software
applications. This chapter discusses some of the preparation that needs to be completed before
beginning set up of your BI system.

BI Architecture Scenarios
Before beginning, you must first designate a machine that will house the BI database. This document
describes three scenarios for housing the BI database. The samples provided are Mactive’s
recommended scenarios for setting up your site’s BI architecture.
Scenario 1 allocates a separate piece of hardware for the BI database. The BI database resides on a
stand-alone database server. Using this solution the server can also serve as the machine that runs the
BI Populator and BI Interface App. See the database sizing information on page 3 to determine the
potential size of the BI database. This information will also help you decide the type of hardware to
purchase as far as CPU power and RAM.

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

1

In Scenario 2 , the BI database resides on the replication server. The replication server houses two
databases, one dedicated to the replication process of production and the other for BI. The BI Populator
and BI Interface App run on a stand-alone PC connected to the replicated server. With this solution, you
may still need to purchase additional database licenses.

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

2

For sites that are smaller (i.e., 10 seats or less), Scenario 3 may be considered. In this scenario, both the
AdBase production database and the BI database are housed on the same server. If your site chooses
this scenario, Mactive recommends that you ensure the BI database is located on a different disk than
the production database. This arrangement will help with database performance.

Scenarios 2 and 3 require that you have the BI Populator and BI Interface App running on a dedicated
Microsoft® Windows machine. Mactive recommends using a system with a Intel® Pentium® 4 2.8 GHz
processor or greater with at least 2 GB of RAM.

BI Database Sizing
Once a scenario is decided upon, the next step is to determine the size of the BI database, using the BI
Database Sizing Workbook (BI_Database_Sizing_Workbook.xls). This spreadsheet provides an estimate
for the size of the BI database using information from your production database. The data is only an
estimate. Like the AdBase database, you must configure the BI database to grow each year. The BI
Database Sizing Workbook has three tabs, including:




The Total Size Requirement tab, which provides the total size after you have entered your site’s
numbers.
The Questions tab, which allows you to enter results for the database queries.
The SizingCalcs tab, which provides information on how the database sizing is calculated.

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

3

Use the following queries to complete the Questions tab.


Customers:
Select count (*) from customers;



GL:
Select count (*) from fntranslinedist;
Select count (*) from aotransactionacctmap;



Orders:
Select count (*) from aoadorder;



Insertions:
Select count (*) from aoadrundates;



Contracts:
Select count (*) from cocontractinstance;

Also on the Questions tab, you must set a range for the number of years you anticipate storing BI data.
The default value in the spreadsheet is 45 years (e.g., 1/1/1980 to 1/1/2025).

Oracle® Database Specific Recommendations
Refer to the General Recommended Settings for Installation of Oracle 9i R2 on Sun Solaris
(Oracle_9iR2_Mactive_Recommended_Settings_V2.pdf) for information on creating the BI database and
tablespaces.
Mactive offers two recommendations for sites using Oracle-based BI databases. For optimal
performance, if your BI database is an Oracle® database and on a different server, you should set the
star_transformation_enabled parameter in the INIT.ORA file to true. Also, if you are using scenario 2 or
3 described earlier in this document, Mactive recommends that you create a new database on the
server so that the BI database will reside in its own database with its own INIT.ORA settings. If the BI
tables are on the same database as the AdBase data and on a single instance, do not change the
INIT.ORA parameters for the database.
NOTE: At this time, there are no specific recommendations for sites using Microsoft® SQL Server
databases for BI.

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

4

Setting up the BI Database
The previous sections discussed your options for BI database configurations as well as Mactive’s
recommendations for setting up your BI database. Once you have selected a server and created the
necessary database and tablespaces for BI (Oracle databases), you must complete the following steps in
the given order.
1. Create the AdBase and BI ODBC connections on the PC where the BI Populator resides. Depending

2.
3.
4.
5.
6.
7.
8.

on the type of database you are running, Oracle® or Microsoft® SQL Server, the steps to setup ODBC
connections are different. For that reason, this process is not detailed in this document. For
instructions, see the online document How to Setup ODBC Connections on your customer pages.
Identify the AdBase and BI data sources.
Create the BI database tables (see page 7).
Initialize the BI database (see page 11).
Import data into the BI database (see page 13).
Complete the Initial Load of data into the BI database (see page 23).
Audit the Initial BI Load using Mactive’s BI Auditor applications (see page 27).
Check the Enable BI Feeder Table Processing box in System Admin (see page 27).

NOTE: Before continuing, make sure you have a current backup of your AdBase database. If any
problems should arise, the database backup can be restored and you will not lose information.

Identify Data Sources
You must run Configure AdBase to point the AdBase software to the correct production AdBase schema
and BI reports schema data sources as established in your ODBC connections. The ODBC connections
must be created prior to beginning the following steps.
To identify the AdBase and BI data sources:
1. Launch Configure AdBase from the Mactive\bin folder. For instructions, see the Configure AdBase

Utility Guide.
The Mactive Configure AdBase window displays. Using Configure AdBase, you will select a data
source for the production and BI databases and then indicate the database type. The remaining
settings are optional and depend on your site’s practices.
2. Click the Select DSN button under the Data Source Name field. This DSN will point to the AdBase
schema.
The Select Data Source window displays.

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

5

3. Click the Machine Data Source tab.

4. Highlight the desired Data Source Name for the core AdBase schema and click OK .

If your site uses a trusted connection, you will not be prompted for a User name and Password.
However, if you are prompted for your login information, enter your database User name and
Password. For more information, see your System Administrator. The data source name, user ID,
workstation ID, database name, and other flags are displayed in the Data Source Name field.
5. Select the Database Type. Your options include MS Access, MS SQL Server, and Oracle.
6. Select Business Intelligence from the Business Intelligence list. This DSN will point to the BI reports
schema.
7. Click the Select DSN button in the Business Intelligence box.

The Select ODBC DSN window displays:

8. Click Select to locate and select the BI data source.
9. Select the Database Type and click Apply .
10. Click Apply and then click Done .

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

6

Create BI Database Tables
After creating the ODBC connections and identifying the data sources in Configure AdBase, you will
create the BI database tables using the Database Table Utility. The following instructions explain how to
log in to the utility, create tablespaces for Oracle® databases, and create the BI database tables. For
more information on the options available, see the Database Table Utility Guide.

Log in to the Database Table Utility
You will find the Database Table Utility in the Microsoft® Windows Start menu or the Mactive\bin folder.
To log in to the Database Table Utility:
1. Select Start > Programs > Atex Applications > Database Table Utility .

The Login window displays:

2. Enter your User name and Password and click OK .

The Mactive Database Table Utility window displays.
3. Click the expand button (+ ) next to Database DLLs to view all of the database functions.

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

7

4. Select the BIDLLFunctions.dll.

The BIDLLFunctions.dll node is highlighted in blue when selected. This node must be selected when
performing functions on the BI database. If this node is not selected, you could cause serious damage to
your production database. By keeping the BIDLLFunctions.dll node selected, you are telling the Database
Table Utility to make changes to the BI database only.

Create tablespaces and ADBASE_BI user for Oracle® databases
When you create a new database in Oracle, you must also create two tablespaces to house the BI data
and the BI indexes. Use the following names to identify these tablespaces.



Main tablespace – ADBASE_BI
Index tablespace – ADBASE_BI_IDX

These tablespaces can be created using the following create-tablespace scripts. Change the path name
to the appropriate UNIX path name for the data files for the tablespaces.
CREATE SMALLFILE TABLESPACE "ADBASE_BI" DATAFILE
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\SERV\adbase_bi01.dbf' SIZE 204864K AUTOEXTEND ON
NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL UNIFORM SIZE 5M SEGMENT
SPACE MANAGEMENT AUTO;
CREATE SMALLFILE TABLESPACE "ADBASE_BI_IDX" DATAFILE
'C:\ORACLE\PRODUCT\10.2.0\ORADATA\SERV\adbase_bi_idx01.dbf' SIZE 204864K AUTOEXTEND ON
NEXT 100M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL UNIFORM SIZE 5M SEGMENT
SPACE MANAGEMENT AUTO;

To create the ADBASE_BI database user, use this script.

CREATE USER "ADBASE_BI" PROFILE "DEFAULT" IDENTIFIED BY
"ADBASE_BI" DEFAULT TABLESPACE "ADBASE_BI" TEMPORARY TABLESPACE
"TEMP" ACCOUNT UNLOCK;
GRANT "CONNECT" TO "ADBASE_BI";
GRANT "DBA" TO "ADBASE_BI";
The following instructions explain how to configure the tablespaces using Database Table Utility.

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

8

NOTE: The following instructions are for Oracle® databases only. If you are running a Microsoft® SQL
Server database, you do not need to complete the following steps. If you are unsure which type of
database you are running, see your System Administrator
To associate tablespaces created by Oracle Enterprise Manager:
1. Right-click on the BIDLLFunctions.dll and select Table Tablespace .

The Table Tablespace Names window displays:

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

9

2. Enter ADBASE_BI as the Tablespace Name.
3. Make sure the value of the Initial Extent and Next Extent fields are both 5120 K.
4. Enter 1 in the Min Extents fields.
5. Select UniformSize as the Future Extents option.
6. Select UseExtents as the Table Size On Rebuild option.

The following window illustrates how the Table Tablespace Names window should appear.

7. Click Apply to write the settings to the tablespacenames table in AdBase.
8. Right-click on the BIDLLFunctions.dll and select Index Tablespace .

The Index Tablespace Names window displays.
9. Enter ADBASE_BI_IDX as the Tablespace Name.
10. Repeat steps 3-7 for the index tablespace.

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

10

Create a blank BI database
The following steps explain how to create the empty BI database tables using Database Table Utility.
To create the blank BI database:
1. Click on the BIDLLFunctions.dll node. Make sure the BIDLLFunctions.dll node is highlighted in blue.
2. Click the Make Tables button.
3. Click the Add Constraints button.

You now have a new, empty BI database. However, you must initialize the database before you can
populate the database with information.

Initialize BI
Once you have created the BI tables and added the constraints successfully in the Database Table Utility,
you will run the BI Populator to insert information in the BI database tables. This step is referred to as
initial processing because you are populating the BI database.
To initialize the BI database, you will use the BI Populator. The BI Populator, which is located in the
Mactive\bin folder, is controlled by the Run Utilities privilege. You must have this privilege to login to
the Populator. If you have any questions about your privileges, see your System Administrator.
Before beginning the initial population of the BI database, you must first prepare the server that is to
hold the BI database. Do not use the BI Importer to populate the BI database. The BI Importer should
not be run until after Initial Processing and the Initial Load of BI data is complete.
NOTE: Make sure the BI Feeder Table Processing box is not checked on the Other Settings tab in System
Admin. This box should not be checked until after the Initial Processing is complete. Before proceeding,
verify the BI Feeder Table Processing box is not checked.
To complete the initial processing of the BI database:
1. Double-click the BIPopulator.exe icon in the Mactive\bin folder.
2. The Login window displays.
3. Enter your User name and Password and click OK .
4. The BI Populator window displays.

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

11

5. Click the Miscellaneous tab.

6. Enter the appropriate Start Date and End Date and click Populate Both .

To make sure all of the information in the core database is populated to the BI database, the date
range must include any dates appearing in your database. For example, if your database contains
orders from 1999, enter a Start Date of at least 01/01/1999. The end date is not as important
because it is easier to update later. However, to be safe, you might enter and End Date of
01/01/2015.
You also have the option to Populate Date Only and Populate Null Rows Only. The Populate Date
Only button can be used with the Clear Existing Dates option to rewrite information to the dmDate
table in the BI database. When clicked, the Populate Null Rows Only button creates the null rows in
all of the BI tables. The BI database uses the null rows to link tables with foreign keys. Both of these
actions are accomplished when you click the Populate Both button.
7. Click the Exit button to close the BI Populator.

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

12

Import Data into BI
The purpose of the BI importer is to directly populate the BI tables with historical data from an external
system. The data may consist of both ad order and revenue information. Imported records are
segmented into three different types: ad orders, insertion records, and GL records (i.e., invoices, credits,
debits, and payments). Each record type is defined in a unique file and each imported record will have a
list of required fields and an assigned length for each field. There is no known import file size limit.
This document explains how to run the BI Importer for ad order, insertion, and GL records as well as the
field formats for import files. Before importing BI information, at a minimum, the BI tables must be
populated with customer account data (dmClient) and other data referenced by the importer (dmUser
and dmAdOrderBools, etc.).
Prior to running the importer, make sure AdBase is pointed to the right BI database. You must run
Configure AdBase and select the Business Intelligence ODBC Connection. For more information, see
page 5.
The BI Importer is located in the Mactive/bin folder. There are no field translations required for this
import. The import may be run from multiple workstations simultaneously. Do not use the BI Importer
to populate the BI database. The BI Importer should not be run until after Initial Processing and the
Initial Load of BI data is complete.
To import data into the BI database:
1. Double-click the BIImporter.exe icon.

The Login window displays.
2. Enter your User name and Password and click OK .
The BI Importer window displays:

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

13

3. Click the Select File button to select the input file containing records to be imported.
4. Click the Select Path button to select the path and folder in which the importer will save the input
5.
6.

7.

8.
9.
10.

file once the import is complete.
Select the Input File Type. Your options include Ad Order (see page 14), Insertion (see page 17), and
GL (see page 21).
Enter an Ad order prefix to define leading characters for imported ad order numbers. For example, if
you enter L in the Ad order prefix field, and if the imported ad order number is 123456, the number
will be saved in the BI database as L123456.
Check the Ignore matching ad orders box to bypass an ad order record that has a matching ad order
number in the BI database. If this box is not checked and an imported ad order record matches an
existing number in the BI database, the imported record replaces the data in the BI database.
Check the Add entry dmAdProduct table box.
Click Import .
Click Exit to close the BI Importer.

The status bar indicates the progress of the import. The results are written to the Messages window.
When the import is complete, the results are also written to the BIImporter.txt file in the Temp
Directory specified in Configure AdBase. For more information, see page 23.

Importing Ad Order Records
Ad order records require a customer account number field. This field must exist in the dmClient table. If
the account number does not exist, the record will be rejected. When importing ad order records, the BI
Importer checks for a unique ad order number. If the Ignore matching ad orders box is checked, any ad
order number that already exists in the database will be rejected. If the Ignore matching ad orders box is
not checked, and the BI Importer finds a matching ad order, the ad order data is updated and all
insertions previously defined for the replaced ad order are deleted. New insertion records must be
imported (see page 17).
An ad order record may be re-imported if missing accounts have been added. If the Ignore matching ad
orders box is checked, all previously imported ad orders are ignored. Ad orders not imported on the
previous pass are imported if the matching account numbers exist.
The primary function of this import is to populate entries in the fctAdOrder table. The following table
lists the import fields. When importing ad order records, the following rules apply.





All date definitions must be formatted using 10 characters (e.g., 01/05/2004).
All alphanumeric fields are left-aligned.
All numeric fields are right-aligned.
All error and warning messages written to the log include the ad order number and error/warning
information (e.g., Sales Rep not found).

TABLE 1. AD ORDER RECORD FIELD DETAILS

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

14

Field #/
Position

Import Field

Length

BI Table Name

Miscellaneous
Information

1/1

BI_ADORDER_ORDERERACCOUNT

18

Link to dmClient
PrimaryOrderer_Client_ID
AccountNumber_Adbase

Required
Test to match account
number. Number must
exist.

2/19

BI_ADORDER_NUMBER

10

AdOrderNumber

Required
Test match to
AdOrderNumber.
Number must NOT
exist.

3/29

BI_ADORDER_CREATEDATE

10

Link to dmDate
OrderTaken_Date_ID

Date order taken in
mm/dd/yyyy format.

4/39

BI_ADORDER_FIRSTINSERTDATE

10

Link to dmDate
FirstInsert_Date_ID

Required
Date of first insert in
mm/dd/yyyy format.

5/49

BI_ADORDER_LASTINSERTDATE

10

Link to dmDate
LastInsert_Date_ID

Required
Date of last insert in
mm/dd/yyyy format.

6/59

BI_ADORDER_TOTALINSERTIONS

3

TotalInsertions

Required

7/62

BI_ADORDER_TAKER

20

Link to dmUser
OrderTaker_User_ID
UserLoginName

Login name. If no
match, then leave
blank.

8/82

BI_ADORDER_COMMISSIONEDREP

20

Link to dmUser
CommissionedRep_User_ID
UserLoginName

Required
Login name. If no
match, then leave
blank, and write a
warning message to the
log.

9/102

BI_ADORDER_COMPANY

48

Link to dmCompany
Company_ID
CompanyName

If not defined, the first
company listed is used
by default.

10/150

BI_ADORDER_PONUMBER

25

PONumber

Purchase order number

11/175

BI_ADORDER_TOTALADAMOUNT

18

TotalAdAmount
(Numeric field)

Required
Amount is defined in
dollars and cents, using
no punctuation (e.g.,
$1234.56 is defined as
123456).
A negative value is
defined as
-123456.

12/193

BI_ADORDER_TOTALDISCOUNTAMOUNT

10

TotalDiscountAmount
(Numeric field)

Format as in field 11
If blank, then zero

13/203

BI_ADORDER_TOTALPREMIUMAMOUNT

10

TotalPremiumAmount
(Numeric field)

Format same as field
11. If blank, then zero.

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

15

Field #/
Position

Import Field

Length

BI Table Name

Miscellaneous
Information

14/213

BI_ADORDER_AGYCOMMISSIONAMOUNT

10

TotalAgyCommissionAmount
(Numeric field)

Format same as field
11. If blank, then zero.

15/223

BI_ADORDER_TOTALMATERIALAMOUNT

10

TotalMaterialAmount
(Numeric field)

Format same as field
11. If blank, then zero.

16/233

BI_ADORDER_TOTALCOLORAMOUNT

10

TotalColorAmount
(Numeric field)

Format same as field
11. If blank, then zero.

17/243

BI_ADORDER_TOTALTAXAMOUNT

10

TotalTaxAmount
(Numeric field)

Format same as field
11. If blank, then zero.

18/253

BI_ADORDER_TOTALCREDITDEBITAMOUNT

10

TotalCreditDeditAmount
(Numeric field)

Format same as field
11. If blank, then zero.

19/263

BI_ADORDER_TOTALTYPOGRAPHICALAMOU 10
NT

TotalTypographical Amount
(Numeric field)

Format same as field
11. If blank, then zero.

20/273

BI_ADORDER_TOTALNETAMOUNT

18

TotalNetAmount
(Numeric field)

Required
Format same as field
11.

21/291

BI_ADORDER_TOTALPREPRINTAMOUNT

10

TotalPreprintAmount
(Numeric field)

Format same as field
11.

22/301

BI_ADORDER_PAYORACCOUNT

18

Link to dmClient
PrimaryPayer_Client_ID
AccountNumber_Adbase

Test to match account
number. If number
does not exist, then
leave blank.

23/319

BI_ADORDER_DONOTBILLFLAG

1

Link to dmAdOrderBools
AdOrderBools_ID

Do Not Bill Flag
If blank, then F.
T = true, F = false

24/320

BI_ADORDER_DONOTPRODUCEFLAG

1

Link to dmAdOrderBools
AdOrderBools_ID

Do Not Produce Flag
If blank, then F.
T = true, F = false

25/321

BI_ADORDER_DONOTPAGINATEFLAG

1

Link to dmAdOrderBools
AdOrderBools_ID

Do Not Paginate Flag
If blank, then F.
T = true, F = false

26/322

BI_ADORDER_INVOICEDALREADYFLAG

1

Link to dmAdOrderBools
AdOrderBools_ID

Invoiced Already Flag
If blank, then F.
T = true, F = false

27/323

BI_ADORDER_PROMOTIONNAME

20

Link to dmAdPromotion

Promotion Name

The Total Ad Amount, Total Net Amount, and Total Preprint Amount fields are imported as follows.
Total Ad Amount  fctAdOrder.totaladamount

The Total Ad Amount is equal to the sum of all the ad insert charges (i.e., charges with a CategoryCode =
0, CategorySubCode = 1). These charges do not include color, typography, or any other charges that do
not meet these specifications.
newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

16

Total Net Amount  fctAdOrder.totalnetamount

The Total Preprint Amount is equal to the sum of all the preprint insertion charges.
Total Preprint Amount  fctAdOrder.totalpreprintamount

The Total Net Amount is the sum of the Total Ad Amount (or Total Preprint Amount) and all other
charges (discount, premiums, color, etc).
The Total Ad Amount is required in the Ad Order Import specifications. If you have a preprint ad, you can
put a 0 in the Total Ad Amount field.

Importing Insertion Records
When importing insertion records, the ad order number must already exist in the BI tables. In other
words, the ad order record must have been previously imported. If the BI Importer finds an insertion
record that does not have a matching ad order number in the database, the insertion record is rejected.
Insertion records are dependent on matching ad order numbers to the dmAdProduct table in seven
fields, including: product, ad type, edition, zone, placement, position, and category. If a match is found,
then the date field is checked. If a matching date is found, the insertion record is replaced with the
imported data, and the fulfillment amount is replaced. If no matching date is found, the defined
insertion entry is added.
The Add entry to dmAdProduct table option is used for insertion records that do not have a match in
the dmAdProduct table. If a match is not found, and if the box is checked, the insertion record is added
and a new entry is written to the dmAdProduct table. All remaining fields not defined in dmAdProduct
(e.g., ProductDescription) are left blank. This process allows legacy imports that do not have core-based
definitions to be imported. If a match is not found and the Add entry to dmAdProduct table box is not
checked, the insert record is rejected.
The primary function of this import is to populate entries in the fctInsertion and
fctInsertChargeSummary tables. The following table lists the import fields.
TABLE 2. INSERTION RECORD FIELD DETAILS
Field #/
Position

Import Field

Length BI Table Name

Miscellaneous Information

1/1

BI_INSERTION_ADORDERNUMBER

10

AdOrderNumber

Test match to Ad Order
Number. Number must exist.

2/11

BI_INSERTION_INVOICENUMBER

18

InvoiceNumber in
fctInsertChargeSummary

Invoice Number

3/29

BI_INSERTION_PRODUCT

60

Link to dmAdProduct
AdProduct_ID

Product Name (See Note 1)

4/89

BI_INSERTION_ADTYPE

30

Link to dmAdProduct
AdProuct_ID

Ad Type (See Note 1)

5/119

BI_INSERTION_EDITION

20

Link to dmAdProduct
AdProduct_ID

Edition (See Note 1)

6/139

BI_INSERTION_ZONE

20

Link to dmAdProduct
AdProduct_ID

Zone (See Note 1)

7/159

BI_INSERTION_PLACEMENT

20

Link to dmAdProduct
AdProduct_ID

Placement Name (See Note 1)

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

17

Field #/
Position

Import Field

Length BI Table Name

Miscellaneous Information

8/179

BI_INSERTION_POSITION

20

Link to dmAdProduct
AdProduct_ID

Position Name (See Note 1)

9/199

BI_INSERTION_DATE

10

Link to dmDate
Insert_Date_ID

Calendar Date in mm/dd/yyyy
format.

10/209

BI_INSERTION_PRODUCTCATEGORY

20

Link to dmAdProduct
AdProduct_ID

Product Category: Classified,
ROP, Preprint. (See Note 1)

11/229

BI_INSERTION_SORTTEXT

40

Link to dmAdDetail
AdDetail_ID

Sort Text (See Note 2)

12/269

BI_INSERTION_COLORCOUNT

10

Link to dmAdDetail
AdDetail_ID

Color Count (See Note 2)

13/279

BI_INSERTION_COLORNAME

12

Link to dmAdDetail
AdDetail_ID

Color Name (See Note 2)

14/291

BI_INSERTION_COLORTYPE

20

Link to dmAdDetail
AdDetail_ID

Color Type (See Note 2)

15/311

BI_INSERTION_INSTANCENAME

48

Link to dmContractDetails

Instance Name

16/359

BI_INSERTION_FULFILLMENTAMT

10

Link to dmContractDetails to
FctContractFulfillment

AppliedFulfillmentUnits1

17/369

BI_INSERTION_NUMCOLUMNS

7

AdWidth
Column count (and fraction)

Converted to twips
Assume 3 decimal positions
6 columns = 06000
2 ½ = 02500

18/376

BI_INSERTION_ADDEPTH

7

AdDepth
Inches (and fraction)

Converted to twips
Assume 3 decimal positions
10 inches = 10000
2 ¼ = 02250

19/383

BI_INSERTION_JOINTADNUMBER

10

JointAdNumber

This in the ad number that this
insert is placed into

20/393

BI_INSERTION_NUMTEARSHEETCOUNT

3

NumberOfTearSheets

Number of Tear Sheets

21/396

BI_INSERTION_BASECHARGE

10

Link to
FctInsertChargeSummary

Amount_InsertSummary
AdInsertCharge (See Note 3)

22/406

BI_INSERTION_COLORCHARGE

10

Link to
FctInsertChargeSummary

Amount_InsertSummary
Color Item (See Note 3)

23/416

BI_INSERTION_TYPOGRAPHICALCHARGE 10

Link to
FctInsertChargeSummary

Amount_InsertSummary
Typographical Item (See Note
3)

24/426

BI_INSERTION_MATERIALCHARGE

10

Link to
FctInsertChargeSummary

Amount_InsertSummary
Material Item (See Note 3)

25/436

BI_INSERTION_AGENCYDISCOUNT

10

Link to
FctInsertChargeSummary

Amount_InsertSummary
Discount (See Note 3)

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

18

Field #/
Position

Import Field

Length BI Table Name

Miscellaneous Information

26/446

BI_INSERTION_MAKEGOOD

10

Link to
FctInsertChargeSummary

Amount_InsertSummary
Credit (See Note 3)

27/456

BI_INSERTION_GENERALDISCOUNT

10

Link to
FctInsertChargeSummary

Amount_InsertSummary
Discount (See Note 3)

28/466

BI_INSERTION_GENERALPREMIUM

10

Link to
FctInsertChargeSummary

Amount_InsertSummary
Premium (See Note 3)

29/476

BI_INSERTION_PAGENUMBER

3

Link to dmLayoutInfo_ID

PageNumber (See Note 4)

30/479

BI_INSERTION_SECTIONNUMBER

20

Link to dmLayoutInfo_ID

Section (See Note 4)

31/499

BI_INSERTION_COUPONFLAG

1

Link to AdInsertBools

IsCouponAd_Flag
Blank=F, T=True (See Note 5)

32/500

BI_INSERTION_OVERRIDEFLAG

1

Link to AdInsertBools

RateOverride_Flag
Blank=F, T=True (See Note 5)

33/501

BI_INSERTION_DOUBLETRUCK

1

Link to AdInsertBools

IsDoubleTruck_Flag
Blank=F, T=True (See Note 5)

34/502

BI_INSERTION_REVERSE

1

Link to AdInsertBools

IsReverse_Flag
Blank=F, T=True (See Note 5)

35/503

BI_INSERTION_INVOICED

1

Link to AdInsertBools

IsInvoicedAlready_Flag
Blank=F, T=True (See Note 5)

36/504

BI_INSERTION_SCHEDTYPE

1

N/A

Indicates print ad, preprint,
internet, or broadcast

37/505

BI_INSERTION_ONLINE_PRODUCT

1

Link to AdInsertBools

OnLineProduct_Flag
Blank=F T=True (See Note 5)

38/506

BI_INSERTION_COLOR_PREPRINT

20

Link to dmAdContent

Link to AoPreprintColors

39/526

BI_INSERTION_PAPER_PREPRINT

20

Link to dmAdContent

Link to AoPreprintPaperTypes

40/546

BI_INSERTION_SIDES_PREPRINT

3

Link to dmAdContent

From AoPreprintInfo.Sides

41/549

BI_INSERTION_PAGECOUNT_PREPRINT

3

Link to dmAdContent

AoPreprintInfo.PageCount

42/552

BI_INSERTION_WEIGHT_PREPRINT

3

Link to dmAdContent

From AoPreprintInfo.Weight

43/555

BI_INSERTION_LOCATION_PREPRINT

20

Link to dmAdContent

Link to
ShPreprintLocationName

44/575

BI_INSERTION_SCHEDATTRIBUTENAME

20

Link to dmAdDetail

Run schedule attribute

45/595

BI_INSERTION_REVENUE

10

Link to fctInsertion

Revenue for the insertion

46/605

BI_INSERTION_CAMPAIGNNAME

30

dmDigitalMediaCampaign

Internet campaign name

47/635

BI_INSERTION_CAMPAIGNTYPE

40

dmDigitalMediaCampaign

Campaign type

48/675

BI_INSERTION_CAMPAIGNCAT

40

dmDigitalMediaCampaign

Campaign category

49/715

BI_INSERTION_FLIGHTGROUPNAME

20

Link to dmDigitalMediaFlight

Flight group name

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

19

Field #/
Position

Import Field

Length BI Table Name

Miscellaneous Information

50/735

BI_INSERTION_SITE

60

Link to dmDigitalMediaFlight

Internet site

51/795

BI_INSERTION_SECTION

60

Link to dmDigitalMediaFlight

Internet section

52/855

BI_INSERTION_PAGE

60

Link to dmDigitalMediaFlight

Internet page

53/915

BI_INSERTION_FLIGHTSTARTDATE

10

Link to dmDigitalMediaFlight

Start date

54/925

BI_INSERTION_FLIGHTENDDATE

10

Link to dmDigitalMediaFlight

End date

55/935

BI_INSERTION_QUATITYREQUESTED

10

Link to dmDigitalMediaFlight

Quantity requested

56/945

BI_INSERTION_UNITNAME

40

Link to dmDigitalMediaUnit

Unit name

57/985

BI_INSERTION_WIDTH

10

Link to dmDigitalMediaUnit

Width

58/995

BI_INSERTION_HEIGHT

10

Link to dmDigitalMediaUnit

Height

59/1005

BI_INSERTION_QUANTITYDELIVERED

10

Link to fctInsertion

Quantity delivered

60/1015

BI_INSERTION_SPOTCOUNT

10

Link to fctInsertion

Spot count

61/1025

BI_INSERTION_SPOTCOUNTRAN

10

Link to fctInsertion

Spot count ran

62/1035

BI_INSERTION_SPOTTYPE

20

Link to dmBcSpot

Spot type

63/1055

BI_INSERTION_SPOTGROUP

20

Link to dmBcSpot

Spot group

64/1075

BI_INSERTION_DURATION

20

Link to dmBcSpot

Duration

65/1095

BI_INSERTION_CHANNEL

20

Link to dmBcChannel

Channel name

66/1115

BI_INSERTION_CHANNELIDENT

20

Link to dmBcChannel

Channel identifier

67/1135

BI_INSERTION_PROGRAM

20

Link to dmBcChannel

Broadcast program

68/1155

BI_INSERTION_DAYPART

20

Link to dmBcChannel

Broadcast day part

69/1175

BI_INSERTION_BCPOSITION

20

Link to dmBcChannel

Broadcast position

70/1195

BI_INSERTION_BCZONE

20

Link to dmBcChannel

Broadcast zone

NOTE 1: Fields 3, 4, 5, 6, 7, 8, and 10 look for a matching entry in the dmAdProduct table. When a match
is found, the index is placed in AdProduct_ID. If a match is not found, the record is ignored if the Add
entry to dmAdProduct table box is not checked.
NOTE 2: Fields 11, 12, 13, and 14 are the only fields defined in the dmAdDetail table. All other fields are
blank.
NOTE 3: For fields 21 through 28, for each field defined, a row is created in the fctInsertChargeSummary
table. The ChargeCategory field is defined by the charge definition (e.g., color item, material item, etc).
The Amount_InsertSummary is the amount in the field. The ChargeType is either charge or discount. The
Posting_Date_ID is the date field (field number 8). The invoice number (field number 2) is placed in the
InvoiceNumber field for all entries. The Special_ChargeCategory field is blank.
NOTE 4: Fields 29 and 30 are the only fields defined in dmLayoutInfo. All other fields are blank.
newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

20

NOTE 5: Fields 31 through 37 are the only fields defined in dmAdInsertBools. All other fields in this table
default to false.

Importing GL Records
An invoice record must be defined before a payment or credit record is defined. Invoice imports are only
for fully paid (closed) invoices. Manual invoice imports are allowed since there maybe no matching ad
order number. A typical credit or payment record contains the invoice number field. This is the
transaction number from the invoice (I) record. A credit or payment may be applied to more than one
invoice.
The primary function of this import is to populate entries in the fctARSummary, fctGL, and fctApply (if
payment or credit) tables. This file defines records for invoice, credit, debit, and payment records. The
following table lists the import fields.
TABLE 3. G/L RECORD FIELD DETAILS
Field #/
Position

Import Field

Length

BI Table Name

Miscellaneous Information

1/1

BI_ARSUMMARY_TRANSTYPE

1

dmGLTransaction

I = invoice
C = credit
D = debit
P = payment
(Required)

2/2

BI_ARSUMMARY_ACCOUNTNUMBER 18

Link to dmClient
AROrderer_Client_ID and
ARPayer_Client_ID
AccountNumber_Adbase

Test to match account numbers.
Number must exist.
Order and Payer Account
Numbers (one field used for both
entries) (Required)

3/20

BI_ARSUMMARY_TRANSNUMBER

18

TransactionNumber
Test match to
For I record, this is the invoice TransactionNumber.
number
Number must NOT exist.
(Required)

4/38

BI_ARSUMMARY_INVOICENUMBER

18

fctApply

If a credit or payment, then
invoice number is placed here.
(See Note 1)

5/56

BI_ARSUMMARY_TRANSDATE

10

Link to dmDate
Realize_Date_ID

CalendarDate. Transaction date in
mm/dd/ccyy format.
(Required)

6/66

BI_ARSUMMARY_AMOUNT

10

TotalAmountApplied and
Amount_ARSummary

(Required)

7/76

BI_ARSUMMARY_GLACCOUNT

80

Link to dmGLAccounts from
GLTrans_ID

GLNumber
If no match, reject record.
(Required)

8/156

BI_ARSUMMARY_COMMISSIONEDRE 20
P

Link to dmUser
CommissionedRep_User_ID
UserLoginName

Login Name. If no match, then
leave blank, and write a warning
message in log.

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

21

Field #/
Position

Import Field

9/176

Length

BI Table Name

Miscellaneous Information

BI_ARSUMMARY_STATEMENTNUMB 20
ER

fctARSummary

StatementNumber

10/196

BI_ARSUMMARY_INVOICENOTE

dmGLInvoiceDetail

InvoiceNote

11/246

BI_ARSUMMARY_APPLIEDAMOUNT 10

fctApply. AmountApplied

Used if loading a credit or a
payment. Shows how much of
credit/payment applied to
invoice.

12/256

BI_ARSUMMARY_ADORDERNUMBER 10

fctARSummary.AdOrderNumb
er

13/266

BI_ARSUMMARY_MULTIPLEFLAG

1

N/A

Indicates, for Invoices, if GL
records should be deleted.
(See Note 2)
If blank, then F.
T = true, F = false

14/267

BI_ARSUMMARY_PRODUCT

60

Link to dmAdProduct
AdProuct_ID

ProductName (See Note 3)

15/327

BI_ARSUMMARY_ADTYPE

30

Link to dmAdProduct
AdProuct_ID

AdType (See Note 3)

16/357

BI_ARSUMMARY_EDITION

20

Link to dmAdProduct
AdProuct_ID

Edition (See Note 3)

17/377

BI_ARSUMMARY_ZONE

20

Link to dmAdProduct
AdProuct_ID

Zone (See Note 3)

18/397

BI_ARSUMMARY_PLACEMENT

20

Link to dmAdProduct
AdProuct_ID

Placement (See Note 3)

19/417

BI_ARSUMMARY_POSITION

20

Link to dmAdProduct
AdProuct_ID

Position (See Note 3)

20/437

BI_ARSUMMARY_PRODUCTCATEGO 20
RY

Link to dmAdProduct
AdProuct_ID

ProductCategory (See Note 3)

21/457

BI_ARSUMMARY_NUMCOLUMNS

7

Link to fctGL

Number of Columns

22/464

BI_ARSUMMARY_ADDEPTH

7

Link to fctGL

Ad Depth

50

NOTE 1: Field number 4, if the invoice number does not exist in the fctARSummary table, the import
record is ignored and the record is flagged with an error in the log.
NOTE 2: Field number 13, the Multiple GL Flag is used to indicate that no records in fctGL are to be
deleted for this transaction, but instead add this to the fctGL table. This will allow one transaction to
appear in the input file multiple times; and thus, split it between G/L accounts, if needed.
NOTE 3: Fields 14 though 20 look for a matching entry in the dmAdProduct table. When a match is
found, the ID is placed in fctGL.AdProduct_ID. If a match is not found, the record is ignored if the Add
entry to dmAdProduct table box is not checked.

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

22

Using BI Importer Arguments
The following command line arguments may be used to run BI Importer from the command line prompt.
The arguments are case sensitive so it is important to use In the table, parameters are enclosed to
indicate information that needs to be supplied by the user. When entering the actual parameter (e.g.,
the user name), do not use the <> symbols. For example, to enter the user name and password for a
particular user and schedule an automatic run, you would type the following.
-U username -P password –auto –date 01/01/2006
Argument

Description

-U 

Identifies the user for automatic login. Enter the AdBase User name to login.

-P 

Provides the password for automatic login. Enter the AdBase password to login.

-File 

Imports the file. Enter the file to import.

-Path 

Specifies the path field. Enter the path to that you would like to move the file to.

-Type 

Specifies one of the type options. Your options are AdOrder, Insertion, and GL.

-Prefix 

Specifies the prefix field. Enter the prefix of the ad order.

-Ignore 

Populates the Ignore Matching Ad Order checkbox.

-AddEntry 

Populates the Add Entry to dmAdProduct table checkbox.

-auto 

Runs the BI Importer in automatic mode. This means that the BI Importer executes
automatically without a user having to click the Populate button. Once processing is
complete, the BI Populator will automatically exit. This argument is primarily for use
in Schedule Admin.

-AutoExit 

Reading the Log File
The BI Importer writes a log file to the C:\Temp folder on the local workstation. The BIImporter.txt file
includes the following items.







Start time of program
Rejected records and reasons
Invalid fields and reasons
Correct import count
Error import count
End time of program

Each time the importer runs, a number is appended to the end of the file name. Each day a new file is
created and the old file is saved. Seven days of log files are maintained.

Populate the BI Database (Initial Load)
The BI Populator is the software application that will initially populate the BI database and update the
database with changes that occur in the core database. The BI Populator is able to discern changes on
the AdBase database by checking the last edit date on key tables as well as using a "feeder" table, to
newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

23

which certain AdBase utilities will write records. Therefore, any direct updates, inserts, or deletes to the
AdBase tables via SQL Statements will throw AdBase and BI out of balance. If you feel you need to
execute SQL statements against the AdBase database, please contact Mactive’s Support Services first.
While the information on page 11 explained how to initialize the BI database, this section explains how
to use the BI Populator for manual population. This method allows you to populate specific information
in the database using the Object Id Filters. You will use manual population to perform the initial load of
data into the BI database. When loading information, data must be loaded in the following order.





Customers
Contracts
Orders
GL

NOTE: Make sure the Enable BI Feeder Table Processing box is not checked on the Other Settings tab in
System Admin. This box should not be checked until after the initial load of data is complete.
When populating BI the first time, Mactive recommends creating a copy of your production database on
a test server. This configuration allows you to load all of the legacy data onto the future production BI
system in a static environment. Once you have completed the initial load from this database, change the
settings in Configure Adbase on the BI machine to point to the core production database and the
production BI database you just populated.

Loading Customer Accounts
You must load the customer accounts into the database prior to loading contracts, GL, or orders.
To load customers into the BI database:
1. Obtain the earliest date a customer was created in the AdBase system by running the following SQL

query.
Select min(creationdate) from customer;

2. Log in to the BI Populator. For instructions, see page 11.
3. Check the Process Customers box on the Execution Criteria tab.
4. Check the Use Create Date box and enter the date obtained in step 1.
5. Click the Populate button.

Loading Contracts
After customer accounts have been loaded into the database, you can proceed with loading contracts.
To load contracts into the BI database:
1. Check the Process Contracts box on the Execution Criteria tab.
2. Check the Use Create Date box and enter the same date used to populate the customers.
3. Click Populate .

Loading Orders
After the contracts are loaded successfully, you can load ad order data.
newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

24

To load orders into the BI database:
1. Obtain the earliest date an order was created in the AdBase system by running the following SQL

query.
Select min(createdate) from aoadorder;

2. Check the Process ad orders box on the Execution Criteria tab.
3. Check the Use Create Date box and enter the date obtained in step 1.
4. Click Populate .

Loading GL
When loading GL information, it is very important that you follow the order of transactions precisely.
You must load the different transaction types (transtypes) in a specific manner. The GL load will be run
four times, once for each transaction type. You should load all invoices first, then debits, then payments,
and finally credits.
To isolate a transaction type and ignore others (i.e., only run the Populator for invoices), you can set the
range for debits, credits, and payments to be 0 to 0 on the Object Id Filters tab. Running the Populator
in this configuration will resolve the following problems.
Credits and payments exist in the system to pay off debits and invoices. In order to pay them off, you
must apply the credit or payment. You cannot apply against an invoice that has not been created yet.
For example, suppose you accept a payment on 1/1/2003 but leave the payment unapplied. On
5/5/2004, the customer runs an order and on 5/7/2004 the Balance Utility auto applies the payment to
the order from 5/5. When you load the 1/1 payment, it has a apply entry to pay off the 5/6 invoice, but
that invoice might not exist yet in BI when doing the initial load. For this reason, you must load debits
and invoices first.
You may receive the following error message when populating BI.
"An error occurred while processing credit id: 55314 (see sql log)
Error: fctApply not stored, no record for InvoiceId 43864
An error occurred while processing credit id: 55314 (see sql log)"

This error message is referring to a credit created in AdBase that is being loaded into the BI database. In
AdBase, this credit is associated with an invoice. When you try to load this credit into BI, the BI
Populator first inserts a record into fctARSummary, and then the Populator looks for the invoice record
associated with the credit in the fctARSummary table. If it does not find this invoice record, you will get
the error stated above.

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

25

To load GL information into the BI database:
1. Check the Process GL info box on the Execution Criteria tab.
2. Check the Use transaction id range box for Debits, the Use payment id range box for Payments,
and the Use credit id range box for Credits and enter 0 in the Start and End Id fields on the Object Id

Filters tab. The following screen shot shows how the window should look at this point.

TIP: Instead of using the Use transaction id range to exclude transaction types, you can
also check the Get ids from file box for the remaining transaction types, but do not specify
a file.
3. Return to the Execution Criteria tab and check the Use Create Date box and set the date to the

earliest transaction created in the system. To obtain the earliest dates, run the following SQL
queries.


For invoices and debits:

Select min(creationdate) from fntransactions;

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

26



For credits:

Select min(creationdate) from aocustomercd;



For payments:

Select min(creationdate) from aopayments; (for payments)

4. Click Populate .
5. Repeat steps 1-4 for Debits. Remember to enter 0 in the Start and End ranges for Invoices,

Payments, and Credits.
6. Repeat steps 1-4 for Payments. Remember to enter 0 in the Start and End ranges for Invoices,
Debits, and Credits.
7. Repeat steps 1-4 for Credits. Remember to enter 0 in the Start and End ranges for Invoices, Debits,
and Payments.

Audit the Initial BI Load
When all of these processes are complete, the initial data load of BI is finished. Mactive recommends
that you balance between the BI and the AdBase core databases after all the initial data is loaded. To do
this you will use the BI Auditor and the BI AR Auditor. For instructions, see page 40. After the initial loads
have been audited, you can then move on to having BI populated on a regular basis.

Enable BI Feeder Table Processing
In order to setup the workstation for BI, you must check the Enable BI Feeder Table Processing box in
System Admin. The feeder table ensures BI is current with all data in the core database. This table
accounts for tools that do not update the LastEditDate in AdBase tables due to avoid sending data to
PageLayout. An example would be Invoice Generator updating the Invoiced Already flag or Contract
Renew Utility issuing a short rate or rebate. This information should be passed to BI, but is not needed in
PageLayout.
To get around this issue, tools now write a record into a feeder table in the AdBase schema called
shBIInterface. When the Process Data from AdBase Tools box is checked, the BI Populator reads the
shBIInterface table and populates the BI database with any information that tools and utilities might
have changed.
NOTE: The option should not be checked during the initial population of the BI database.
To enable the BI feeder table:
1. Log in to System Admin. See the System Admin User Guide for instructions.
2. Select Tools > System Level Information .

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

27

3. Click the Other Settings tab and check the Enable BI Feeder Table Processing box.

4. Click OK to close the System Info Configuration window.

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

28

Updating the BI Database
Once the BI database is configured and initialized with data, you must decide how you want to perform
updates to the database. You will use two applications to update the data in BI. The BI process pulls
information from two areas in the production database to populate the BI database. The BI Populator
looks at orders, GL information, customers, and contracts that are created or modified by users. The BI
Interface App reads the ShBIInterface table in production and populates BI with data from this table.
These entries come from processes that change data in production, such as Invoice Generator.

Running the BI Populator
The BI Populator offers three options for performing updates. At any time, you can run the BI Populator
manually to update the database for specific orders, customers, and so on. For periodic updates, you
can set up the BI Populator to run at regular intervals using defined criteria. In addition, you can
schedule BI Populator by entering arguments in Schedule Admin.

Updating BI Manually
Using the Execution Criteria options and the Object Id Filters, you can setup the BI Populator to search
for specific ad order, GL, and contract information and populate that information in the BI database.
To run the Populator for the selected information:
1. Log in to the BI Populator. For instructions, see page 11.
2. Select the desired Execution Criteria. For an explanation of each field, see page 31.
3. Select the desired Object Id Filters options and enter the required information. See page 35.
4. Click the Populate button on the Execution Criteria tab.

If you are using one of the Object Id Filters, you will receive a message prompting you to confirm the
action.

If the Populator inserts more than one record, the status bar will indicate the progress of the action.
When the action is complete, the “BI Populator  End” line displays in the Messages box. If the
Populator encounters any errors, you are instructed to see the SQL log.

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

29

Updating BI Periodically
Once you have fully populated the BI database with historical data, you are able to execute the BI
Populator in periodic mode. You will remain logged in to the Populator, which will search for and insert
information based on the interval you enter.
To update the BI database periodically:
1. Log in to the BI Populator.
2. Check the Execute Periodically box and set the interval that BI will check the AdBase database for

changes. Enter the interval in the Auto mode minutes field.
3. Check the Process data from adbase tools box when you are running in periodic mode.
When this box is checked, the BI Populator checks the feeder table, which holds records generated
by the AdBase tools, such as Invoice Generator or Balance Utility. You may also enter a time
constraint, if necessary. For instructions, see page 31.
4. Click the Hide button to minimize the window to the system tray. Right-click the utility icon in the
tray and select Show to view the window.
NOTE: To auto start and continuously run the BI Popoulator, use the -u -p -continuous command
sequence. To auto start, run once, and then shut down the BI Populator, use the -u -p -auto
command sequence.

Scheduling BI Updates
The following command line arguments may be used to run BI Populator from the command line prompt
or schedule BI Populator to run using Schedule Admin. In the table, parameters are enclosed to indicate
information that needs to be supplied by the user. When entering the actual parameter (e.g., the user
name), do not use the <> symbols. For example, to enter the user name and password for a particular
user and schedule an automatic run, you would type the following.
-U username -P password –auto –date 01/01/2006
Argument

Description

-U user name

Identifies the user for automatic login. Enter the AdBase User name to login.

-P password

Provides the password for automatic login. Enter the AdBase password to login.

-auto

Runs the BI Populator in automatic mode. This means that the BI Populator executes
automatically without a user having to click the Populate button. Once processing is complete,
the BI Populator will automatically exit. This argument is primarily for use in Schedule Admin.

-continuous

Runs the BI Populator in automatic mode. This means that the BI Populator executes
automatically without a user having to click the Populate button. Unlike the -auto argument,
once the process is complete, the BI Populator continues to run.

-date start date

Specifies the start date in the absolute date format (mm/dd/yyyy).

-D # days in future

Specifies the start date as the number of days from the current date.

-duration # days

Specifies the end date as the number of days after the start date.

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

30

Argument

Description

-crdate

Specifies selection by the create date, rather than the last edit date. Using this argument is
essentially the same as checking the Use create date box in the BI Populator window. When using
this argument, do not specify a date.
NOTE: If you do not use the crdate or ledate argument in the command line, BI Populator will run
using whichever setting was selected the last time the utility was exited.

-ledate

Specifies selection by the last edit date, rather than the create date. Using this argument is
essentially the same as checking the Use last edit date box in the BI Populator window. When
using this argument, do not specify a date.

-customer

Specifies customer population will be performed.

-customerfile file path

Specifies a file containing a list of customer IDs to be populated. Note that if you use this
argument, you do not have to use the -customer argument. The presence of the -customerfile
argument specifies customer population will be performed.

-contract

Specifies contract population will be performed.

-contractfile file path

Specifies a file containing a list of contract IDs to be populated. Note that if you use this
argument, you do not have to use the -contract argument. The presence of the -contractfile
argument specifies that contract population will be performed.

-gl

Specifies GL population will be performed.

-invoicefile file path

Specifies a file containing a list of invoice IDs to be populated. Note that if you use this argument,
you do not have to use the -gl argument. The presence of the -invoicefile argument specifies that
invoice population will be performed.

-debitfile file path

Specifies a file containing a list of debit IDs to be populated. Note that if you use this argument,
you do not have to use the -gl argument. The presence of the -debitfile argument specifies that
debit population will be performed.

-paymentfile file path

Specifies a file containing a list of payment IDs to be populated. Note that if you use this
argument, you do not have to use the -gl argument. The presence of the -paymentfile argument
specifies that payment population will be performed.

-creditfile file path

Specifies a file containing a list of credit IDs to be populated. Note that if you use this argument,
you do not have to use the -gl argument. The presence of the -creditfile argument specifies that
credit population will be performed.

-adorder

Specifies ad order population will be performed.

-adorderfile file path

Specifies a file containing a list of ad order IDs to be populated. Note that if you use this
argument, you do not have to use the -adorder argument. The presence of the -adorderfile
argument specifies that credit population will be performed.

-leavesettings

If you run two copies of the BI Populator, they interfere with each other’s settings. A copy of the
BI Populator called with this argument will avoid updating the settings, leaving the original copy
of the BI Populator alone.

Using Execution Criteria
Use the Execution Criteria tab to indicate how information should be selected from the core database.
You have the option of populating data based on a date range. When determining the range, you choose
to base the date on either the create date or the last edit date in the database.
Use last edit date
Check this box to search the core database for orders, GL information, and contracts based on the last
date that data was edited by a user. The date shown in the Last Edit/Create Date Info box defaults to
newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

31

today’s date. However, you can select a different date on which to search for data. The following steps
illustrate how to use these options.
To search for ad orders edited on or after to 1/01/2004:
1. Check the Use last edit date box.
2. Check the Process ad orders box.
3. Select 1/01/2004 from the first date field in the Last Edit/Create Date Info.
4. Enter 12:00:00 AM in the time field.

TIP: You are also able to enter a date range to search for the last edit date during a date range. For
information, see page 34.
Use create date
Check this box to search the core database for orders, GL information, and contracts based on the date
the data was entered in the system. Like the Use last edit date option, you are able to use the Use create
date option with the Last Edit/Create Date Info fields to further restrict how the Populator searches for
data.
This option can only be used during initial processing or when the Populator is run manually. You cannot
use this option when running period updates automatically.
Log timing info
Check this box to write debug information to the BiPopulatorLog.txt file located in the C:\Temp folder.
Even when this box is not checked, the Populator writes some cursory information to the log file on any
execution. Use this option when you are troubleshooting a problem, or trying to figure out which phase
of the Populator is taking a long time to complete its task. However, be aware that the log file ends up
growing to an extreme size on a large populate.
NOTE: The location of the BiPopulatorLog.txt file is determined in the Configure AdBase Utility. For more
information, see your System Administrator.
Process ad orders
Check this box to include ad orders in the information populated to the BI database. If you check the Use
last edit date box, the Populator will search for orders based on the last date the orders were edited. If
you check Use create date , the Populator will search for orders based on the date they were originally
saved in the core database. Order based charges are spread proportionally across the insertion days
within an order.
If the order is a Till Forbid (TF), then the process gets a bit more complicated. To spread an order based
charge or credit across a TF order, the BI Populator must first determine if accounting periods are set up
in the fnAccountingPeriods table. If the accounting periods are set up, the utility will use those defined.
If not, the utility will use months as the accounting period. Mactive defines the current accounting
period as that period or month that the credit or order based charges falls into, and the previous
accounting period as the period before the current accounting period.

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

32

Next, BI Populator will determine if the TF order has any run dates in the previous accounting period. If
so, the credit or order based charge is proportionately spread across all the run dates within the
previous accounting period. If there are no run dates in the previous accounting period, then the credit
or order based charge will be spread across all run dates from the beginning of the current accounting
period up to the effective date of the credit or order-based charge.
Process GL info
Check this box to include GL information to be populated to the BI database. When this box is checked,
the Populator searches for all credit, debit, invoice, and payment transactions. Like the Process ad
orders option, you can search for GL information based on the edit date or the create date.
You must use this option, along with the Object Id Filters, during initial processing to populate the BI
database with transaction information. For instructions, see page 25. BI saves invoices, debits,
payments, and credits based on the transaction creation date. This criteria is different than AdBase,
which relies on the transaction date to identify invoices and debits, and effective date to identify
payments and credits.
Process contracts
Check this box to include all contracts and contract fulfillment statistics in the information populated to
the BI database. You can also use the edit date and create date to distinguish how contract information
is selected by the Populator.
Process customers
Check this box to update the dmCustomers table with all new or modified customer records whether
the record has an associated transaction or not. The BI Populator filters the customers based on the last
modified date on the record.

Process data from adbase tools
Check this box when running in Period Execution Mode to check the feeder table for information
generated using the AdBase tools. For more on the feeder table, see page 27. For more information on
running in Period Execution Mode, see page 30.
When you check the Process data from adbase tools box, the Use time constraint option is enabled. By
checking the Use time constraint box, you are able to enter a Start time and End time to limit the
window for processing data generated from the AdBase tools.
Execute periodically
Check this box to setup a time interval during which the Populator will run automatically. Use the up and
down arrows to enter the Auto mode minutes and then click the Set button. For example, if you check
the Execute periodically box and enter 30 in the Auto mode minutes field, the Populator will run
automatically every 30 minutes as long as the application is open on the system.
TIP: To leave the Populator open on the system, but work in other applications, click the Hide button. Use
the utility icon in the System Tray to show the hidden window.

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

33

Last Edit/Create Date Info

Depending on which option you use to search for information, you are able to enter a date range to
restrict the time in which the Populator will find data based on the create date or the last edit date.
To enter a date range:
1. Check the Use last edit date or Use create date box to determine how the Populator should search

for data.
2. Check the Use date range box.
The end date field is enabled.
3. Select the start date and time in the top fields.
4. Select the end date and time in the bottom fields.

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

34

Using the Object ID Filters
In the initial populate mode, you also are able to control what items are loaded into BI using the Object
ID Filters tab. If you enter information on the Object Id Filters tab, that object (i.e., ad order, contract,
etc.) is populated to the BI database using the selected filter. Otherwise, the last edit date or create date
is used, depending on which option you are using.

When you select GL information to populate, the Populator will attempt to insert all of the GL
information associated with the selected transaction type unless you indicate otherwise. If you only
want to populate invoices within a selected range using the Use transaction id range option, you must
point the other GL fields in another direction. In this case, the easiest way to make sure that you only
include invoices in the GL information would be to check the Get ids from file box for debits, payments,
and credits, but do not select a file. The following sections explain how to use these fields. For
instructions on populating GL information during initial processing, see page 25.

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

35

Get IDs from file
This option is available for customers, contracts, invoices, debits, payments, credits, and ad orders. If
you want to populate a bunch of ad order IDs that do not fall within a certain range, you save the IDs in
a text file and populate them to the BI database. When entering information in the file, only enter the ID
numbers. The Populator will not read text and will stop each line when it reaches a space. For example,
to populate ad order IDs 1, 101, and 10101, you could create a file containing the following entries.
1
101
10101

To select the file containing the ad order IDs:
1. Check the Get ad order ids from file box and click the Browse button.

Notice that the field name changes to Ad Order ID File Name.

2. Highlight the desired file and click Open .

The name of the file displays in the Ad Order Id File Name.
This method may be useful when you receive errors after a long execution, allowing you to “clean up”
IDs that were not successfully populated in the BI database.
Use id range
This option allows you to enter a range of database IDs when searching for customers, contracts,
invoices, debits, payments, credits, and ad orders. Do not confuse the database ID with a text field that
is displayed in one of the AdBase GUIs. The ad order ID is different than the ad order number issued in
AdBooker.
To select search for a range of ad order IDs:
1. Check the Use ad order id range box.

The field names change to Start Ad Order Id and End Ad Order Id.

2. Enter the beginning and ending ad order IDs to define the range.

TIP: You can also search for a single ad order ID by entering that number in the Start field and leaving the
End Ad Order Id field empty.

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

36

Use single ad order/transaction number
While the previous option allowed you to search for data by the ID, you are able to use the invoice
number, debit number, and ad order number to find information. Since these numbers are text fields,
you cannot use a range of numbers. For example, you can search for ad order number 0000000010.
To search for a single ad order number:
1. Check the Use single ad order number box.

The field name changes to Single Ad Order Number.

2. Enter the desired ad order, invoice, or debit number. For this example, you would enter

0000000010.
TIP: If you are entering a single transaction number for invoices or debits, enter the entire transaction
number used at your site. Sites use different formats for numbering their invoices and debits, so make
sure to enter the number using the correct format according to your site’s rules.

Running the BI Interface App
In order to use the BI Interface App, you must check the Enable BI Feeder Table Processing box in
System Admin. The feeder table ensures BI is current with all data in the core database. This table
accounts for tools that do not update the LastEditDate in AdBase tables due to not wanting to send
information to PageLayout. An example would be Invoice Generator updating the Invoiced Already flag
or Contract Renew Utility issuing a short rate or rebate. This information should be passed to BI, but is
not needed in PageLayout.
To get around this issue, tools write a record into a feeder table in the AdBase schema called
ShBIInterface. The BI Interface Application then reads the ShBIInterface table and populates the BI
database with any information that tools and utilities might have changed.
Mactive strongly recommends running the BI Interface Application on the same machine that is running
the BI Populator. When using the BI Interface Application, make sure you Process BI Interface data box is
not checked in the BI Populator. This checkbox does the exact same processing as the BI Interface
Application.
The BI Interface Application is located in the Mactive\bin folder. You must be assigned the Run Utilities
privilege to log in to the application. If you have any questions about your privileges, see your System
Administrator.
To run the application:
1. Double-click the BIInterfaceApp.exe icon.

The Login window displays.
2. Enter your User name and Password and click OK .
The BI Interface App window displays.
newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

37

3. Click Populate .

The application begins processing information from the ShBIInterface table:

The line above the status bar indicates how many rows are in the ShBIInterface table. When all of
the rows have been populated in the BI database, the “Populate Complete” message displays above
the status bar.
4. Click Exit to close the application.
The Log timing info checkbox is used to troubleshoot errors that occur when running the BI Interface
Application. When this box is checked, additional information is written for every row in the
ShBIInterface table that is processed. The information is written to the BIInterfaceAppLog.txt file in the
Temp Directory specified in Configure AdBase. However, writing this extra information will slow down
the process, so the Log timing info checkbox should only be used in troubleshooting situations.
You have two options for scheduling the BI Interface Application. You can schedule the BI Interface
Application to run on a regular time interval using the Execute periodically option; or, you can use time
constraints to run the application during certain slow hours.
To schedule the application to run at a regular interval:
1. Check the Execute periodically box.
2. Use the up and down arrows to enter a value in the Auto mode minutes field. For example, if you

enter 30, the application will populate data every 30 minutes.
3. Click Set .
4. Click Populate .
5. Click Hide to minimize the BI Interface App on the server.
To schedule the application to run during specific hours:
1. Check the Use time constraint box.
newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

38

The Start time and End time fields become active.
2. Enter the Start time and End time manually or use the up and down arrows to change the time.
Remember to also adjust the AM to PM, as necessary.
In the following example, the Start time is 2:00 AM and the End time is 3:00 AM. This means that the
BI Interface Application will run every morning between these hours.

3. Click Populate .
4. Click Hide to minimize the BI Interface App on the server.

NOTE: To auto start and continuously run the BI Interface Application, use the -u -p -continuous
command sequence. To auto start, run once, and then shut down the BI Interface Application, use
the -u -p -auto command sequence.

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

39

Balancing BI
This section explains how to balance the BI and AdBase database for orders and AR data. This section
also explains how to use the BI Utility to search for discrepencies in the BI database.

How to Balance Orders in AdBase and BI
This section explains how to find discrepancies between the AdBase database and the BI database for
orders. This process will allow you to audit all orders in the system and resolve all issues to get in
balance.
This process can be run at any time and multiple times to check the database. The process is quick
enough that you can find issues with order balancing if you think you have differences. To summarize,
the process can be completed in four steps.
1. Make sure BI Populator and BI Interface App are current with populating BI.
2. Complete the initial BI query by running BI Auditor in commit mode.

If you do not check the Commit Core Transactions box when running BI Auditor, you must run the
BISQLFILE.txt in SQLPlus and commit changes. If you do NOT Commit Core Transactions OR run it
through the BISQLFILE.txt file in SQLPlus, BI Auditor will continue to report the same issues.
3. Run BIFIXFILE.txt and BIFIXCONTRACTFILE.txt in BI Populator for the found ad orders or contract
instances.
4. Run BI Auditor again and verify results.
The first step is to configure the BI Auditor to point to your production and BI databases. The BI Auditor
is not part of the standard Mactive installation. You must contact Mactive Support Services to request
the executable. When you receive the file, unzip the executable and save it to a location on the PC that
is currently performing BI processing. You may also want to save a backup copy to another location for
sake keeping. Once saved, configure the BI Auditor.
To configure the BI Auditor:
1. Double-click the executable.

The Set Preferences window displays:

2. Enter the AdBase ODBC Connection name, Login name, and Password.
3. Enter the BI ODBC Connection name, Login name, and Password.
newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

40

4. Enter the Path for Log Files, which identifies the location where log files and discrepancy files will be

saved.
5. Click OK .
The BI Auditor window displays:

Once you have configured the BI Auditor to point to the correct datasources, you must complete the
initial query to get the count of orders to process.
NOTE: If you do more than one run in the same day the files will be overwritten. If you want to save the
files, simply rename them before running another audit.
To complete the initial query of the BI Auditor:
1. Enter the Created On or After date and the Created Before date to the appropriate range.
2. Check the Use Run Dates box to find all orders to audit by looking at the run day or days in the order

instead of the order creation date. This box should be checked if your BI reports use the run day.
When the Use Run Dates box is checked, you can verify that all orders running in a given date range
are up to date. When you initially populate the BI database, it is better to remove the check from
the Use Run Dates box and use the creation date instead.
3. Check the Commit Core Transactions box to make changes to the core (production) database. The
changes are logged to the Temp folder in the BISQLFILE.txt file. Keep this file as a record of the
changes made to core.
NOTE: If you do not check the Commit Core Transactions box when running BI Auditor, you
must run the BISQLFILE.txt in SQLPlus and commit changes. If you do NOT Commit Core
Transactions OR run it through the BISQLFILE.txt file in SQLPlus, B I Auditor will continue
to report the same issues.
newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

41

4. Check the Include Adjustments box to verify adjustments linked to orders in core balance with

adjustments linked to orders in BI. If the BI Auditor finds any discrepencies, it will generate a file
containing the ad order IDs.
Mactive does not recommended using this option every time you run the auditor; it will take longer
to run and most sites will not have an issue with this data.
5. Check the Check Fulfillment Records box to verify contract fulfillment in core balances with
contract fulfillment in BI. If the BI Auditor finds any discrepancies, it will generate a file containing
the contract instance IDs.
6. Check the Check Invoiced Already Flag box to verify invoiced insertions in core balances with
invoiced insertions in BI. If the BI Auditor finds any discrepencies, it will generate a file containing
the ad order IDs.
7. Click Balance Orders .
The progress of the audit displays in the line above the Activity Log tab and shows a count of processed
orders, the time it has taken to process the orders, and the time remaining until completion. As issues
are found, the Activity Log will display information about the discrepencies.
When the process is complete, the last entry in the Activity Log provides a total count of orders with
issues. The order IDs are written to a file in the directory indicated in the Set Preferences window. If
you did not change the location, the file is written to the default C:\Temp folder. The name of the file
will be BIFIXFILE20051130.txt or BIFIXCONTRACTFILE20051130.TXT. The date on the file is today’s date.
TIP: You can use the From File button to run the BI Auditor for a specific set of transactions. This feature
should be used if you are running the BI Auditor after populating transactions from a fix file. Use the
same fix file from the first run of the BI Auditor to verify the data has been corrected.

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

42

Once the file is created, the next step is to process the Fix files using BI Populator. Launch a new copy of
BI Populator and leave your current daily BI Populator and BI Interface App processes running.
To process Fix files using BI Populator:
1. Launch the BI Populator. For instructions, see page 11.
2. Check the Process ad orders box on the Execution Criteria tab.

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

43

3. Check the Get ad order ids from file box on the Object ID Filters tab, and click the Browse button to

locate the files generated during the BI Auditor run. Select the file so that the path and file name
display in the field.

4. Click the Populate button on the Execution Criteria tab.

A message similar to the following example displays the active object ide filters and prompts you to
continue:

5. Click OK when you will be notified that the object id filters are active.

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

44

Once the BI Populator has completed processing ad orders, then go back to the BI Auditor and re-run
the process. This time, the BI Auditor should return 0 orders or note unpopulated orders and a few
balance items, which are caused by the live environment where BI and core are constantly changing.
Since the daily BI Populator is running, you will see a disconnect of x minutes between core and BI. Thus,
you should expect the BI Auditor to report a few items; but, not nearly as many as during the initial run.
If the BI Auditor generated a Fix file for contracts, run BI Populator with the following options selected
on the Execution Criteria.

Check the Get contract ids from file box on the Object Id Filters tab, and click the Browse button to
locate the BIFIXCONTRACTFILE generated by the BI Auditor. Select the file so that the path and file name
display in the field.

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

45

TIP: You can run the BI Populator for both Fix files at the same time by checking the Process Ad Orders
box and Process Contracts box and selecting the Fix files on the Object Id Filters tab.
Once you have completed all of the processing, re-run all of the Crystal® Reports. By running the
reports again, you will see the data changes and you can verify that BI and production are now in sync.
Once the reports are re-run, save the data in the report. However, make sure you do a Save As and
name the reports BIRevenuepost.rpt, CoreRevenuepost.rpt, etc.

How to Balance AR in AdBase and BI
The BI AR Auditor tool verifies AR transactions between the AdBase core and the BI databases. This
process can be run at any time and multiple times to check the database. The process is quick enough
that you can find issues with order balancing if you think you have differences. To summarize, the
process can be completed in four steps.
1. Make sure BI Populator and BI Interface App are current with populating BI.
2. Complete the initial query by running BI AR Auditor in commit mode.
3. Run BI Fix File in BI Populator for the found transactions. The files are written to the directory

specified in the Preferences. For example, if discrepancies are found for payments, a
BIPAYMENTFIXFILE20060320.txt file is created.
4. Run BI AR Auditor again and verify results.
The first step is to configure the BI AR Auditor to point to your production and BI databases. The BI AR
Auditor is not part of the standard Mactive installation. You must contact Mactive Support Services to
request the executable. When you receive the file, unzip the executable and save it to a location on the
PC that is currently performing BI processing. You may also want to save a backup copy to another
location for sake keeping. Once saved, configure the BI AR Auditor.
To configure the BI AR Auditor:
1. Double-click the executable.

The Set Preferences window displays:

2. Enter the AdBase ODBC Connection name, Login name, and Password.
3. Enter the BI ODBC Connection name, Login name, and Password.
4. Enter the Path for Log Files, which identifies the location where log files and discrepancy files will be

saved.
newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

46

5. Click OK .

The BI Auditor AR window displays:

NOTE: The current version does not filter out transactions that were imported via BI
Importer. Since the transactions do not exist in core, there is nothing to use for balancing .
In the future, the BI Auditor will be enhanced to ignore these transactions.
To complete the initial query of the BI Auditor:
1. Enter a Created On or After date and a Created Before date to limit the transactions to a date range.
If you do not want to limit the transactions to a date range, check the Ignore Dates box.

2.

3.

4.

5.

For example, you can enter a date range that matches a fiscal period. If you have a lot of
transactions to verify, you should start in the past and audit one period at a time.
Check the boxes next to the transaction types (i.e., Invoices, Debits, Credits, and Payments) you
want to include in the query. For example, if want to limit the number of transactions to audit to
payments, check the Payments box and remove the check from all others.
You do not have to run the BI AR Auditor separately for all transactions. The four checkboxes simply
allow you to restrict processing to one transaction type.
Check the Check Account Info box to verify the GL account is the same in core as it is in BI for each
AR transaction. If someone changes a GL account on a transaction in core, this checkbox will validate
that the same GL account information is changed in BI.
Check the Check Apply Amounts box to verify the applied amounts are the same in BI as they are in
the Core system. Do not check this box if you do not have any BI reports that use the Apply Amount
option.
Check the Commit Fix Transactions box to make changes to the BI database. If you do not check this
box, any discrepencies found will not be corrected in the database.

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

47

Mactive has found issues where transactions were duplicated in BI. In these cases, the transactions
need to be deleted from BI. If you check the Commit Fix Transactions box, the duplicate
transactions will be deleted. The next time you run the application, it should not find the same
issues. In the window, the message will read “Resolve issue of debit populating as credit.”
6. Click the Balance AR button to audit all of the transaction types that are selected in the window for
the given date range.
As the BI AR Auditor finds discrepancies, they are listed in the window. For each transaction type, two
files are written to the directory specified in the INI file. For example, if discrepancies are found for
payments, the following two files are written.



BIPAYMENTFIXFILE20060320.txt – This file contains IDs for any transactions that need to be repopulated in BI Populator.
BIPAYMENTExceptionFile20060320.txt – This file contains a row for each transaction that the
application finds. You can open this file in Microsoft® Excel and it will show what value this
transaction has in core and the value it currently is in BI. This file provides more information on what
it wants to correct.

The BI AR Auditor might also produce a file named CORECREDITGLFIXFILE20060619.txt. This file is
produced when BI AR Auditor finds GL issues on the core side. This file must be run through GL
Remapper on the core database. For instructions, see the Money Manager User Guide.

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

48

After the BI AR Auditor has completed the transactions, run the BIPAYMENTFIXFILE20060320.txt file in
BI Populator using the following steps.
To process Fix files using BI Populator:
1. Launch the BI Populator. For instructions, see page 11.

The BI Populator window displays.
2. Check the Process GL info box on the Execution Criteria tab.

3. Click the Object Id Filters tab.

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

49

4. Check the box or boxes to retrieve the file for the appropriate transaction type. For example, to

process a file for payments, check the Get payment ids from file box as shown in the following
screen shot.

You can run all files at the same time if you want, or do them one at a time.
5. Click the Browse button to locate the file generated by the BI AR Auditor. Select the file so that the
path and file name display in the field.
6. Click the Populate button on the Execution Criteria tab.
After all files have been run through BI Populator, run Balance AR again in BI Auditor and the same
period should not show any errors.

Checking Balances
The BI AR Auditor also contains a Check Balances button, which checks the database for discrepencies,
but does not update any records or create any files. When you click Check Balances, the BI AR Auditor
displays a sum of each transaction type and a sum for GL by transaction type. The results can be
compared to the Balance Forward Report for the same date range. You must run the Balance Forward
newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

50

Report to include bad debt payments and credits. See the Mactive Standard Reports User Guide for
instructions.
The Check Balances button also indicates if you have any rows in the ShBiInterface table, since this will
affect your AR transactions. For the best results, you should run BI Auditor when the interface table is
empty (0 rows).
If you check the Log SQL Transactions to Screen box when you use the Check Balances button, the BI
Auditor will show the actual queries it performs to compare the total sum on the SQL Log tab.

Using the BI Utility
The BI Utility is designed to find and resolve any discrepancies between the AdBase and BI databases.
The BI Utility will also help you determine if these issues are occurring regularly. The BI Utility has three
functions. First, the BI Utility locates unpopulated items, including customers, contracts, invoices, debits,
payments, credits, and ad orders. Second, the utility checks the balance of ad orders in BI. Third, the
utility allows you to view dmUser entry(s) for each AdBase user. To accomplish each of these tasks, the
BI Utility compares information in the AdBase database to the BI database to determine what
information is missing from the BI database.
A record of processes completed in the BI Utility is written to the BIUtilityLog.txt in the C:\Temp folder,
or the Temp Directory specified in Configure AdBase. You should refer to this log file when
troubleshooting the BI Utility.
The BI Utility is located in the Mactive/bin folder. The ability to run the BI Utility is controlled by the Run
Utilities privilege. If you do not have this privilege, you will receive a “Permission Refused” message
when you try to login to the BI Utility. For more information on your privileges, see your System
Administrator.
To start the utility:
1. Double-click the BIUtility.exe icon.

The Login window displays:

2. Enter your User name and Password and click OK .

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

51

The BI Utility window displays:

A utility icon displays in the System Tray next to the clock. You can hide the BI Utility window by
clicking the Hide button. To display the window, right-click the utility icon in the System Tray and
select Show .
3. Click the Exit button, or right-click the utility icon in the System Tray and select Exit when you are
ready to close the utility.

Using the Execution Criteria Dates
When selecting the Execution Criteria, you have two options for specifying dates.



Check the Use create date box to search based on the date the item was entered into AdBase; for
example, the date on which an order was booked or a payment was entered.
Check the Use last edit date box to search based on the last time an item was modified; for
example, if an order was booked on 6/1, but a run date was added on 6/2, 6/2 would be the date
used to search.

The Start Date is automatically set to today’s date. However, you can change the Start Date to search on
that date and then forward. For example, if you check the Use create date box and enter 1/1/2005 in
the Start Date field, the BI Utility will begin searching for information (e.g., ad orders) entered in AdBase
on or after the first of January in 2005.
newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

52

You may also specify an End Date to limit the search for entries. Check the Use date range box to enter
a Start Date and End Date to search. The utility will search for all items that fall within this date range.

Viewing Unpopulated Items
Generally, the BI database is populated during the initial processing and load of information. For
instructions, see page 11. Then, based on your site’s practices, the BI Populator is run periodically to
update information. The View Unpopulated Items function compares the AdBase and BI databases to
locate any BI database tables that contain empty rows to ensure that all of the entries are being
updated. The following instructions explain how to run the BI Utility using this function.
To view unpopulated items:
1. Select View Unpopulated Items from the list of Functions.
2. Select the date criteria. For more information on each option, see page 52.
3. Select the Criteria For Unpopulated Item Search. Check the boxes next to the items you want to

include. If none of the boxes are checked, the BI Utility will search on all of the criteria. The following
options are offered.


Search for Clients (customer accounts)



Search for Debits



Search for Ad Orders



Search for Contracts



Search for Payments



Search for Invoices

 Search for Credits
4. Click Execute .
The Execution Status window indicates which criteria are currently being searched. For instance,
when searching for contracts, the status reads, “Checking core database for contracts.” If the utility
does not locate any unpopulated items, an Error message displays for each criteria. Click OK to close
the Error message.

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

53

When execution is complete, a “Processing Completed” message displays in the Execution Status. If
the utility locates any unpopulated items, the items are listed in a window similar to the following
example. While this window contains invoices, a different would display for each search criteria
used. From this window you have three options.

5. Click Apply Unpopulate Ids to File to save the ids to a text file. The file can then be selected on the

Object Id Filters tab in BI Populator.
The Open window displays:

6. Enter the File Name, select the destination folder, and click Open .
7. Click Apply Unpopulate Ids to ShBIInterface to save the unpopulated items to the ShBIInterface

table, or feeder table. You can then run the BI Populator using the Process data from adbase tools
option to populate the entries in the BI table.
8. Click Close Form Without Applying if you do not want to save the unpopulated items.

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

54

Balancing Ad Orders
The second function of the BI Utility is to balance ad orders between the AdBase and BI databases. This
function will make sure the balances for ad orders match in both databases. You may specify a range of
order Ids to search for or leave the range fields empty to search all orders in the databases.
To balance ad orders:
1. Select Balance Ad Orders from the Function list.
2. Select the date criteria. For more information on each option, see page 52.
3. Enter the Start Ad Order Id. The BI Utility will begin with the specified order. Based on the date

criteria, the utility will check all orders after the specified order.
4. Enter the End Ad Order Id to limit the BI Utility’s search. If you enter an End Ad Order Id, the utility
will stop searching ad orders when it reaches this Id.
5. Check the Include Credit And Debits in Ad Order Balance box to search for any credits or debits
applied to orders within the date range or Id range specified.
6. Click Execute .
The Execution Status shows the number of orders to process.

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

55

When the BI Utility locates any orders that are not balanced, the Unbalanced Ad Orders window
displays:

This window lists each of the orders that do not match between the AdBase and BI database. The
database Id and the order number are listed as well as the amount currently showing in each
database. From this window you have two options.
7. Click Apply Unpopulate Ids to File to save the ids to a text file. The file can then be selected on the
Object Id Filters tab in BI Populator.
The Open window displays:

8. Enter the File Name, select the destination folder, and click Open .

TIP: Click Close Form Without Applying if you do not want to save the unpopulated items.

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

56

Viewing User Entries
The dmUser table stores information about the AdBase users saved in System Admin. The View dmUser
Entry function allows you to search for user profiles that have not been populated or are not current in
the BI database.
Also in System Admin, you will find an option to Update Existing dmUser Entry . If this box is checked, all
modifications to a user profile are saved to a single entry in the dmUser table. If the Update Existing
dmUser Entry box is not checked, every time a user profile is modified, a new entry is written to the
dmUser table in BI.
NOTE: The Squeeze Entries During dmUser Fix option is still in development. This document will be
updated as soon as the feature is completed.
To view the user entries in BI:
1. Select View dmUser Entry(s) from the list of Functions.

Choose A Specific User to Process from the list of names. If you do not select a name, the following
Error message will display:

2. Click Execute .

The View DmUser Form displays the entries for the selected user:

3. Click OK to close the form.

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

57

Purging the BI Database
The BI Populator allows you to purge data from the BI database. The Purge button on the Execution
Criteria tab may be used to clear some of the upper level information from tables. However, keep in
mind that any ad order, GL, or contract information that is linked to other transactions in the database
will not be purged.
To purge information from the BI database:
1. Log in to the BI Populator. For instructions, see page 11.
2. Select the desired Object Id Filters and click the Purge button.

You must select information from the Object Id Filters to complete the purge or you will receive the
following message.

3. Click No when you receive this message and select the desired criteria from the Object Id Filters tab.

The Purge Complete message displays when the information has been successfully purged from the
database.

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

58

Dropping the BI Database Tables
Dropping tables will result in removing the tables and the data that resides in those tables from the
database. Do not drop tables unless you are sure that you do not need the data that is stored in them. If
you know that there is no data in any existing BI tables that you want to keep, drop all the BI tables
before creating them again. Also, if you are unsure if there are existing tables in your BI database and
you know that you want to delete all the data, you should first try and drop all the tables in the BI
schema so that you can start with a blank database.
To drop the BI database tables:
1. Click on the BIDLLFunctions.dll node.

CAUTION! Make sure the BIDLLFunctions.dll node is highlighted in blue before proceeding. If this node
is not selected, the Database Table Utility will drop all tables in your production database as
well. Dropping tables can cause serious damage, especially if you have not recently performed
a backup of your database.
2. Click the Drop Constraints button.
3. Click the Drop Tables button.

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

59

FAQ
What is BI?

BI is a separate database designed to make reporting more intuitive for Mactive’s users. BI is short for
Business Intelligence. The BI database was designed using dimensional modeling techniques to obtain
the following benefits.





An easy to understand schema layout
A database that is easy to query
A database that is adaptive and resilient to change
High performance access

How will reporting off BI be easier than production?

BI has about 60 tables compared to the over 400 tables in the production database. Also, a consistent
naming scheme has been used, making it very easy to know which fields join to each other.
How often can I update BI?

The BI tables are updated using the BI Populator Utility in the Mactive\bin folder. The BI Populator can
be set to run at any interval your site chooses. For more information, see page 29.
Can I purge AdBase and retain the reporting data?

Yes. Purging AdBase does not affect the data in BI. See page 58.
Does BI have to exist in the same DB and schema as AdBase?

No. It is probably better to have the BI database located on its own server.
Can I load legacy data directly into BI and bypass AdBase?

Yes. To do import legacy data directly into the BI database, you must supply Mactive with detailed
specifications of the data that you will be importing, and then an importer will be created for your site.
Will I need complicated formulas to report linage in agate line measurements like in production?

No. The BI database contains fields that have calculated many linage measurements, which are stored in
the fctInsertion table.











NumColumns
AdWidth (Twips)
AdDepth (Twips)
AdDepthAgates
AdDepthMillimeters
AdDepthInches
ColumnAgates
ColumnMillimeters
ColumnAgates
NumLines

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

60

How will order based charges be reflected in BI?

Order-based charges are spread proportionally across the insertion days within an order.
If the order is a Till Forbid (TF), then the process gets a bit more complicated. To spread an order based
charge or credit across a TF order, the BI Populator must first determine if accounting periods are set up
in the fnAccountingPeriods table. If the accounting periods are set up, the utility will use those defined.
If not, the utility will use months as the accounting period. Mactive defines the current accounting
period as that period or month that the credit or order based charges falls into, and the previous
accounting period as the period before the current accounting period.
Next, BI Populator will determine if the TF order has any run dates in the previous accounting period. If
so, the credit or order based charge is proportionately spread across all the run dates within the
previous accounting period. If there are no run dates in the previous accounting period, then the credit
or order based charge will be spread across all run dates from the beginning of the current accounting
period up to the effective date of the credit or order based charge.
How does the feeder table and the Process Data from AdBase Tools option work?

The feeder table ensures the BI database does not miss any changes that tools and utilities make to
AdBase. Some tools and utilities do not update the LastEditDate in AdBase tables due to not wanting to
send orders to PageLayout. An example would be Invoice Generator updating the Invoiced Already flag
or Contract Renew Utility issuing a short rate or rebate. This information should be passed to BI, but is
not needed in PageLayout. To get around this issue, tools now write a record into a feeder table in the
AdBase schema called shBIInterface. When the Process Data from AdBase Tools box is checked, the BI
Populator reads the shBIInterface table and populates the BI database with any information that tools
and utilities might have changed.
I am receiving an error during the initial load when processing a credit. What does this error message
mean?
"An error occurred while processing credit id: 55314 (see sql log)
Error: fctApply not stored, no record for InvoiceId 43864
An error occurred while processing credit id: 55314 (see sql log)"

This error message is referring to a credit created in AdBase that is being loaded into the BI database. In
AdBase, this credit is associated with an invoice. When you try to load this credit into BI, the BI
Populator first inserts a record into fctARSummary, then the Populator looks for the invoice record
associated with the credit in the fctARSummary table. If it does not find this invoice record, you will get
the error stated above.
When loading GL, you should load all invoices and debits first, then load the credits and payments. To
ignore credits and payments, you can set the range for credit and payments to be 0 to 0 on the Object Id
Filters tab.

newscyclesolutions.com

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

61

Index
Activity Log, 38
Ad order prefix, 14
Ad order records, 14
Ad Orders, 22
AdBase ODBC Connection, 36, 42
ADBASE_BI, 8
ADBASE_BI_IDX, 8
Add Constraints, 11
Add entry dmAdProduct table, 14
Add entry to dmAdProduct table, 16
Apply Unpopulate Ids to File, 50, 52
Apply Unpopulate Ids to ShBIInterface, 50
AR, 42
Architecture Scenarios, 1
Balance Ad Orders, 51
Balance AR, 44
Balance Orders, 36, 38
BI, 56
BI AR Auditor, 42
BI Auditor, 36
BI feeder table, 25
BI Feeder Table Processing, 11
BI Importer, 13
BI Interface App, 33
BI ODBC Connection, 36, 42
BI Populator, 11, 26, 39, 45
BI Utility, 47
BIDLLFunctions.dll, 8
BIFIXCONTRACTFILE.txt, 36
BIFIXFILE.txt, 36
BiPopulatorLog.txt, 29
BISQLFILE.txt, 37
BIUtilityLog.txt, 47
Browse, 32
Check Balances, 46
Check Fulfillment Records, 38
Close Form Without Applying, 50, 52
Commit Core Transactions, 36, 37
Commit Fix Transactions, 43
Configure AdBase, 5
Contracts, 22
Created Before date, 37, 43
Created On or After date, 37, 43
Creating BI Database Tables, 7
Credits, 23, 43
Criteria For Unpopulated Item Search, 49
Current accounting period, 29
Customers, 22
Data Source Name, 5
Database DLLs, 7
Database Sizing Workbook, 3
Database Table Utility, 7
Database Type, 6
Debits, 23, 43
dmAdOrderBools, 13
dmClient, 13
newscyclesolutions.com

dmUser, 13
Drop Constraints, 55
Drop Tables, 55
Enable BI Feeder Table Processing, 24
End Ad Order Id, 51
Execute periodically, 30, 35
Execute Periodically, 27
Execution Criteria, 23
fctAdOrder table, 14
fctApply, 19
fctARSummary, 19, 23
fctGL, 19
fctInsertChargeSummary tables, 16
fctInsertion, 56
Get ad order ids from file, 40
Get contract ids from file, 41
Get IDs from file, 31
GL, 22
GL Remapper, 44
Ignore Dates, 43
Ignore matching ad orders, 14
Import legacy data, 56
Include Credit And Debits in Ad Order Balance, 51
Index tablespace, 8
Initial processing, 11
Input File Type, 14
Insertion records, 16
Invoices, 43
Last Edit/Create Date Info, 30
Log timing info, 29, 34
Main tablespace, 8
Make Tables, 11
Object Id Filters, 45
Object ID Filters, 31
Oracle® Database, 4
Order-based charges, 57
Path for Log Files, 36, 42
Payments, 23, 43
Populate Both, 12
Populate Date Only, 12
Populate Null Rows Only, 12
Previous accounting period, 29
Process ad orders, 22, 28, 29, 39
Process contracts, 29
Process Contracts, 22, 41
Process customers, 29
Process Customers, 22
Process data from adbase tools, 27, 30, 50
Process Data from AdBase Tools, 24
Process GL info, 23, 29, 45
Purge, 54, 56
Questions, 3
Scenario 1, 1
Scenario 2, 2
Scenario 3, 3
Schedule Admin, 27

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

62

ShBiInterface, 47
ShBIInterface, 34
SizingCalcs, 3
Squeeze Entries During dmUser Fix, 53
Start Ad Order Id, 51
System Admin, 25
System Level Information, 25
Tablespaces, 8
Till Forbid, 29, 57
Total Size Requirement, 3
Unbalanced Ad Orders, 52
Unpopulated Items, 49
Update Existing dmUser Entry, 53
Use create date, 28, 30, 48

newscyclesolutions.com

Use Create Date, 22
Use credit id range, 23
Use date range, 30, 48
Use id range, 32
Use last edit date, 28, 30, 48
Use payment id range, 23
Use single ad order/transaction number, 33
Use time constraint, 30, 35
Use transaction id range, 23
User, 53
View dmUser Entry, 53
View Unpopulated Items, 49

BI User Guide (4.0), Version 1.1
NEWSCYCLE Solutions Confidential & Proprietary

63



Source Exif Data:
File Type                       : PDF
File Type Extension             : pdf
MIME Type                       : application/pdf
PDF Version                     : 1.5
Linearized                      : No
Page Count                      : 69
Language                        : en-US
Tagged PDF                      : Yes
Title                           : BI User Guide (4.0), Version 1.1
Author                          : Tim Downie
Subject                         : BI User Guide
Keywords                        : BI, User, Guide
Creator                         : Microsoft® Word 2013
Create Date                     : 2014:04:16 13:02:17-04:00
Modify Date                     : 2014:04:16 13:02:17-04:00
Producer                        : Microsoft® Word 2013
EXIF Metadata provided by EXIF.tools

Navigation menu