Tableau reports

Using Tableau Desktop to create basic reports.

In this blog post I am looking at creating a very basic report using Tableau.

We will use one of the sample excel sheets to create this basic report. I will provide a brief walk through with screenshots.

Tableau Desktop

This is the initial data menu. You have a vast list of database types that you can choose from. Tableau gives its own set of sample excel sheets that will use for this example.

Select Sample-superstore-Excel

We see that the dimensions and the measures show up on the left hand side.

Tableau gives you a platform to create a wide variety off reports. You can create bar graphs,scatter plots and it has some amazing visualization tools for geography enabled data as well.

Lets say we're interested in finding the customer segment information. Drag and drop the customer segment from the dimension to the "Rows" tab. You should get a table with the following information.

Lets analyze the sales across different states for the consumer segments. Drag and drop state to the "rows" and then drag and drop sales on the "columns" tab. The final result should look something like this.

You can play around with the visualizations and get Maps as well. Click on "Show me" and change the visualization type to maps.

Here's a map type report of the same data set.

Let's see if OBI delivers the same reporting experience.

Signing off !!!

Types of Dimensions

In the previous blog we looked at classifying facts and measures into different categories,this blog post is related to dimensions.

We will not be covering the different ways to preserve history in a dimension rather we will look at ways in which a dimension table structure is broadly classified.

• Conformed Dimension

Multiple keys are referenced from this dimension. These dimensions "conform" i.e they mean the same across different tables referencing them.For eg: Date dimension in a sales fact table must be the same as a date dimension connected to a product table

• Junk Dimension

A junk dimension is a grouping of attributes that might not be required in the fact table but are required for storage purposes. Rather than having these attributes in the main fact table it is easier to store them in a separate dimension and access them whenever required.

Typical junk dimensions will have flag attributes,text description and can be unrelated to each other.

• Degenerate Dimension

A degenerate dimension does not need a separate table to be modeled. It exists in the fact table and may often be the primary key of the fact table.

Common degenerate dimensions include transaction id numbers,invoice number,ticket numbers etc.

• Role-playing Dimension

 Dimensions that can be used multiple times in different contexts are called role playing dimensions. A date dimension is a common role playing dimension. For eg: When different dates(Date of Birth,Date of Joining..) are part of a single fact table,they play a role playing dimension.

Next post we'll look at how we can model some "Slowly Changing Dimensions" .

Its all about the Facts

Types of FACT tables

Fact tables can be classified broadly into 3 types:

• Transactional Fact Table

A transaction fact table gives you information "one row" at a time. This is a single specific event that happens in the transaction.This fact table has multiple dimensions associated with it. Example: Take an order receipt from your store. Every item that you purchase is recorded as a row in this transactional fact table.

• Periodic Snapshot Fact table

A periodic snapshot fact table as the name suggests are accessed at specific periods in time. These contain measures which might be semi additive.For example: Lets say that the store you purchase your groceries from needs an aggregated report of the daily sales that the shop generates.In such scenarios we utilize the Periodic Snapshot Fact table. This is done in scenarios where you need point in time data. Accessing bank statements on a quarterly basis can also be used as an example for periodic snapshot fact tables.

• Accumulating Fact Table

The accumulating fact table (also referred to as the accumulating snapshot fact table) is used to capture a series of events in a well defined process. For example: When a student enrolls into a University,there are a series of steps that have to take place for the student to be admitted. We can record these steps until the student is finally admitted to the university. There will be multiple date value columns to identify when different steps in the process were completed. We revisit the grain and keep updating it to show the different states.

On the same lines we can look at classifying measures as:

• Additive: Can be added across the different rows( example: Sum of Sales quantity)
• Non Additive: Measures that are stand alone and are only specific to a fact(example: Color of a product)
• Semi Additive: Measures that can be added across some dimensions(example: Adding sales quantity for a specific year to report yearly).

We'll look at dimensions in a later post..Cheers !