Vizubi AdventureWorks tutorial 1: create a Vizubi DB

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 Vizubi database.

Table structure relationships can be reviewed here.

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 “AdventureWorksDWR2″ 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 the 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 from the dropdown menu to the right of the Source Table text 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; rename it to Internet Sales.
(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. Insert “Order Date” in the label field on the first row.
(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) Click on Import to import the data.
(2) 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 Internet Sales shows the data imported (notice that the name of the table is the one you inserted in the Friendly Name box when importing the data).
(2) The Order Date table is the time dimension automatically generated by Vizubi. Click on the Order Date tab to see the time data which was generated automatically (notice that the name of the table is the one you inserted in the label field in the time dimensions window, when importing the data).

Import the remaining tables (“DimCurrency”, “DimCustomer”, “DimGeography”, “DimProduct”, “DimProductCategory”, “DimProductSubcategory”, “DimPromotion” and “DimSalesTerritory”) from DW:
(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. In some cases Vizubi will also create relationships between the table you are importing and tables you have already imported and the Define relationships window will also open. Just click next and create these relationships which we need to perform our analyses.

(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. Remove this kind of relationships is important to avoid circular relationships.
We have to remove relationships between:
a) FactInternetSales and DimGeography ( called relationship_SalesTerritoryKey ).
b) DimPromotion and DimProduct (called relationship_End_Date and relationship_Start_Date).
c) DimGeography and DimSalesTerritory (called relationship_SalesTerritoryKey).

(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 FactInternetSales and DimGeography called relationship_SalesTerritoryKey).
(3) Use CTRL+click to select the other relationships mentioned above (b and c)
(4) Click on the Delete button in the ribbon.
(5) Click on OK to confirm.

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 on the Tables button in the navigation pane on the left.
(2) Click anywhere on the DimCurrency table. This activates the Columns Tool tab.
(3) Click on the Hide and Unhide button in the Column options group in the ribbon. The Hide and Unhide Columns window opens.
(4) Click on the flag in the box to the right of CurrencyKey.
(5) 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”

(1) Click on the main menu (the frog) and click on Save As. Insert “Vizubi AdventureWorksDW” as the name and save the database.
(2) Click on Switch to Workbook to close the Vizubi editor and go back to Excel.

NB: Download the Vizubi database which is the output from the final tutorial in the series from here.

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 Vizubi database.

Table structure relationships can be reviewed here.

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 “AdventureWorksDWR2″ 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 the 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 from the dropdown menu to the right of the Source Table text 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; rename it to Internet Sales.
(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. Insert “Order Date” in the label field on the first row.
(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) Click on Import to import the data.
(2) 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 Internet Sales shows the data imported (notice that the name of the table is the one you inserted in the Friendly Name box when importing the data).
(2) The Order Date table is the time dimension automatically generated by Vizubi. Click on the Order Date tab to see the time data which was generated automatically (notice that the name of the table is the one you inserted in the label field in the time dimensions window, when importing the data).

Import the remaining tables (“DimCurrency”, “DimCustomer”, “DimGeography”, “DimProduct”, “DimProductCategory”, “DimProductSubcategory”, “DimPromotion” and “DimSalesTerritory”) from DW:
(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. In some cases Vizubi will also create relationships between the table you are importing and tables you have already imported and the Define relationships window will also open. Just click next and create these relationships which we need to perform our analyses.

(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. Remove this kind of relationships is important to avoid circular relationships.
We have to remove relationships between:
a) FactInternetSales and DimGeography ( called relationship_SalesTerritoryKey ).
b) DimPromotion and DimProduct (called relationship_End_Date and relationship_Start_Date).
c) DimGeography and DimSalesTerritory (called relationship_SalesTerritoryKey).

(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 FactInternetSales and DimGeography called relationship_SalesTerritoryKey).
(3) Use CTRL+click to select the other relationships mentioned above (b and c)
(4) Click on the Delete button in the ribbon.
(5) Click on OK to confirm.

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 on the Tables button in the navigation pane on the left.
(2) Click anywhere on the DimCurrency table. This activates the Columns Tool tab.
(3) Click on the Hide and Unhide button in the Column options group in the ribbon. The Hide and Unhide Columns window opens.
(4) Click on the flag in the box to the right of CurrencyKey.
(5) 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”

(1) Click on the main menu (the frog) and click on Save As. Insert “Vizubi AdventureWorksDW” as the name and save the database.
(2) Click on Switch to Workbook to close the Vizubi editor and go back to Excel.

NB: Download the Vizubi database which is the output from the final tutorial in the series from here.