topic Re: Where can I find columns of Fact, Dimension, Bridge and other support Tables for SAS AML Solutio in Fraud, AML and Security Intelligence

Where can I find columns of Fact, Dimension, Bridge and other support Tables for SAS AML Solutions

ckpotab — Thu, 08 Oct 2015 07:26:13 GMT

Hello all,

I am new to SAS and I am currently working on SAS AML Scenarios.

The context:

I have information on the parameter values and the required source columns for scenarios that are of my interest.

For example, Scenario SAS10005 uses the following:

Parameters:

p10005_account_type_desc
p10005_cdi_indicator
p10005_ctr_amount
p10005_currency_acct
p10005_pri_medium_desc
p10005_status_desc

and the required Source Tables - Source Columns are:

DERIVED - DATE_KEY
FSC_ACCOUNT_DIM - ACCOUNT_TYPE_DESC
FSC_ACCOUNT_DIM - CURRENCY_BASED_ACCOUNT_IND
FSC_CASH_FLOW_FACT - CURRENCY_AMOUNT
FSC_CASH_FLOW_FACT - TRANSACTION_KEY
FSC_CURRENCY_DIM - CURRENCY_CODE
FSC_TRANSACTION_STATUS_DIM - STATUS_DESC
FSC_TRANSACTION_TYPE_DIM - PRIMARY_MEDIUM_DESC
FSC_TRANSACTION_TYPE_DIM - TRANSACTION_CDI_CODE

Now, I want to understand the mapping between the parameters and their relevant columns.

For Ex: p10005_account_type_desc is mapped to FSC_ACCOUNT_DIM - ACCOUNT_TYPE_DESC

However, I am not able to find the mapping column for p10005_ctr_amount. I thus request your inputs on where can I look for this. Also, to help me work on other parameter mappings, kindly share information on where can I find all the columns of all the underlying tables of SAS AML (Fact, Dimension, Bridge, Transaction and other supporitng tables)

Thank you in advance.

Chaitanya.

Re: Where can I find columns of Fact, Dimension, Bridge and other support Tables for SAS AML Solutio

LinusH — Thu, 08 Oct 2015 08:07:12 GMT

Sorry for shooting in the dark, answer based on experience with other SAS solutions.

Do AML come with DI Studio? Then try to use the Reverse Analyze utility.

Also, SAS have some tendencies not to use metadata for loading some of the data marts in their solutions, rather parametrized calls to macros. If so, you need to dig deep into the documentation.

Bottom line, I think this question is best directed to product specialists at the institute.

Re: Where can I find columns of Fact, Dimension, Bridge and other support Tables for SAS AML Solutio

ckpotab — Thu, 08 Oct 2015 08:13:04 GMT

Thank you.. AML (Anti Money Laundering) does have DI studio.

I am looking for some pointers on user guides which might have those details. Could you suggest any?

Thank you.

Re: Where can I find columns of Fact, Dimension, Bridge and other support Tables for SAS AML Solutio

LinusH — Thu, 08 Oct 2015 09:50:42 GMT

User guides for AML (I don't know, the documentatin is locked for non AML customers).

For DI Studio? There is a user guide on support.sas.com that should cover the basics, like Analyze. But to learn the tool, you probably need guidance/training.

Re: Where can I find columns of Fact, Dimension, Bridge and other support Tables for SAS AML Solutio

Patrick — Thu, 08 Oct 2015 13:48:17 GMT

I've got actual SAS AML implementation experience. What version are you using? What's your role? What do you need to do? You're new to SAS - what's your programing background if any?

SAS AML is a rather big beast and if this is an implementation or upgrade then normally SAS Professional Services or specialized partners are directly involved in the project.

If you don't have all documentation or need a data model/data dictionary then ask these guys to provide it. You could of course also create such a listing directly from the database running SQL Describe statements.

@LinusH

Source systems ->(ETL) AML CORE ->(ETL) Prep Files -> AGP -> AML KC ...

AML CORE: A dimensional model holding the consolidated data from sources

Prep Files: Datamart tables created from Core to run rules (scenarios) against

AGP: Alert Generation Process

AML KC: Knowledge Centre, data model holding Alerts, configuration and enrichment data

AML is one of the areas where you don't want to tell the "bad guys" what you're doing. That's one reason why documentation is not public.

Scenarios are code snippets which get registered against Prep Files (to be precise: Scenarios & Prep Files get registered agains headers), which have defined parameters (macro variables in the code snippets), and which run in defined frequencies (like daily, weekly,...).

The AGP process reads this registered information, generates and then executes SAS code (header & scenarios) - and does also a few other things like replicating data from CORE to KC.

data alert;
   set daily_prep_file;
   by customer;
   array amount {*} amt1 amt2 ....; <all transaction amounts for this customer and day >
   array....

   /*  scenario 10005 */
   scenario='p10005';
   <sum currency amount if credit>
   if &p10005_ctr_amount > sum_currency_amount_credit then output;

   /* scenario .... */

run;

@ckpotab

Now to your initial question: What you're dealing with are parameters used in the scenarios for comparisons. These parameters don't map directly to a colum in the CORE data model.

The PREP file used in the Header section of the code has columns mapping to CORE, either 1:1 or derived. Additionally during AGP processing additional columns will get created like sums (the columns which you define as analysis variables when registering Prep files against headers will be used to generate arrays - like all transaction amounts of a day for a customer).

p10005_ctr_amount is such a case. It holds the threshold value for credit currency transactions for the primary_entity.

I hope this didn't confuse more than it helped.

Thanks,

Patrick

Re: Where can I find columns of Fact, Dimension, Bridge and other support Tables for SAS AML Solutio

ckpotab — Thu, 08 Oct 2015 13:41:55 GMT

Thanks a lot Patrick.. appreciate the inputs and help very much..Also, for the name of the document, the details are very clearly available in the document named Installation, Configuration, and Administration Guide by SAS

Re: Where can I find columns of Fact, Dimension, Bridge and other support Tables for SAS AML Solutio

Patrick — Thu, 08 Oct 2015 13:58:40 GMT

A lot is documented and available - but there is also stuff missing like the data models (they exist though, you just need to ask for them).

For what you're doing (as I understand it) the other documentation is "Scenario Administration: User's Guide"

I would also hope that the team designing and implementing the ETL's created a data mapping document.

Re: Where can I find columns of Fact, Dimension, Bridge and other support Tables for SAS AML Solutio

ckpotab — Thu, 08 Oct 2015 15:24:33 GMT

@Patrick, yeah.. I have access to Administration guide and also, we have the data mapping document. Could you suggest where exactly can I get my hands on the Data Models.. I am currently learning SAS AML Data Model and I am not very happy with the documents I have. So who exactly do I ask for access to SAS AML Data Models?

Re: Where can I find columns of Fact, Dimension, Bridge and other support Tables for SAS AML Solutio

Patrick — Thu, 08 Oct 2015 21:13:21 GMT

@ckpotab

I don't know how your organisation ticks. If it's a current project then I would start with the PM or tech lead, else with the technichal owner or BAU team in your organisation.