Data Analytics for Business BISM7233

SSIS Task: Company_data.csv contains information for each of the companies, some of the state code information is missing in this table. You would need to use “state_code.csv” to fill in these blank cells under state code before creating the company dimension table. Additionally, sector and industry-related information is provided on “industry.csv” and “sector.csv”. “Bankruptcy_1.csv” and “Bankruptcy_2.csv” contain information with the details of the bankruptcy, which forms the fact table in the dimension model. Connect to the company dimension table you created in the last step to build the dimension model for this case study. In particular, the integrated data should allow the analyst to generate reports on factors that can analyze and understand the bankruptcy of the companies.

iuww520iuww520iuww520iuww520iuww520iuww520iuww520iuww520iuww520

1. Data transformations via SSIS

Please set up a data transformation process for the relevant source data tables (company.csv, state code.csv, industry.csv, sector.csv, bankruptcy_1.csv, bankruptcy_2.csv) and output the tables into csv files (you would need to submit the output CSV files for this section). Here is a brief description of the data:

• Financial data related to the companies is summarized in files “bankruptcy_1.csv” and “bankruptcy_2.csv”.
• Additional information about companies is available in “compay_csv”.
• State codes are given in “state_code.csv”.
• The industry to which the companies are classified is stored in “industry.csv”.
• A high-level industry sector classification is stored in “sector.csv”. A sector classifies different industries into a few industrial sectors. The sector code is derived from the first two digits of the industry code.

Please create data transformation process to export the data from CSV files and create:

1. Company Dimension table (contains all available attributes regarding the companies)
2. Time Dimension 
3. Fact table (contains all available measures with company dimension connecting to the fact table)

Please enable the data viewer that leads to each export of the two csv files and provide a screenshot of the data that you are going to export a) company dimension table b) Time Dimension, and c) Fact table. Each of your screenshot must have “green ticks” and “number of rows displayed”. A sample screenshot is shown below:

Note: When connecting to the CSV source files in SSIS, you might need to click “suggest data types” and increase the number of rows when suggesting data types to ensure the data types are detected correctly. Feel free to refer back to the tutorial exercise and make sure you know how to check and how to manually change the data types if needed.