Orange Belt Classes:

1. DataSelf Architecture

  1. Data warehouse: DataSelf ETL+ extracts the data from the source system(s) into a MS SQL Server data warehouse for a single version of the truth, data consolidation, ease of reporting and fast performance.

  2. Analytics: Streamlines the data sources for easier reporting design and distribution.

  3. Actionable Insights: Preconfigured views and dashboards provide value on day one, as well as allow users to easily perform ad hoc analysis and create new views.

The DataSelf Value Proposition:

2. Sign in to Training Server

(self-service training only)

  1. Go to https://bi1.dataself.com

  2. Sign in with:

    1. User: orange

    2. Pw: DataSelf1!

  3. If prompted to Select a Site, select training_orange.

3. Opening the Workbook Editor

Self-service: See the animation below (no audio).

  1. Depending on where you are on the DataSelf portal:

    1. Click the Explore path link in the top left of the black banner.

      1. Click New button → Workbook.

    2. Or click the Home icon on the left.

      1. Click New button → Workbook.

  2. Select a data source to work with. For training, select _Sales_Invoice.

4. Workbook Editor Main Areas

  1. Main areas of the Workbook editor:

    1. Data source for workbook: (additional data sources can be added later).

    2. Data structures for reporting:

      1. Dimensions (top section): groupings for row and column headers. Ex.: Customer.

      2. Measures (bottom section): Aggregations for Worksheets. Ex.: Qty Sold.

    3. Workbook area.

    4. Sets the Workbook’s Rows and Columns.

    5. Sets the Workbook’s Filters.

    6. Sets the Workbook's Marks.

Tableau Help Link: Edit Tableau Views on the Web

5. Examples of data source schemas

This section is for techies interested in taking a peek at DataSelf' back-end.

The star schemas shown below are examples of Sales data feeding the Workbook Editor’s Data panel. Editing the star schemas requires user licenses for DataSelf Tableau OEM Desktop and MS SQL. However, addition of fields to tables already existing in star schemas only require editing the tables DataSelf ETL+.

Acumatica

NetSuite

Sage 100

Sage 300

Sage X3

6. Drag and Drop, creating a basic view

Self-service: See the animation below (no audio).

How to create a view showing Sales by Customer. (See step 3 on how to create a new workbook)

  1. From the left panel, drag the Customer dimension into Rows.

  2. From the bottom of the left panel, either double click on the Sales measure to let Tableau auto-add it or drag the Sales measure into the Abc area. The Abc indicates you are displaying a numeric measure value as text rather than as color or size.

  3. The Sales by Customer view is done!

  4. To undo steps, clear the sheet or start a new sheet:

    1. To undo your steps, use the Undo/Redo icons on the top left of the Workbook editor.

    2. Use the Clear Sheet icon to erase all settings on the sheet.

    3. To start a new blank sheet, click on the New Worksheet Icon.

Hands-on Exercises

  1. Click on the New Worksheet tab icon and create a view showing Sales by Salesperson.

  2. Click on the New Worksheet tab icon and create a view showing Qty Sold by Item.

  3. Add Sales to the Qty Sold by Item view to display multiple measures.
    Note that the measure column headers automatically appear when multiple measures are displayed.

Tip: How to see a column header for only one measure

As you noticed above, when only one measure is selected, there is no column header (measure name), but when a second measure is added the measure names field is automatically added to the columns or rows, and you see column headers.

You can manually do the same process to see a column for only one measure. There are two methods.

A. Tableau Help Link: The One-Click Trick to Creating Headers for Single-Measure Tables (tableau.com)
Drag your measure to the Abc section of the display area, then drag the Measure Values field also to the Abc section of the display area.

B. Tableau Help Links: Measure Values and Measure Names - Tableau,
Drag the Measure Names dimension to the columns section.
Drag the Measure Values measure to the Text marker in the Marks area.
This adds all the measure values to the Measure Values area and the Measure Names dimension to the Filters area. Edit the Filter to select only the measure name you want to display.

7. Publishing (Saving your work)

Saving/Publishing applies to the the whole workbook. Workbooks are like Excel files, containing one or more sheets (also call tabs or views), which are like Excel tabs/sheets.

  1. Click File Publish As. Or use the Publish icon in the upper right corner.

  2. Name the workbook. For this training course, user your name with a number suffix. i.e., “Pat Smith 2”

  3. Check Show sheets as tabs (You can change this setting after the fact. You’ll see later what it does).

  4. Click Publish.

  5. Options to save next times:

    1. File → Publish

    2. Click the Publish icon in the top right

    3. Ctrl+S.

Exiting the Edit Mode

  • Click File → Close

  • Or click the white X on the top right of the black ribbon.

Returning to the Edit Mode

  1. From a worksheet or dashboard tab, click the Edit icon on the top left icon menu.

  2. From the list of workbooks or views, click the '' icon → Edit Workbook.

8. Filters

Self Service: See the animation below (no audio).

Filters control which records are included or excluded based on the value of a dimension

Tableau help link: Filter Data from Your Views - Tableau

Create a new worksheet showing Sales by Item and add filters for years and items.
Or modify the existing worksheet from the previous exercise.
Select only the last 2 years.

  1. Add the Item dimension to the Rows, and Sales as a text measure

  2. Drag Inv Date to the Columns (top center section). Notice it automatically groups it by Year.

  3. Right-click the YEAR(Inv Date) dimension field on the Columns. Select the Show Filter property.

  4. In the Year of Inv Date filter on the right, unselect All, then check the boxes for the last two years.

  5. Right-click the Item dimension field on the Rows and select Show Filter.

  6. In the Item filter on the right panel, unselect Null.

  7. Rename the sheet. Right-click Sheet 1 and rename it to Sales by Item by Year.

  8. Publish the workbook by clicking File Publish As or click the Publish icon in the upper right.

  9. You can also drag dimensions and measures directly from the Data panel to the Filters shelf (center panel). This method often shows more Filter options. But it does not automatically show the filter on the right.

    1. Drag Salesperson to the Filters shelf, then right click to show filter and select/unselect some of its members on the right panel.

Hands-on Exercises (optional)

Create a new worksheet showing Sales by Customer for the last two years.

Method 1: Right-click the bottom left tab → New Worksheet to create the new worksheet.

Method 2: Right-click the existing sheet on the bottom left and select Duplicate

Drag the Item Class dimension on top of the Customer dimension in the Rows.

The red down arrow indicates it is inserting the field before or after an existing field.

If there is no red down arrow, then when you let up on the mouse button it will overwrite the field.

Exclude the NULL Item Class value.
Method 1: Right-click the Item Class dimension in the Rows. Select Show Filters, then unselect Nulls on the filter card.

Method 2: Click on the NULL Item Class Value, select Exclude from the shortcut menu.

9. Drag and Drop: Multiple Dimensions

Self Service: See the animation below (no audio).

Tableau Help Link: Dimensions and Measures Intro (tableau.com)

Dimensions define how records are sorted and grouped, and how measures are subtotaled (aggregated).
Multiple Dimensions form a grouping hierarchy based on the order they display in the Rows or Columns.
The last Dimension shown is called the lowest level of detail, which is the level of the measure total.

Create a new worksheet showing Sales by Item Cat, Item and Customer in the last 2 years.
Or modify the existing workbook sheets from previous exercises.

  1. Create a new worksheet and insert Item, Item Cat, and Customer on Rows.

  2. Sizing Columns: Drag Item Cat & Customer row column lines to change widths.
    TIP:
    For Dimensions select the column line within in the body of the rows.
    For Measures select the column line in the header area of the rows.

  3. Rearranging Row Field Order: note how subtotals and row totals change.
    On Rows, drag Customer to the middle of the fields (note the red down arrow when dragging and dropping fields - the down arrow indicates where the field will be inserted). Drag Customer to the far left.

Hands-on Exercise (optional)

  1. Create a new worksheet showing Sales by Warehouse, Customer, and Item for the last two years.

  2. Filter out Item Nulls.

  3. Adjust the row header widths as needed.

10. Grand Totals and Subtotals

Self Service: See the animation below (no audio).

Create a new worksheet showing Sales by ShipTo Country and ShipTo State. Add totals and subtotals.

  1. Click the Sigma icon → Show Column Grand Totals.
    TIP: Click the Sigma → Column Totals to Top.

  2. Expand to ShipTo State by clicking the + icon on the ShipTo Country on Rows or Workbook row header.

  3. Click the Sigma icon → Add All Subtotals.

This process works the same way for adding totals and subtotals to columns and/or rows.

TIP: Add All Subtotals only adds subtotals for existing groups. If you add additional groups after the fact, they will not automatically have subtotals. This can be a handy feature if you only want certain dimensions subtotaled.

Hands-on Exercises (optional)

Add Inv Date to the columns on the Worksheet above, filter it for the past 2 years, expand to quarters (hover mouse over year and click '+' to expand), filter for quarters 1 and 2, Add All Subtotals to see Totals by year. Show Row Grand Totals see the total for all years.

11 Sorting

Each Dimension can be sorted within the higher-level Dimension that it is grouped.

Tableau Help Link: Sort Data in a Visualization - Tableau

  1. Create a new worksheet showing Sales by Customer in the last 2 years, then Show Row Grand Totals.

  2. Row Grand Total Sorting: click the Sort icon on the Grand Total column header. Select Descending.

  3. Sorting by Column headers:

    1. Hover the mouse over the column header and click the Sort icon that pops up on the far right.

    2. The first click sorts descending for measures and alphabetical for dimensions. Clicking a second time reverses the sort, the third click resets the sort to data order.

Hands-on Exercise (optional)

On the last worksheet above, how are the top 10 customers by sales last year doing this year?

When done publish your workbook.

12. Dashboards

A Dashboard displays multiple worksheets (dashboard objects) within that workbook in one view.
You can add special actions to make the different dashboard objects to make them interactive.

How to create a Dashboard combining a Sales by Salesperson worksheet with a Sales by Customer worksheet as an interactive dashboard.

  1. Create a Worksheet showing Sales by Salesperson for the last two years.

  2. Create another Worksheet showing Sales by Customer for the last two years.

  3. Create a new Dashboard sheet. On any sheet name, right-click → New Dashboard. Or click on the 2nd new sheet icon.

  4. On the Dashboard Sheet, in the top left, select Size → Fixed Size → Automatic.

  5. In the left panel, drag the Sales by Salesperson worksheet into the Add sheets here area.

  6. On the left panel, drag the Sales by Customer worksheet into the left of the dashboard area to insert it before the Sales by Rep.

  7. Note the dashboard objects auto fit themselves on the sheet due to the Tiled property setting in the lower left corner

  8. Adjust the dashboard object borders as desired.

  9. Note the filters shown on the worksheets were automatically added to the Dashboards.
    Select values on the filters and notice they only affect one object on the dashboard.

  10. Making filters affect the whole workbook:

    1. Select a filter, click the arrow down button on the top right (or left) of the gray box around the filter, and select Apply to Worksheets → All Using This Data Source.

      Change the filter selections and see how they affect the whole dashboard.

    2. Remove duplicated filters by selecting them, and then clicking the X on the top right (or left) of the gray box around the filter.

  11. Using dashboard object Action Filters:

    1. Click on the Sales by Rep dashboard object.

    2. On the top corner of the gray box around the object, click the Use as Filter icon.

      Click on data points within the Sales by Rep object and see that it selects only related data in the other dashboard objects.


Hands-on Exercise (optional)

Build the dashboard below. Summarized steps:

  • Create a worksheet for Sales & Qty Sold by Warehouse in 2022. Add Column Grand Totals at the top.

  • Duplicate the worksheet, replace the Warehouse field with the Item Cat and Item fields.
    Rename the worksheet to Sales & Qty Sold by Item Cat and Item.

  • Duplicate the worksheet, make it Sales & Qty Sold by Cust Cat. Add a filter for Salesperson.

  • Create a dashboard, set it to automatics size, arrange objects as shown below.

  • Set filters to apply to all Worksheets and remove duplicate filters, (such as duplicated Year of Inv Date filters).

    • Format the Salesperson filter to a Single Value (dropdown): click on the filter, then on the down arrow in the top right or left, select Single Value (dropdown).

  • Set all dashboard objects to Use as Filter: Click on a dashboard object to highlight the gray frame. Click on the Filter icon. This is called an Action Filter.

The filter funnel icon action filter

Interact with the dashboard as shown below.

Click on a row in the Warehouse view to highlight it. Note the other views show only related data.

Next: Orange Belt: Report Design Part 2.