Re: Using SAS DI Studio To Load A Data Vault

_thesasgeek — Fri, 14 Nov 2014 21:02:51 GMT

Many thanks for this post Linus.

I am struggling to find out how to PM you on this sure to ask for the package to be emailed so I can look at the example above in more detail. I've added you on LinkedIn so hopefully we can correspond on there.

Re: Physical Table Design for DI

_thesasgeek — Fri, 14 Nov 2014 19:20:19 GMT

Many thanks Linus for taking the time to reply.

I agree that we have a lack of architecture and development standards as each one was developed in its own right.

I'll have a read up about the 3NF and Data Vault styles.

Can you recommend and books or papers on these topics?

Physical Table Design for DI

_thesasgeek — Wed, 12 Nov 2014 17:14:26 GMT

Typically, my organisation's SAS database design is a mix of approaches depending on the developer who designed it.

I've been searching for a long time about the preferred style of physical table design within DI which will marry up with the use of the BI tools.

I feel that there is too much focus on how things were done about 5 years ago to navigate around problems with I/O and space constraints.

Currently, many of the tables that are used by analysts with my organisation are monthly tables with a date suffix at the end of either YYYYMM or MON_YYYY. I feel that these don't naturally progress through into the BI tools - would people agree? This also leads to problems with (that is unable to) opening tables within DI as the preferences of tables have macro variables within them.

Please can people share what they think is the best physical table structure within DI which will then allow users to get maximum benefit from the BI tool (OLAP cubes, Web Reports etc).

I personally think that it should be....

Reception Layer contains the unformatted information from either raw files or SQL database connections.

Q: Do people retain the information within SAS? If so, how? Date suffixed tables? (I ask as a SAS form of the raw data might need to be retained in circumstances and doubt people would store in dimensions etc?)

Foundation Layer contains a star schema type approach of dimensions and fact tables using custom transformations (as SCD works slowly).

Exploitation Layer would be tables the analysts frequently use with single table containing multiple observation points allowing trend analysis etc.

Are there any good SAS papers out there for this type of topic?

Many thanks in advance.

Clark

_thesasgeek Tracker

Re: Using SAS DI Studio To Load A Data Vault

Re: Physical Table Design for DI

Physical Table Design for DI