Touches on the design of a Data Warehouse

Assuming you have already done a good requirements analysis with analysis of existing reports, interviews with key users, etc., And that already is clear about the different sources on which to focus the DWH , we consider what the facts are most important for the company and for users, business analysts have to exploit the information.
Above all it must always bear in mind that the goal of DWH is not to draw any detailed reports, but the user can analyze the information, browse and view business information pooled from different points of view.

Returning to the sources, these are relatively easy to identify, and be very close to the main activity of company departments. I put some examples:
Dpt. Sales -> Sales, Commissions
Dpt. Shopping -> Shopping Providers
Dpt. Marketing -> Campaigns, Promotions
Dpt. Accounting -> Payments, Expenses
Dpt.Personal -> Presence, Training, Recruitment
Dpt. Logistics -> Stocks, Distribution
Each bulb is going to be in the fact table and star center of a Data Warehouse.

Most recommended methodologies begin implementation of Data Warehouse focusing on a single focus, and then go upgrading with others, but always one by one. This would create Data Marts separate business-oriented. Although separate believe will share many of the dimensions, which are the points of view under which we analyze the information, and can create a common repository that will expand with each Data Mart. This repository also known as ODS (Operational Data Store).
Thus, in the ODS is stored all the common information and time frames, and prepares it to feed the Data Marts. This environment is not mandatory, but recommended, as centralizing all corporate information can also contribute to the creation of certain reports are not analytical.

A logical design level is paramount while the star design, define the fact table with all the important indicators for the business covering the Data Mart, and choose the minimum granularity with which to store the data.It is important to make an effort not to store more detail than necessary, or use of space trip and performance problems when we want to access the data. We must choose the granularity for each 'key' of a fact table.
For example, we could design a sales data mart with information grouped by Seller, Shop, Customer, Product and Dia. Would have to be some very special needs analysis to justify the need to overload the system sales stored at a detailed level of hours or minutes.

These same codes and we have defined the start of five dimensions, only necessary to complete the nest with the other levels, they will not be more than groups of lower-level elements.It would be interesting to think about whether to analyze the information with respect to some dimension, such as geographic location, which is one of the most persistent dimensions, along with the time, never missing.

From here and you enter the physical design. It often creates a structure for collecting snapshots of the operational systems called Stage Area, which preserves the structure exactly as in the source system. Then comes the ODS which already made the first data transformation and begin to integrate information. The ODS is typically created and timestamps necessary data structures are unified, and organizes the information so it is easy after feeding the different stars.You can begin to de-normalize some entities, making some groups in tables of dimensions, although the model is still more like a relational.

Finally, create a star for each Data Mart (if you follow the technique of design star), and the corresponding dimensions, which for the first data mart will be all new, and some start from the second to be shared, and may redesigned with the new requirements of the next model.