Step by Step guide for creating Vizubi report from AdventureWorksDW SQL tables

Note1: This guide assumes that you have access to SQL Server 2008R2 with the AdventureWorksDW database installed (you can download it here. In this tutorial we will be loading the “FactInternetSales” table and some of the related dimension tables to create a simple report.

Click here to see the data schema

Open Excel.
(1) Click on the Vizubi Tab in the ribbon bar.
(2) Click on the Edit button in the database group.
(3) This will open the Import Data window.

(1) Select Database as the Data Source Type.
(2) Click the Database Connection button to open the Database Connection window.
(3) Type “AdventureWorksDW” in the Name and Label box.
(4) Select Microsoft SQL Server from the dropdown menu.

Once you have created and saved this database connection you can reuse it by clicking on the browse button to the right of Database Connection.

(1) Use the Browse button “…” to open Data Link Properties window.
(2) The Provider tab will be active.
(3) Select Microsoft OLE DB Provider for SQL Server.
(4) Click on Next.

(1) Type a server name or click on the arrow to the right to select one from the dropdown window.
(2) Insert a userid and password if required.
(3) Check the box to the left of Allow saving password so that you can reuse the database connection without having to insert the password each time.
(4) Click on the arrow to the right and select AdventureWorksDW2008R2.
(5) Test the connection by clicking on the Test connection button.
(6) Click on OK.
(7) Click on OK in the Database Connection window to confirm and save the connection settings.

(1) First select the FactInternetSales table fom the dropdown menu to the right of Source Table box (you can only import one table at a time from databases).
(2) The name of the Source Table appears in the Friendly name text box and you can modify it if you like.
(3) Click on Next.

The Preview window shows the first 50 rows of data in line with the import parameters you defined in the previous step.

(1) You can change the format of the data in any column by clicking on the icon to the left of the column header (you can select from General, Number and Date and time).
In the OrderDateKey column change the type from Number to Date and time.
(2) You can choose not to import a column by clicking on the check box to the right of the column header.
(3) Click on Next.

In the Time dimensions window, Vizubi shows fields which were formatted as date or time. These fields can be used to analyze your data against time so that (for instance) you can automatically see sales for this year vs. last year. Vizubi will also automatically create slicers and panels used for filtering data for all time dimensions (you don’t need to import the DimTime table which contains all the time dimensions for the DW).

(1) During the import of some tables Vizubi automatically generates time dimensions. CTRL + click will deselect rows so that time dimension will not be created (you can also deselect or select all the rows by clicking on the appropriate buttons).
(2) You can change the name that will be displayed for time dimensions by clicking in the label column in the row of the time dimension you want to modify.
(3) Click on Next.

The Import Data window shows you all the parameters you set in the previous steps. In some cases you may want to skip the import step and merely save the import parameters to import the data at a later time (if for instance you want to wait until servers and networks are not busy).

(1) If you want to modify any of the parameters you set in the previous steps, click on the Back button.
(2) Click on Import to import the data.
(3) Click on Save to save the import parameters so that you can update the data at a later time if you wish.

(1) The active label FactInternetSales shows the data imported.
(2) The OrderDateKey table is the time dimension automatically generated by Vizubi.

Import the remaining tables (“DimCurrency”, “DimCustomer”, “DimGeography”, “DimProduct”, “DimProductCategory”, “DimProductSubcategory”, “DimPromotion” and “DimSalesTerritory”) from DB:
(1) Click on New Import in the New group of the ribbon.
(2) Select a new table ( e.g. DimCurrency) by clicking on the down arrow to the right of the Source Table box, and repeat the steps shown before.

Repeat this process for all the other tables.

(1) All the tables are loaded in the Vizubi Editor.
(2) Click on the right arrow to the right to view the remaining tables.

Vizubi automatically creates relationships between columns in different tables when the column names are the same. In some cases, these relationships should not be created because there is no relationship between the two columns (often this is because the two columns are in fact BOTH related to a third column) Alternately, there may be two tables with similar information (a customers table and a suppliers table) which have columns with the same name (in this case both might have a “postal_code” column) but are not related.

(1) Click on the Relationships button in the Navigation pane.
(2) Click on the relationship you want to delete (in our tutorial select the relationship between DimGeography and DimSalesTerritory called relationship_SalesTerritoryKey).
(3) Click on the Delete button in the ribbon.
(4) Click on OK to confirm.

Repeat this step to remove the relationships between DimPromotion and DimProduct (called relationship_End_Date and relationship_Start_Date) and the relationship between FactInternetSales and DimGeography ( called relationship_SalesTerritoryKey ).

Some columns (like those used to create relationships between tables) can be hidden because they are needed to create the relationship but will not be used in the analysis of data.

To hide a column:

(1) Click anywhere on the DimCurrency table. This activates the Columns Tool tab.
(2) Click on the Hide and Unhide button in the Column options group in the ribbon. The Hide and Unhide Columns window opens.
(3) Click on the flag in the box to the right of CurrencyKey.
(4) Click OK to confirm.

Now the CurrencyKey column is no longer visible in the DimCurrency table and will not be available as a field to put into reports.

Repeat this step for the other following columns:
- Table “DimCustomer”: columns “CustomerKey”, “GeographyKey”
- Table “DimGeography”: column “GeographyKey”
- Table “DimProduct”: columns “ProductKey”, “ProductSubcategoryKey”
- Table “DimProductCategory”: columns” ProductCategoryKey”, “ProductCategoryAlternateKey”
- Table “DimProductSubcategory”: column “ProductSubcategoryKey”, “ProductSubcategoryAlternateKey”
- Table “DimPromotion”: columns “PromotionKey” and “PromotionAlternateKey”
- Table “DimSalesTerritory”: columns “SalesTerritoryKey”, “SalesTerritoryAlternateKey”
- Table “DimTime”: column “TimeKey”
- Table “FactInternetSales”: columns”ProductKey”, “OrderDateKey”, “DueDateKey”, “ShipDateKey”, “CustomerKey”, “PromotionKey”, “CurrencyKey”, “SalesTerritoryKey”

Now save the database and go back to Excel by clicking on Switch to Workbook.

Once we have managed the relationships correctly we can create a simple report.

(1) Click on the upper part of the Report button and choose where to put the report.
(2) From the Field Section drag and drop SalesTerritoryCountry to the Row Labels pane and SalesAmount to the Values pane.
(3) The table shows total sales for each country.

Applying filters to the report is simple.

Suppose you want to filter data by gender and marital status.
(1) Drag and drop the two fields from the Field Section to the Report filter pane.
(2) Click on the Filter Data button in the Selections group of the ribbon. The Selections window opens.
(3) The two fields are available to be used as filters. Double click on one of them to open the related field window and select the values for which you want to filter.

Do the same for the EnglishProductCategoryName field from the DimProductCategory table and Year and Month from the OrderDateKey table (this is a time dimension table that Vizubi automatically created during the import phase).

Another way to manage filters is by using panels.
Panels are automatically generated during import by Vizubi and can also be created manually by the user to personalize the filters used in the report (see figure 1).

To learn more about Panels, see the following tutorials: How to create a panel, How to create a slicer and How to filter data.

Note1: This guide assumes that you have access to SQL Server 2008R2 with the AdventureWorksDW database installed (you can download it here. In this tutorial we will be loading the “FactInternetSales” table and some of the related dimension tables to create a simple report.

Click here to see the data schema

Open Excel.
(1) Click on the Vizubi Tab in the ribbon bar.
(2) Click on the Edit button in the database group.
(3) This will open the Import Data window.

(1) Select Database as the Data Source Type.
(2) Click the Database Connection button to open the Database Connection window.
(3) Type “AdventureWorksDW” in the Name and Label box.
(4) Select Microsoft SQL Server from the dropdown menu.

Once you have created and saved this database connection you can reuse it by clicking on the browse button to the right of Database Connection.

(1) Use the Browse button “…” to open Data Link Properties window.
(2) The Provider tab will be active.
(3) Select Microsoft OLE DB Provider for SQL Server.
(4) Click on Next.

(1) Type a server name or click on the arrow to the right to select one from the dropdown window.
(2) Insert a userid and password if required.
(3) Check the box to the left of Allow saving password so that you can reuse the database connection without having to insert the password each time.
(4) Click on the arrow to the right and select AdventureWorksDW2008R2.
(5) Test the connection by clicking on the Test connection button.
(6) Click on OK.
(7) Click on OK in the Database Connection window to confirm and save the connection settings.

(1) First select the FactInternetSales table fom the dropdown menu to the right of Source Table box (you can only import one table at a time from databases).
(2) The name of the Source Table appears in the Friendly name text box and you can modify it if you like.
(3) Click on Next.

The Preview window shows the first 50 rows of data in line with the import parameters you defined in the previous step.

(1) You can change the format of the data in any column by clicking on the icon to the left of the column header (you can select from General, Number and Date and time).
In the OrderDateKey column change the type from Number to Date and time.
(2) You can choose not to import a column by clicking on the check box to the right of the column header.
(3) Click on Next.

In the Time dimensions window, Vizubi shows fields which were formatted as date or time. These fields can be used to analyze your data against time so that (for instance) you can automatically see sales for this year vs. last year. Vizubi will also automatically create slicers and panels used for filtering data for all time dimensions (you don’t need to import the DimTime table which contains all the time dimensions for the DW).

(1) During the import of some tables Vizubi automatically generates time dimensions. CTRL + click will deselect rows so that time dimension will not be created (you can also deselect or select all the rows by clicking on the appropriate buttons).
(2) You can change the name that will be displayed for time dimensions by clicking in the label column in the row of the time dimension you want to modify.
(3) Click on Next.

The Import Data window shows you all the parameters you set in the previous steps. In some cases you may want to skip the import step and merely save the import parameters to import the data at a later time (if for instance you want to wait until servers and networks are not busy).

(1) If you want to modify any of the parameters you set in the previous steps, click on the Back button.
(2) Click on Import to import the data.
(3) Click on Save to save the import parameters so that you can update the data at a later time if you wish.

(1) The active label FactInternetSales shows the data imported.
(2) The OrderDateKey table is the time dimension automatically generated by Vizubi.

Import the remaining tables (“DimCurrency”, “DimCustomer”, “DimGeography”, “DimProduct”, “DimProductCategory”, “DimProductSubcategory”, “DimPromotion” and “DimSalesTerritory”) from DB:
(1) Click on New Import in the New group of the ribbon.
(2) Select a new table ( e.g. DimCurrency) by clicking on the down arrow to the right of the Source Table box, and repeat the steps shown before.

Repeat this process for all the other tables.

(1) All the tables are loaded in the Vizubi Editor.
(2) Click on the right arrow to the right to view the remaining tables.

Vizubi automatically creates relationships between columns in different tables when the column names are the same. In some cases, these relationships should not be created because there is no relationship between the two columns (often this is because the two columns are in fact BOTH related to a third column) Alternately, there may be two tables with similar information (a customers table and a suppliers table) which have columns with the same name (in this case both might have a “postal_code” column) but are not related.

(1) Click on the Relationships button in the Navigation pane.
(2) Click on the relationship you want to delete (in our tutorial select the relationship between DimGeography and DimSalesTerritory called relationship_SalesTerritoryKey).
(3) Click on the Delete button in the ribbon.
(4) Click on OK to confirm.

Repeat this step to remove the relationships between DimPromotion and DimProduct (called relationship_End_Date and relationship_Start_Date) and the relationship between FactInternetSales and DimGeography ( called relationship_SalesTerritoryKey ).

Some columns (like those used to create relationships between tables) can be hidden because they are needed to create the relationship but will not be used in the analysis of data.

To hide a column:

(1) Click anywhere on the DimCurrency table. This activates the Columns Tool tab.
(2) Click on the Hide and Unhide button in the Column options group in the ribbon. The Hide and Unhide Columns window opens.
(3) Click on the flag in the box to the right of CurrencyKey.
(4) Click OK to confirm.

Now the CurrencyKey column is no longer visible in the DimCurrency table and will not be available as a field to put into reports.

Repeat this step for the other following columns:
- Table “DimCustomer”: columns “CustomerKey”, “GeographyKey”
- Table “DimGeography”: column “GeographyKey”
- Table “DimProduct”: columns “ProductKey”, “ProductSubcategoryKey”
- Table “DimProductCategory”: columns” ProductCategoryKey”, “ProductCategoryAlternateKey”
- Table “DimProductSubcategory”: column “ProductSubcategoryKey”, “ProductSubcategoryAlternateKey”
- Table “DimPromotion”: columns “PromotionKey” and “PromotionAlternateKey”
- Table “DimSalesTerritory”: columns “SalesTerritoryKey”, “SalesTerritoryAlternateKey”
- Table “DimTime”: column “TimeKey”
- Table “FactInternetSales”: columns”ProductKey”, “OrderDateKey”, “DueDateKey”, “ShipDateKey”, “CustomerKey”, “PromotionKey”, “CurrencyKey”, “SalesTerritoryKey”

Now save the database and go back to Excel by clicking on Switch to Workbook.

Once we have managed the relationships correctly we can create a simple report.

(1) Click on the upper part of the Report button and choose where to put the report.
(2) From the Field Section drag and drop SalesTerritoryCountry to the Row Labels pane and SalesAmount to the Values pane.
(3) The table shows total sales for each country.

Applying filters to the report is simple.

Suppose you want to filter data by gender and marital status.
(1) Drag and drop the two fields from the Field Section to the Report filter pane.
(2) Click on the Filter Data button in the Selections group of the ribbon. The Selections window opens.
(3) The two fields are available to be used as filters. Double click on one of them to open the related field window and select the values for which you want to filter.

Do the same for the EnglishProductCategoryName field from the DimProductCategory table and Year and Month from the OrderDateKey table (this is a time dimension table that Vizubi automatically created during the import phase).

Another way to manage filters is by using panels.
Panels are automatically generated during import by Vizubi and can also be created manually by the user to personalize the filters used in the report (see figure 1).

To learn more about Panels, see the following tutorials: How to create a panel, How to create a slicer and How to filter data.