Creating Mash-ups of data from multiple source in Excel

VanArsdel is a company that manufactures and sells sporting goods. The company has offices in the United States (US) and several other countries. Its sales comprise of US sales and International sales. VanArsdel’s sales come from its owned manufactured products, as well as other manufacturers’ products. 

VanArsdel’s US office stores the sales data on a SQL Database on Microsoft Azure. This is a relational database service in the cloud based on the Microsoft SQL Server engine, with built-in, mission-critical capabilities. The following image shows the database diagram for the tables of your

Database Diagram

VanArsdel International sales transactions are available as comma separated (CSV) files. They could be generated daily, either manually by someone, or automatically by an automated process. They are available in a dedicated folder. These CSV files have the same column structure as the sales table for the US sales that comes from the SQL Database. 

You want to perform analysis on VanArsdel’s worldwide sales data for the year of 2015. You need to bring all these data into Excel before you can perform any analysis.

  1. In the first exercise, you will import data to Microsoft Excel from a SQL database on Azure. Once you have imported the data, you will explore existing table relationships and create a new one yourself.
  2. In the second exercise, you will import data from CSV files which resides in a file folder. You will append this new data to the corresponding existing data that comes from the SQL Database. 
  3. In the third exercise, you will create a Date table in the data model to be used for data analysis. 

1. Start with a blank Excel file.

2. Import the data from the SQL Database by creating a new Query.

Use the following information to connect to the SQL Database.

  • Server: msedxeus.database.windows.net
  • Database: DAT206x01
  • Login: ExcelLogin (remember to select database login)

Password: P@ssw0rd

IMPORTANT! If you cannot login, please try the following:

  • Ensure you are not behind a firewall.
  • Ensure you are using database login instead of windows login.
  • Try typing the server details and credentials instead of copy pasting.

Select the following tables to be imported: LocationsManufacturerProducts, and Sales (Hint: The Select multiple items option might be useful).

Edit the query before loading to the data model.

1. Filter the rows on the Sales table to include dates from January 1st, 2015. (Hint: filter Dates which are after 12/31/2014).

2. Name the Query “Sales”.

3. Load the data into Excel data model. This might take a few minutes.

4. Open the Excel data model and explore the existing table relationships in the Diagram View

You want to create a relationship between the Sales table and the Locations table. First, you merge the Country and Zip columns in both Sales and Locations table as a new column, CountryZip. Then, you create a relationship on the CountryZip column for both tables.

  1. Edit the Sales query in Query Editor.
  2. Create a CountryZip column in Sales table by merging the Country and Zip columns.
  3. Load the data into Excel data model. This might take a few minutes.
  4. Edit the Locations query in Query Editor.
  5. Create a CountryZip column in Locations table by merging the Country and Zip columns.
  6. Load the data into Excel data model. This might take a few minutes.
  7. Open the Excel data model.
  8. Link the newly created CountryZip column on the Sales table to the newly created CountryZip column on the Locations table. (Hint: Use the Diagram View).

Import Data from a Folder Containing CSV Files

  • The CSV file used in this lab is created using the English – US locale, which means the decimal separator is a period and the thousands separator is a comma, and dates are formatted in m/dd/yyyy format. If you are using local settings like German, French, or others, in which the decimal separator is a comma and the thousands separator is a period, Power Query (or Query in Excel 2016) will ignore the period and treats all digits as numbers. You can change the locale of your Power Query for just the workbook you are using. If you use Excel 2010 or 2013, you can see the locale settings in the Power Query tab. If you are using Excel 2016, it is under New Query / Query settings / Data Load.
  • You might be asked to confirm Privacy Levels when you append the International Sales query to the Sales query. You can read about privacy levels here. The article is talking about Power Query but the same apply to queries created by the get-and-transform functionality in Excel 2016. In short data can be public, organizational, or private and when the query mashes up different sources you may need to assign one of these privacy categories to each source so the query engine will know to send private data to a public source for a merge. If you assign Organizational to all the sources for the lab, you’ll be fine.
  1. Download the zip file containing VanArsdel’s international sales data and extract it to a folder. You should see 4 CSV files in the folder.
  2. Import the data from the file folder by creating a new Query. To do this, use the From File / From Folder option and select the folder where you saved the 4 CSV files containing VanArsdel’s international sales data.
  3. Perform the following steps:
    • Name the Query InternationalSales.
    • Filter out the records (rows) that come from the header of the CSV files. (Hint: Filter the Zip column where the row does not equal the text Zip. The filter is case sensitive so please type it in just as it appears here – Zip).
    • Filter the rows that are after 12/31/2014.
    • Create a CountryZip column by merging the Country and Zip columns.
  4. Create the connection from the query without loading to the data model.
  5. Edit the Sales Query from the previous exercise (SQL Database import).
  6. Append the InternationalSales Query to the Sales Query.
  7. Load the data into Excel data model. This might take a few minutes.
  8. Ensure all other relationships are intact, otherwise, correct them accordingly.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s