DBM 460 Week 3 Dimensional Model Hands-On Project

Dimensional Model Project

Dimensional Model

Lafleur Trading Company is looking to integrate their departments to a centralized shared environment. The company has several departments within its organization. They are looking to increase the productivity and better communication between departments to provide a more efficient service within the company as well to their customers. However, in order to accomplish this they need a way to integrate their data from different departments into one system. A data warehouse would solve this problem of having their information distributed in different departments

Analysis

In order to extract the information needed for properly analysis a couple of things must happen. The first piece to take care of would be to complete an enterprise wide analysis of the data requirements. This would include having the data that are in the different departments integrated into the new data warehouse system that will be implemented. Once the data warehouse is constructed the data from the existing databases will be extracted and consolidated into one location. The data warehouse architecture that is being put in place would be the hub and spoke of the company.

The hub and spoke architecture is a design were a main data mart is created to house the cleansed data. The operation sources are connected to the main data mart and provide all the data regarding Lafluer’s operations. The new data is stored by subject matter in an atomic level with the minimum of third normal form (3NF). The data from the operational sources are then cleaned through the extract, transform, and load (ETL) process. To maintain a single true version of the data, separate data marts are then created to pull information for individual applications and/or departments.

As mentioned previously, the first piece that would need to be is a full analysis of the current operations to identify the data that needs to be collected and the best locations to collect it from are from the sources of each department. A determination of the data format would need to be made so the data in the main data mart is consistent. Lastly, the requirements would need to be established as to what each dependent data mart needs to house for each department.

Data Warehouse

Databases are designed around relational data, meaning data that is connected to other data by means of a relational method of primary and foreign keys, data warehouse technology are not setup under this method. Data warehouses are setup using a dimensional model, meaning the data is stored in fact tables of like information, usually be subject matter, and joined into a single dimensional table. Lafleur Trading Company is looking to get information based on sales and “increasing” their sales. With this information the company will better be able to decide what marketing works and then target the areas needed to increase their sales and their market share.

Below is a possible database table structure (University of Phoenix, n.d.).

The diagram shows the tables and relationship that will bind the fact tables and dimension table in regards to related data by subject.

SQL

To best aid the data marts, the entire architecture should be built in a SQL server. The tables can be created in a couple of different ways from typing the SQL procedures out in the SQL server interface to using the tools that are provided by simply filling out a form then saving the tables. The first item when creating the data mart is to create the actual database that will hold all the tables and ensuring to allocate enough room for growth. Once that is complete, then the tables can be created. The fact tables will need to be created first with their primary keys. Then the dimensional tables can be created linking all the primary keys from the fact tables to it.

ETL

Extract, transform, load (ETL) process can be done in several different ways to loading the data into the new data mart. One way is to extract the data from the source files into an excel file and then import the data into the proper tables. Another way would be to link the database servers and again import the data to the proper locations. The method that will be used here is the first method. The reason the first method will be used because the data that is in the data mart needs to be cleansed up and put into a single format. By using an Excel file, the data can be transformed into a common format and then loaded into the proper tables in the new data mart.

Each data marts can now be created to gather only the information that is needed for analysis or reports. These data marts will be created the same way the main data mart was created, the ETL process can be used by linking the servers because the data is already clean.

Conclusion

By combining the data from the operational sources of each department into a data warehouse, Lafluer Trading Company will be able to gather the information regarding the effectiveness of the sales much faster and reliably. This will allow Lafluer the ability to make the decisions needed to push the company in the right direction and gain a stronger hold on their perspective market.

References

University of Phoenix. (n.d.). Information Technology. Retrieved from

https://ecampus.phoenix.edu/secure/aapd/CIST/VOP/Business/Lafleur/intranet/information-technology/entity-relationship-diagram.asp