Use SQL server data tools (SSDT), SQL Server Management Studio (SSMS), and Microsoft Power BI desktop to complete this homework. Refer Baltimore city’s Opendata portal or other dataset of your preference as data sources
ETL Exercise:
1. Develop an SSIS package to import a flat file into a database table.
● Download a sample CSV dataset of your choice from https://data.baltimorecity.gov.
● Use the CSV file as a data source while developing your SSIS package.
● Add at least three transformation steps such as formatting data types, concatenating, splitting columns, and conditional splits in to multiple outputs.
● Create a database called “ETL” in your development instance.
● Use ETL database as a destination to host the transformed data. You may use multiple tables as necessary based on the type of transformation made to the source data.
● Add a step in your package that truncates the target table/s before loading data every time the package is executed.
● Deploy your package to SSIS catalogue in your development instance.
● Submit the package file
Power BI Report
2. Use the dataset you imported under question 1 as a data source to develop a power BI report.
● Please use only less than 10k rows. Some datasets have huge number of records and may exceed the upload limits while submitting your files.
● Include at least 6 data visualizations.
● Include at least three filtering options.
● Configure Power BI report server.
● Deploy your report to the report server.
● Submit the .pbix file for the power BI report.