SQL Based Dashboards

Create dashboards based on your SQL queries

Once you enter SQL query to Kyubit BI it could be reused and shared among authorized users. SQL queries could be visualized on the dashboard as dashboard charts and KPIs/Gauges.

Query Type 'Category-Values'

This query type simply expects the first column in a row to present category item, while other columns (values) in the same row will be present series of values on the chart for the category item. To be rendered as visualization charts, values columns (series) must be of numeric values.

Example 1 ...
In this example, SQL query results consist of category and one value column, which is convenient to present with Pie/Doughnut (or any other chart).

Select Top 12
DimProduct.EnglishProductName as 'Product Name', 
sum(FactInternetSales.SalesAmount) as 'Internet Sales'

From FactInternetSales 
	inner join DimProduct on 
	FactInternetSales.ProductKey = DimProduct.ProductKey

Group by DimProduct.EnglishProductName

Example 2 ...
When query results return more value columns, they need to be rendered as a series on the chart, which are designed to present a series of values. Such as 'Column Chart', 'Line Chart', 'Area Chart', 'Bar Chart', etc.

Select 
DimCustomer.EnglishEducation as 'Education', 
Sum(FactInternetSales.SalesAmount) as 'Internet Sales',
Sum(FactInternetSales.TaxAmt) as 'Tax Amount',
Sum(FactInternetSales.Freight) as 'Freight'

From FactInternetSales 
	inner join DimCustomer on 
	FactInternetSales.CustomerKey = DimCustomer.CustomerKey

Group by DimCustomer.EnglishEducation

Example 3 ...
If query results category items are geography countries, regions or cities, it is convenient to present the results with the Geo map chart on the dashboard.

Select Top 15
DimGeography.RegionCountryName as 'Country', Sum(FactOnlineSales.SalesAmount) as 'Sales'

From FactOnlineSales inner join DimCustomer on FactOnlineSales.CustomerKey = DimCustomer.CustomerKey
inner join DimGeography on DimCustomer.GeographyKey = DimGeography.GeographyKey

Group by DimGeography.RegionCountryName
Order by Sum(FactOnlineSales.SalesAmount) desc

Example 4 ...
Query results that return items ordered in chronological order, like dates, months, etc., are convenient to present with 'Line Chart' on the dashboard.

Select 
Right('0' + cast(DimDate.DayNumberOfMonth as varchar),2) as 'Month',
Sum(FactInternetSales.SalesAmount) as 'Internet Sales'

From FactInternetSales 
	inner join DimDate on 
	FactInternetSales.OrderDateKey = DimDate.DateKey

Group by Right('0' + cast(DimDate.DayNumberOfMonth as varchar),2) 
Order by Right('0' + cast(DimDate.DayNumberOfMonth as varchar),2) 

Query Type 'Category-Series-Values'

This query type always expects 3 columns. First presents category items, the second column is series items and the third column presents values. When it's not convenient to write the query where each series value is in a separate column, it is easier to write a query where all series items are in the single column with this query type. Items in the first column (Categries) must be sorted in any order.

Example 6 ...
When its easier to display series items in a single column, use this type of query with 3 columns.

Select 
DimCustomer.EnglishEducation as 'Education', 
DimCustomer.EnglishOccupation as 'Occupation', 
Sum(FactInternetSales.SalesAmount) as 'Internet Sales'

From FactInternetSales 
	inner join DimCustomer on 
	FactInternetSales.CustomerKey = DimCustomer.CustomerKey

Group by DimCustomer.EnglishEducation, DimCustomer.EnglishOccupation
Order by DimCustomer.EnglishEducation