One of the important aspects of database design is to model the database. There are two types of models one is logical model and the other is a physical model. It is important to model the data that needs to be analysed or reported. Modelling provides a abstract view of the tables present in the database, here in modelling terms we talk about entities and relationships. With good modelling one can understand the value of data beneath it and also provides a better view of the business that we are trying to capture in terms of the Data. Most of the times the models (ER) are reverse engineered, rarely do models are generated first and then database is designed. Sometimes one would have a fair idea of the entities that would be present in the database through a model. I recently started working on a data sourcing project and one the requirements was to have a ER model of the data that we are receiving from the source. In order to generate/create the ER model I decided to use the ER/Studio Data Architect Version 8.5.3 from Embarcadero technologies.
http://www.embarcadero.com/.
The install was pretty straightforward. Once I launched the ER Studio, I realised that there lot of options available in this tool. There are 3 different options for creating a model:
Draw a new data Model: There are 2 options Relational,Dimensional
Reverse Engineer an existing Database: Here one would connect to a database and choose the tables that need to be modeled. This is one of the most used option.
Import Model From: This can be done on ERX file,SQL File and External MetaData.
I choose the option of Reverse Engineer an existing database, this is done by connecting to a sql server database which is set up using the ODBC setup option in the Tools menu in the main screen of the ER Studio.
Once I connected to the database I choose from the list of tables I needed to model and the ER Studio takes you through a couple of screens and finishes up generating the ER Model. In the Screens there are options to choose relationships based on Foreign Keys. Overall there are lot of features that are available in the ER Studio which makes it a full fledged modeling tool.
Hello Ram,
ReplyDeleteGreat blog very informative. I had a question as I saw SAS in your profile.
Could you please suggest how to import data from SAS table to SQL server table via SSIS 2005/2008 r2 ?
Currently we have a flat file dropped of on a file share location where we pick up that file and import the load into sql tables. We are planning to create an ETL package to directly draw data from the SAS tables into SQL Server tables. Please help !!
Hi Sagar,
DeleteThanks for your comments. Based on your question, I created a post on importing data from SAS into SQL Server. There is a blog post(link) in my article which details the import into SQL Server from SAS. Hope you find it helpful.
Thank you