12-04-2014 03:31 PM
I am trying to build a data warehouse (star/snowflake) in SAS Enterprise Guide.
Once I have assigned a library, how can I create the schema?
Could you please advise how I should progress and/or could you refer to a suitable article?
12-04-2014 06:00 PM
There are no tools or tasks in EG to build a data warehouse schema. It can be done but you would need to design and hand-code it yourself. You can define your required tables either in a SAS DATA step or the SQL procedure.
If you prefer a more automated approach SAS has a product called Data Integration Studio. Check it out here:
Automation of Star Schemas is also possible in SAS VA, which is superceding SAS's older cube technology:
12-07-2014 06:11 PM
Thank you for your reply.
I do not have access neither at Data Integration Studio nor at SAS Visual Analytics and, therefore, I would go for the SQL procedure.
Thank you and regards,
12-07-2014 07:10 PM
One thing is to create the tables another to load data on a regular basis.
For table creation:
If these are SAS tables then a data steps with ATTRIB statements are as good as SQL. You then would use PROC DATASETS or PROC SQL to create indexes and the like. If the tables reside in a data base then I would use data base specific SQL code for everything.
To populated the tables: Are you designing for slowly changing dimensions. You can use SAS EG to write the code - but it will be hand-coded and quite a bit of work. If this is a "serious" data warehouse then using SAS' ETL tool, SAS DI Studio, would be the way to go. Yes, you can hand-code it. I had to do this in the past. It's much more effort to implement and comes with reduced maintainability.
12-08-2014 06:38 AM
What is it that you want help with?
Designing star schemas or the SAS programming part?
Programming and loading a star schema does not differ much from most other type of programming, but it depends on the "style" of your star schema.
If you wish to use surrogate keys forinstance, that typically requires some specific routines (which you by the way are much helped in ETL tools such as DI Studio).