Business Intelligence projects are very crucial for all kinds of companies at every scale. BI projects become one of the most important projects especially after any crisis,. Unfortunately there are many unsuccessful BI projects. I’m going to write several reasons about this in the coming articles.
So I’ll start from the first step which is designing the datawarehouses.
PHYSICAL ARCHITECTURE
Mostly you can find different kinds of data sources. One data source for one module or application. So the problem we’ll need to create reports based on these different technologies, different sources. These different sources should be consolidated in a location. In that point, we’ll need a datawarehouse. Of course consolidating of the data sources into one central database doesn’t mean that copying only the data exactly like they are originally stored.
As a result, we have to use another physical server to populate the datawarehouse. After creating the database, the next step is creating tables that we’ll use in the BI project. This design is the most important part of this project. All the business requirements (report requirements) must be defined. Analysis phase will be completed so we’ll be able to create these tables. Design of the tables will be based on Star Schema and/or Snowflake Schema.
We’ll have to create one fact table and one or more dimension tables in the database in order to implement Star/Snowflake Schema. Dimension tables will contain information about a specific business entity. Like product information. Product category, subcategory, manufacturer etc.. Fact tables stores the transaction details. There should be Foreign Key and measure columns in the fact table. Foreign keys will be referenced to primary key columns that are stored in Dimension Tables.
