Naviga Analytics

Naviga Analytics

  • Analytix
  • BI
  • InSight
  • Informer

›BI/Core Support Docs

BI Maintenance

  • BI Database Core Mapping
  • Running The BI Auditors

BI Resources

  • BI Resources

BI/Core Support Docs

  • BI FAQ
  • Adbase Core FAQS
  • BI ChargeTypes Explained

Common SQL Core/BI

  • Common BI Queries
  • Common Core Queries

BI FAQ

  • BI Populator Command Line Arguments
  • dmUser SysAdmin Setting
  • Spread Logic may Cause Large Spread Amount
  • BI Populator Object Id Filter Mapping
  • shBIInterface Table Codes

BI Populator Command Line Arguments

The following command line arguments may be used to run the BI Populator from the command line prompt or to schedule the 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 <username>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.
-autoRuns 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.
-continuousRuns 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
-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.
-crdateSpecifies 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.
-ledateSpecifies 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.
-customerSpecifies customer population will be performed.
-customerfile <filepath>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.
-contractSpecifies 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.
-glSpecifies 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.
-adorderSpecifies 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.
-leavesettingsIf 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.

## dmUser SysAdmin Setting

A setting in System Admin 🡒 Tools/System Level Information 🡒 Other Settings tab called “Update Existing dmUser Entry” is used to control when a user is moved to a new sales team, region, territory or company.

Update Existing dmUser Entry - CHECKED

When this option is checked, any change made to the sales rep’s Team, Region, Territory or Company within system admin is reflected by updating the existing row in the dmUser table.

Update Existing dmUser Entry - UNCHECKED

If this option is not checked, than when a change is made to a sales rep’s Team, Region, Territory or Company a new row is created in dmUser for that sales rep, but his old row still exists.

img

What Does This Mean

These options affect how the Sold By rep’s transactions are able to be reported.

Every transaction (ad taken) has a Sold By sales rep attached to it. This is a field that is set in Adbooker.

When this option is Unchecked it will effectively let the Sold By rep’s transactions “move” with him when he moves to a new sales team, territory or region.

For example, Rep JDoe is on Team Alpha in '17 and in Dec '17 he sold 10 ads in which he was the rep in the Sold By field in Ad Booker. If we were to run a report for Dec '17 for Team Alpha, Rep JDoe’s sales would be included in the total.

Update Existing dmUser Entry - CHECKED

If in Jan ’18 Rep JDoe was moved to Team Beta and Option 1 was set in System Admin, the same report, Dec '17 for Team Alpha, Rep JDoe’s sales would NO longer be included in the total.

Update Existing dmUser Entry - UNCHECKED

However if this option is checked in System Admin, the above report would be the same both before and after Rep JDoe was moved to Team Beta.

Be aware the Rep JDoe always is the Sold By rep on the ads. This just allows you to choose what Team, Region, Territory or Company those ads should show up in.

BE CAREFUL Changing This Option

If you have been populating the BI Database with this option unchecked and then decide you want it checked. You will need to run a process in the BI Populator to make sure the data is consistent.

If you change this setting, from being un-checked to being checked, there is a function in BiPopulator to update the existing entries in BI. This would merge all entries for a sales rep to point to one record in BI.

Some sites start with the setting unchecked, but realize they want to change this and go to one record per sales rep. To update existing data to have one row vs. multiple you can use a function in BI Populator:

1543509375900

You click on ‘Fix The Table’ and it goes through all the dmUser records and merges them into one per sales rep. Note: Always do this in a separate BI Populator session here you are logged in with a user that is NOT the same as the Automated BI Populator.


## Spread Logic may Cause Large Spread Amount

First seen in 2009

First and foremost this is not a bug in the sense that there is an error in the code. As you will see it is caused by how the spread is calculated.

The spread algorithm is designed to spread an amount, like order level charges, credits, etc, across all other charges and distribute a weighted amount to each.

Here is how the algorithm figures the weights:

Assume we are spreading a $50 Order Level charge across an ad with 3 revenue entries

1539022078765

  1. Add all the revenue amounts together that we are going to spread the order level charge across. (Amount Column)
  2. Divide each line item amount by the total amount to get a weighted percentage of how much of the spread amount to allocate to this line item.
  3. Multiply the Spread Amount by the calculated percentage found in step 2 to get the spread amount allocation.

This works fine until you run into a situation with line items as follows:

1539022127954

You can see that because the final total is a small number our percentages are very high, but in the end add to a total of 100% and the spread amount add to the correct number.

This is most commonly seen in Till forbid ads.


## BI Populator Object Id Filter Mapping

The BI Populator allows you to manually populate individual records from the Object Id Filters tab. Below is a mapping to the fields that are being referenced on this tab.

1539022454846

shBIInterface Table Codes

The shBIInteface table in the AdBase core database is a table used to tell the BI Populator to populate information that will not get picked up by the BI Populator's normal check.

Usually, it is utility applications like RepSetter that will populate this table when it is run by users.

select * from shbiInterface

One of the fields in the shBIInterface table is the FunctionCode field. This code represents what type of populating is going to occur. The other field of importance is the ApplicationId which tells us which application wrote the record in the shBIInterface table. Lastly, the ObjectId, is the associated ID to be populated. Whether the ObjectID represents an AdOrderID or CustomerID or something else, depends on the FunctionCode .

Function Codes

Below is a list of the function codes and their enumerations.

FunctionFunction Code
Contract related functions
ShBIInterfacePopulateContract1
ShBIInterfacePurgeContract2
Invoice related functions
ShBIInterfacePopulateInvoice101
ShBIInterfacePurgeInvoice102
ShBIInterfaceInvoiceClosedDate103
Debit related functions
ShBIInterfacePopulateDebit201
ShBIInterfacePurgeDebit202
ShBIInterfaceDebitClosedDate203
Payment related functions
ShBIInterfacePopulatePayment301
ShBIInterfacePurgePayment302
ShBIInterfacePaymentClosedDate303
Credit related functions
ShBIInterfacePopulateCredit401
ShBIInterfacePurgeCredit402
ShBIInterfaceCreditClosedDate403
Ad order related functions
ShBIInterfacePopulateAdOrder501
ShBIInterfacePurgeAdOrder502
ShBIInterfaceAdOrderQueueStatus503
ShBIInterfaceAdOrderInvoicedAlready504
ShBIInterfaceAdOrderEditorialInsert505
ShBIInterfaceAdOrderPrimarySalesRep506
Miscellaneous functions
ShBIInterfacePopulateCustomer901
ShBIInterfaceStatementNumber902
ShBIInterfaceSyncAccountingPeriods903
ShBIInterfaceLast90

Application Ids

Below is a list of the Application Ids and their associated application descriptions.

ApplicationId
AdBookingAppId1
ContactManagementAppId2
GraphicsManagementAppId3
LockAdminAppId4
GalleyoutAppId5
AdRaterAppId6
ProductManagerAppId7
AdOrderExportAppId8
ScheduleSrvAppId9
AdmarcCustomerExportAppId10
AIMCustomerExportAppId11
OfficePayMgrAppId12
OutputAppId13
AdOneExportAppId14
Layout8000ExportId15
CashReceiptsId16
CustomerBalanceUtilityId17
DisplayAdImporterId18
InvoiceGeneratorId19
RemoteDataAppId20
TillForbidExtenderAppId21
RemoteMaintAppId22
AdOrderPurgeUtilityId23
CustomerPurgeUtilityId24
RemoteClientDataSenderAppId25
CreditManagerUtilityId26
ContractRenewUtilityId27
DBTableUtilityId28
WorkFlowUtilityId29
SystemAdminId30
AdbaseWebServerAppId31
GEACCustomerExportId32
PaymentDaemonId33
GEACCreditImporterId34
NovaInterfaceId35
FinanceManagerId36
GEACContractExportId37
WebExporterId38
GraphicPurgeUtilityId39
ContractManagerAppId40
BlindBoxReplyManagerId41
LockBoxUtilityId42
WriteOffUtilityId43
NovaViaWarpInterfaceAppId44
AuthorizeDotNetInterfaceAppId45
CrainCustomerExportId46
CrainContractExportId47
AdBaseXMLImporterAppId48
UserFieldsImportId49
HighPlainsAdOrderExportId50
HighPlainsCustomerExportId51
GannettCustomerImporter52
PlainDealerImportAppId53
ExpireAdsId54
PaymentPlusAXId55 // Payment Plus interface
SAOEImportAppId56
ICVerifyAXId57
SuperChargeInterfaceId58
SmartStreamAdOrderExportId59
MactivePGLExportId60
SmartStreamInterfaceId61
PlainDealerAdOrderExportId62
CampaignManagerAppId63
ImportEditorialInformationAppId64
PlainDealerCCInterfaceId65
Annapolis8000ExportId66
PlanPagExportId67
SJCC8000ExportId68
SystemTranslatorUtilityId69
AdBaseWebServerId70
AppSettingsManagerId71
DataAnalysisReportId72
DataAnalysisToolId73
SpokaneCustomerExportId74
WebDbManagerId75
XMLWebExporterId76
DataImportAppId77
SpokaneCustomerImportId78
AdjustmentManagerAppId79
PlanPagImportId100
AdQuoterId101
ExternalLoginAppId102
InvoiceAutoPayAppId103
CustomerExportId104
CreditBalImportId105
GraphicsExporterAppId106
RefundCheckExporterAppId107
TellanAXAppId108
GLExporterAppId109
VerisignPayflowAppId110
AutoLaunchToolAppId111
CustomerImportAppId112
AdTrackingExportAppId113
TimesPicayune8000ExporterId114
AdbaseXMLExporterAppId115
AmosCustomerExportAppId116
SanJoseGLExportAppId117
ContraCostaGLExportAppId118
NewarkOrderExporterAppId119
SJCCGLExportAppId120
SystemNodeMonitorAppId121
AdbaseSystemNodeManagerAppId122
AdbaseToPMPAdtrackAppId123
AdjustmentsMonitorAppId124
AdbasePurgeUtilityAppId125
RevenueExportAppId126
BIPopulatorAppId127
EmailAlertManagerId128
GatewayPaymentsMgrAppId129
InCollectionsMarkerId130
BalanceUtilityId131
IpixManagerId132
BIImporterAppId133
Fayetteville8000ExportId134
RangerExportAppId135
USPSConversionUtilityAppId136
StatementNumberToolAppId137
TimesPicayuneUSGExportAppId138
TimesPicayunePPBillingExportAppId139
MactivePlannerAppId140
AdbaseXMLDistributorAppId141
MactiveLoginAuditTrailId142
XMLCustomerTransportId143
CallFXExportAppId144
BIUtilityAppId145
BIInterfaceAppId146
KubraInterfaceAppId147
ScheduleAdminAppId148
MGDSIProjectedCountsAppId149
MGDSIFinalCountsAppId150
AdPayManagerId151
MobileAdPackPreprintId152
BonusSpendingUtilityAppId153
MactiveImageManagerId154
MactiveImageUtilityAppId155
DorfmanInterfaceAppId156
Newark8000ExportId157
VVPaymentInterfaceAppId158
LiveProcessorInterfaceAppId159
BookedSizeUpdaterAppId160
MediaLinkAppId161
PlaceholderOrderAppId162
DocumentEpxorterAppId163
StandardReportsAppId164
CyberSourceAppId165
AdbaseToYahooHotJobsId166
CustomerStatusUtilityId167
SDEPayInterfaceId168
ContractImportAppId169
GraphicsImporterAppId170
PaywareAppId171
DirectDebitToolAppId172
AdbaseARAppId173
InterfaceDateUtilityId174
PreprintCountToolAppId175
DJBizSerivicesInterfaceAppId176
CreditCardUtility193
EPSImporterAppId194
PaymentCutoffUtilityAppId195
SalesforceUpdaterAppId196
TagOrderToolAppId197
BatchCheckerAppId198
DJBizPayToolAppId199
OASExporterAppId200
PDFLinkerAppId201
AdbasePrintAuditAppId202
PersonalFinanceManagerAppId203
WebInventoryManagerAppId204
PaymentTokenManagerAppId205
BraintreePaymentAppId206
GraphicsUpdaterAppId207
XmlImporterFolderServerAppId208
SSPCustomerExportAppId209
OneViewAppId210
EdgilEccOAppId211
ElavonProtoBaseAppId212
AutoPlannerAppId213
BcContentMgmtSubsystemId214
BcSpaceMgmtSubsystemId215
TrafficManagerAppId216
SCANDARFinanceChargerAppId217
OneViewSelfServiceAppId218
FTNIAppId219
PayFlowTokenAppId220
SPHWorldPayAppId221
EPXAppId222
EdgilPayWayAppId223
FDHelperAppId224
Newsday8000ExportId225
AdServingAppId226
PreSalesAppId227
MSSExportAppId228
AdSafeExportId229
BroadcastFormatImporterId230
BroadcastPostProductionId231
BroadcastFeedbackImporterId232
GLRemapperAppId233
ASIStatsUtilityAppId234
IntegrationServicesAppId235
RelationUpdateInterfaceId236
MonerisAppId237
EPSFPurgerAppId238
GoalToolAppId239
ChasePaymentTechAppId240
ISNPDFXMLExporterAppId241
ImportEditorialInfoAppId242
PrintQueueToolAppId243
ReportToolAppId244
FirstDataAppId245
FieldedDataModUtilAppId246
DJContractAccrualExportAppId247
FinanceChargerAppId248
← BI ResourcesAdbase Core FAQS →
  • BI Populator Command Line Arguments
  • dmUser SysAdmin Setting
    • Update Existing dmUser Entry - CHECKED
    • Update Existing dmUser Entry - UNCHECKED
    • BE CAREFUL Changing This Option
  • Spread Logic may Cause Large Spread Amount
  • BI Populator Object Id Filter Mapping
  • shBIInterface Table Codes
    • Function Codes
    • Application Ids
Naviga Analytics
Docs
Getting Started With AnalytixBI Docs
naviga
Websitenaviga BlogTwitterFacebookLinkedIn
More
Analytix DocsBI Docs
naviga - Venture Forward
Copyright © 2021 naviga