I hope by now you have SQL Server installed. In this post I will quickly talk about database design and give you a quick overview of different things.
Database Design (Quick Snapshot):
Your database initially is empty or there may be a need to add data at some point. Therefore, you need to be able to design your database efficiently. Database Design is simply a way of getting tables you need right. So department table will have things like department id , department name, department location etc. This is a job of data architect. The design process has three phases in general.
1. Gathering requirements from BA. Verifying relationship and creating conceptual diagrams.
2. Coming up with logical diagram. Architect comes up with table names, dependencies and data types etc.
3. Physical model.
Usually the above process can be carried out in Erwin or SQL Power Architect. It’s relatively straight forward. In the end of the process you can generate a schema using forward engineering. Hit the ‘Generate‘ button and Connect to SQL Server (using windows authentication or whatever system you have) and your tables will be created from Erwin. You can also generate scripts for tables you have and copy paste them in SQL server. Remember database have different objects like tables, views , stored procedures, functions, trigeers etc but tables hold the real data.
Noobs 3 Best Practices For Database Design:
- Document your ER diagram.
- Consistency in naming.
- Spend more time designing.
Overview:
When you install your server, you will not get any databases. You will only get system databases which are master, model, tempdb and msdb. Please do no create tables on system databases. Why? If you do, upgrading later will be a big problem. We will soon talk about attaching the database we downloaded in Part-1 in SQL Server Management Studio.
One last thing. Majority of business users are never concerned about all this stuff. All they want is access to reports. Where are the reports? In SQL SERVER REPORTING SERVICES you will have an URL where reports are hosted. You have report server name like https://mystartup/reports/folders.aspx. In other companies they have sharepoint portal. Report is usually tested and deployed. If users want to add column then the whole life cycle is repeated. There is no Ad-hoc in this scenario. We need to be able to give users Ad-hoc capabilities. To do so, we need cubes and give users access to them. I really didn’t want to go through all this but again this will sets up a stage for my future posts.
In excel, either you can connect to SQL SERVER directly and import data in or you can use Analysis Services to retrieve cubes. In order to create a cube you need a Data mart. Data mart is simply an access layer of your Data Warehouse. We can create a new data mart for user requirements and write an ETL using SSIS. Phew…
DataTweet 