Starting the set up CDM in SQL

[tfabri]

Hi all, 

I'm starting an implementation process and I will need to configure the CDM (following the doc SAS® Customer Intelligence 6.5 Common Data Model Data Dictionary) in the near future. The common data model will be set up in SQL and I'm looking for the SCRIPT to create all necessary tables, fields ans relationship between tables

Does anyone have this script? Also, does anyone know how many GB (or TB) is recommendaded to create the CDM?

I really appreciate any help.

Thiago Fabri

[Bet_ty · Accepted solution]

Hello Thiago,

the script files to create the tables, fields and relationships are available at the following location:

<SASHome>\SASFoundation\9.4\cicsvr\sasmisc

Depending which SAS/Access engine you are using to connect to SQL Server, you will need to run one of these scripts:

ci_cdm_ddl_sqlserver_odbc.sas
ci_cdm_ddl_sqlserver_oledb.sas
ci_cdm_ddl_sqlserver_sqlsvr.sas

To pre-populate some of the tables you also need to run ci_cdm_load_codes.sas.

Regarding the sizing, there is no easy answer because it all depends on the following (to name just a few):
- Estimated number of campaigns/year/month
- Average number of communications per campaign/year/month
- Average number of Treatments
- Average number of UDF's per campaign
- how many months of history you want to keep into the contact/response/Presented treatment history tables?
- how many Business Contexts are you using?
- Is this MA only or also RTDM?

etc.

All the necessary steps are documented in the SAS Marketing Automation 6.5 Administration Guide in Chapter 7, "Implementing the Common Data Model", which is available here:

http://support.sas.com/documentation/solutions/ci/6.5/en/PDF/maag.pdf

I hope this helps.

Thanks,

Beate

[tfabri]

Hi @Bet_ty,

I have no word to say thank you for your support! 

I reallly appreciated it.

Kind regards,

Thiago Fabri

[tfabri]

Beate, answering your points:

- Estimated number of campaigns/year/month

120/Month -  around 1500 campaigns / year

- Average number of communications per campaign/year/month
6 comunication / campaign

- Average number of Treatments:

20 treatmens

- Average number of UDF's per campaign

N/A

- how many months of history you want to keep into the contact/response/Presented treatment history tables?

3 years

- how many Business Contexts are you using?

10 BC 

- Is this MA only or also RTDM?

Only MA.

Do you have any idea (or just a simple suggestion) of the size of storage that should be used in this case? I'm thinking of starting with 100 GB just for the CDM.

Thank you a lot, again! 😃

Thiago

[Bet_ty]

Hi Thiago,

I am really sorry but regarding the sizing you need to involve somebody from our SAS Professional Services/Consulting teams to support you with your implementation activities.

These sizing questions are not something that I can help you with.

Thanks,

Beate

[tfabri]

No problem Beate!

I will do it!    Thank you again for all your supoort.

Have a great weekend!

Thiago