SQL-based Dashboards

Create Dashboards based on your SQL queries from various data sources.
A quick way to visualize your relational data from MS SQL Server or any other ODBC data source.
Subscribe to SQL dashboards and scheduled delivery, export values to the PDF file, run as a real-time data monitoring slideshow, embed analytics to your app, and many more options.

Create dashboards based on your SQL queries

Once you enter the SQL query into the Kyubit BI platform it can 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 a category item, while other columns (values) in the same row will present the 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 a 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 is designed to present a series of values. Such as 'Column Charts', 'Line Charts', 'Area Charts', 'Bar Charts', 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 a '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 'Any Data'

This query type can accept any value type for any column, as this query type can be displayed only on the 'Table' chart on the dashboard and it is not important for any column to be of numeric type.

Example 5 ...
When a query returns various text, dates, and any other non-numerical values, it could be displayed with the 'Table' chart.

Select 
p.EnglishProductName as 'Product Name',
p.ModelName as 'Model Name',
ps.EnglishProductSubcategoryName as 'Subcategory',
pc.EnglishProductCategoryName as 'Category',
Sum(f.SalesAmount) as 'Sales amount'

From 
FactInternetSales f left join DimProduct p on f.ProductKey = p.ProductKey
left join DimProductSubcategory ps on p.ProductSubcategoryKey = ps.ProductSubcategoryKey
left join DimProductCategory pc on ps.ProductCategoryKey = pc.ProductCategoryKey
left join DimDate d on d.DateKey = f.OrderDateKey
Where d.MonthNumberOfYear = 5

Group by 
p.EnglishProductName,
p.ModelName,
ps.EnglishProductSubcategoryName,
pc.EnglishProductCategoryName

Query Type 'Category-Series-Values'

This query type always expects 3 columns. The 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 a single column with this query type. Items in the first column (Categories) must be sorted in any order.

Example 6 ...
When it's 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

Query with Parameters for Dashboard Filtering

With these simple rules explained below, add parts of the query that will be included in the query execution only if the related parameter value is provided. In this way, we can write queries that will return values regardless if parameter values are provided or not.

Example 7 ...
If parameter @productName is provided (as dashboard filter), SQL part and EnglishProductName like '%@productName%' will be included in the query execution replacing @productName with the actual value. Any number of parameters could be included in the SQL query. This type of query always needs to include a 'Where' clause and for this reason 'Where 1=1' is included. All added parameters are added within curly brackets {} into the SQL query definition.

Select Top 200 
DimProduct.EnglishProductName, DimGeography.EnglishCountryRegionName, s.SalesAmount, s.ShipDate 
From [dbo].[FactInternetSales] s
Left join DimCustomer on s.CustomerKey = DimCustomer.CustomerKey
Left join DimGeography on DimCustomer.GeographyKey = DimGeography.GeographyKey
Left join DimProduct on DimProduct.ProductKey = s.ProductKey
Where 1=1

{@productName: and EnglishProductName like '%@productName%'}
{@shipDate: and s.ShipDate < '@shipDate' }
{@country: and EnglishCountryRegionName in (@country)}
{@salesAmount: and s.SalesAmount > @salesAmount}

order by s.SalesOrderNumber desc

An example with provided filter values for all defined parameters in the above query definition.

SQL Query for the Dashboard

When a filter with a parameter name is added to the dashboard, all dashboard charts that are based on the SQL query that accepts the same parameter name will be refreshed as soon as a new value is entered/selected for that filter.

Example 8 ...
Query as Stored Procedure with defined parameters

exec BrowseProducts @productName=null, @shipDate=null, @country=null, @salesAmount=null

Sample dashboard with charts/visualizations based on the SQL queries.

SQL Query Sample Dashboard