Data Warehousing & Visualization in Microsoft BI & Power BI

Explain what a relational database management system is and why Microsoft SQL Server is favored for business intelligence, due to integration with SQL Server services and its express edition.

Install SQL Server Express 2017 and SQL Server Management Studio by downloading from the official download center, following the basic install steps, and then learn to navigate SSMS.

Learn how to open SQL Server Management Studio, connect to a SQL Server instance with Windows authentication, navigate system databases, switch databases, and run a new query.

Explore the Microsoft business intelligence package, including integration surfaces for data extraction, reporting surfaces for insightful decision-making reports, and analysis surfaces for data mining within the Microsoft Visual Studio shell.

Install SQL Server Data Tools for business intelligence by downloading the BI tools for Visual Studio 2013, meeting 64-bit hardware requirements, and completing the setup and restart.

Discuss data types in Microsoft SQL Server, including strings char and varchar, numeric types bit tinyint int bigint float, date time, and identity.

begin a simple data cleaning project by organizing a three-folder workflow (original, prepared, load), clean a 71-company dataset, handle anomalous data, and remove dot separators.

Copy original file, convert it to txt, import into excel as comma-delimited csv, set text formats with text to columns, save as csv, and move to load folder for ETL.

Create a database in SQL Server Management Studio to serve as a data warehouse. Connect to the server, run create database with a meaningful name, and review the generated objects.

Create an SSIS project in Visual Studio Shell to implement an ETL process moving data from flat files to a SQL Server data warehouse.

Create the ssis project in ms visual studio shell for the etl process, map source to destination columns, save, and run to load data into the stage table.

Create a working table in ssms for data analysis by defining a new table from the stage table, setting correct data types, and loading data for analysis.

Examine a semicolon-delimited csv where Excel cannot split columns, revealing car sales by manufacturer, model, sale date, fuel efficiency, and price in thousands, with cleaning preview for the ADL process.

Clean and prepare a car sales data file in Excel by handling semicolon delimiters, converting dates, and removing currency signs, then save as csv for the data warehouse load file.

Create a data warehouse database named car sales using SQL Server Management Studio. Open a new query, run create database, and refresh to verify car sales appears.

Create a working table in SSMS for data analysis using used car sales, define the schema, insert data from the old table, and disable the data flow to prevent duplicates.

Clean the Boston crimes data in Excel by importing as a delimited CSV, parse the date time in month-day-year format, and set offense code as text for data warehouse prep.

Open SQL Server Management Studio, create a new database named Boston, execute the command, and refresh databases to confirm the warehouse setup for the FTL process.

Create an SSIS project in Microsoft Visual Studio Shell, set up a flat file source, and use a conditional split to separate valid records from invalid or anomalous data.

Identify anomalies in a csv-like flat file, reveal missing shooting data and misaligned columns caused by a comma delimiter and extra commas, and note seven records eliminated.

Create a working table in SSMS for data analysis by defining columns and data types from a template, then insert data from a flat file with conversion.

Remove extra comma from seven rows in flat file, save with a new name in a different folder, and create a data flow to transfer corrected rows to target table.

Solve the homework by cleaning a faulty comma-delimited file, importing it into Excel, restoring headers from the CSP file, and transferring seven rows via a flat file data flow.

Clean the movies dataset in MS Excel by converting a CSV to Excel, applying text formatting across columns, and preparing data for loading into a data warehouse for BI reporting.

Create a warehouse database in SSMS, set up an SSIS ETL project, configure a flat file source, map to a raw movies data table, load 5043 records and verify.

Create and configure a working table in SSMS for data analysis, validate data types, handle null values, and prepare data for reporting.

Clean and prepare a bank customer complaints dataset in Excel by importing a delimited file, configuring date formats, splitting columns, and saving as a csv for data warehousing and visualization.

Create the warehouse database in ssms and set up an etl ssis project to load data from a flat file into the bank customers complaints data warehouse.

Validate the transfer of 84,811 records, then create a fresh working table in SSMS from the raw data, drop the existing table, and define appropriate data types for quality assurance.

Insert data into the working table and identify a conversion error from non-numeric zip codes. Create a second table with a different data type for zip codes, using length 100.

Clean a Glassdoor employee review dataset and transfer it to a SQL Server data warehouse using Visual Studio for analysis of ratings and work balance.

Create and organize an SSRS project by using solution explorer, properties window, and view options to add a new report and name it, then explore the toolbox for report design.

Create a data source and dataset from a Microsoft SQL Server, query the work companies table to fetch all fields, and drag fields into a report table for preview.