Learn BI concepts with Kyubit
Welcome to the 60-minutes Kyubit
BI tools video tutorial! You will be introduced to Kyubit BI features and how to use it with your own data (OLAP, Tabular, SQL, Excel, or CSV).
We advise you to watch the video from start to end, as many explained concepts are related and depend on actions and objects previously created. However, you can also jump
from topic to topic using below-listed video 'chapters' structure. To follow the tutorial by doing the same tutorial actions on your side, please download sample sales data
sales.csv that is used in the video to create the sample sales BI environment.
Tutorial Structure
00:00 Tutorial introduction
01:50 Creating an 'Analytic Model'
05:16 Creating an analysis
07:53 Create a group of simple analyses
09:57 Create a dashboard
14:02 KPIs
17:47 Dashboard filters
18:48 Dashboard analytics
22:08 Dashboard dynamic text/HTML values
24:49 Chart data caching
25:53 Dashboard slides
26:29 Sharing and permissions
27:54 Scheduled 'Analytic Model' updates
30:56 Dashboard subscriptions
32:01 Dashboard with a context
37:31 URL filtering
38:36 Create relations between dashboards
41:44 Drill-through actions
44:27 Hierarchical dashboard charts
48:10 Calculated Measures
50:33 User Properties (Personalized dashboard data for the current user)
52:54 More about analysis and report features
55:39 Decomposition Analysis
58:12 Scorecards
59:24 Multi-page reports
1:00:24 Mobile devices view
1:02:46 Conclusion
Tutorial Texts
In case you need the tutorial to be translated into your language, please find the complete tutorial text below divided by the topics:
00:00 Tutorial introduction
Hi!
Welcome to the Kyubit BI
self-service analytics and dashboards video tutorial. We will lead you step by step in creating your BI environment and explain all concepts required to create a meaningful site for your business data insights and analysis that you’ll share with other authorized users.
The goal of the BI environment is to create a series of reports, dashboards, and KPIs that will cover various aspects of your business and present them to end users in a simple and comprehensive way so that they can instantly find relevant information and evaluate their business goals. A very important aspect of a BI environment is the ability to explore and discover details not immediately visible.
So what does it take to start creating your BI environment?
You need data that contain relevant business information that will be used to create analyses and dashboards.
What formats of data can be used?
The first option is to use your OLAP/Tabular databases which have data prepared for multidimensional analysis that you could quickly use with Kyubit analytics, reports, and dashboard features.
In this video tutorial, we will assume that you only have your data in some kind of an SQL database, Excel, or CSV files and you don't have any business intelligence experience. Let's see how quickly we can create useful data insights and analytics. These
self-service BI tools lets you quickly prepare and organize your data into multidimensional analytic models with no special tools or skills so that they could be used in drag-and-drop analytics and dashboards. In the description of this video tutorial you will find a link to downloading the resources to help you follow the tutorial with exactly the same steps and data on your side.
01:50 Creating an 'Analytic Model'
Let's assume that you have your sales data organized in rows and columns, where each row presents a single sale and the columns present some additional attributes about that sale.
This table of data could be stored in your Excel/CSV file or your relational SQL database where you would need to write an SQL query that returns the same data structure.
To follow this tutorial and repeat the steps in this video tutorial on your site please download the CSV file sample data available as a link in the video description.
We will start with an empty Kyubit BI application that has no existing objects or defined data and create our BI environment from scratch.
The first step is to create a Data Source. In this tutorial this will be an Analytic Model. To do that, open the Data Source section and select New Analytic Model or go to the home page, click on the Create button, and select the New Analytic Model item on the menu.
Name your new Analytic Model and save it. Upload your CSV file or set the SQL query that returns the data. If the Analytic Model is based on the SQL query, creation of a previous SQL or ODBC data source related to this SQL query is required.
The next step is to define the role of each column in the new analytic model. We will start by defining the columns as Measures or Dimensions. The Measure is always a numeric column that represents that values we can aggregate, count, or do other arithmetic operations with.
The Dimensions are additional attributes related to the Measure values and are used to break down measure values to some other level of analytical significance.
In this example the Sales Amount and Product Cost columns are defined as Measures, while other columns are defined as Dimensions. We will exclude the last four columns from the model and explain their usage later.
We have columns related to the sold products: Product name, Subcategory, and Category.
Three columns related to the date and 5 columns related to the customer: Gender, Country, Region, City, and Customer type.
If the column contains actual date value and we want to use it as a date filter, it must be defined as a Date.
We will add the dimension attributes that logically form a hierarchy, such as Year-Month-Date, to a Hierarchy that will make the analysis more intuitive to drill down data from higher to lower levels of data granularity.
We will also create a hierarchy for the Country-Region-City and Product category-Subcategory-Product name dimension attributes.
So let's start with this simple data set and create an analytic model based on this definition.
These data contain more than 120,000 rows and will be quickly processed.
The multidimensional analytic model data source has been created. Let's see what we can do with it.
05:16 Creating an analysis
Once you have an analytic model or OLAP cube, choose a data source and
create new data analytics.
The analysis is a base object which we will use while designing our BI environment and we'll create a series of analysis objects to create dashboards, KPIs, and reports, and organize them into a meaningful connected whole.
A grid analysis will display the tree of all available dimensions and measures that we could use to construct the analytic insights.
Drag and drop a measure (Sales amount) to the grid area. The analysis will immediately display the sum of all sales amounts we have in our model. To get the analysis on how the sales amount is distributed over product categories simply drag and drop the Product category dimension attribute on the category axis. The Product category is organized as a hierarchy and the expand icon offers to
drill down data analytics to the lower levels of the hierarchy, but we could also use other levels for the drilldown by dragging and dropping any dimension attribute to the category axis. To remove added dimension levels, drag and drop them to the left on the selection tree. In this example we will add the Country and Year dimension attributes to the category axis. Expanding product categories will now display values for countries and years. It could be any other combination of dimension attributes that would give us results of our interest. We will add the Customer type dimension attribute to the series axis, and we will also add the Product cost measure to the analysis. Now we have an analysis that could be quickly drilled down, always giving an insight into Sales amount and Product cost measures distributed by the dimensions on the category and series axis. If you want to see only specific elements of the level and not all of them, add a Level filter to reduce the level items display by setting the criteria. We will add the Top 5 criteria to display only the top 5 countries by the Sales amount measure in this example. To add any number of filters that would be automatically applied to all the analysis drag and drop dimensions into the filter area and set the filters to slice the whole grid view. The analytic model date dimension attribute offers a great way to quickly select a fixed or relative date range that would slice the data of the analysis using the selected date period.
Our first analysis provides certain insights into the sales from our data and we can continue to explore the data with various combinations of dimensions. Now we could save the analysis, open it as a report, share it, download it as a pdf or an Excel file, or subscribe to receive this analysis with fresh data at a scheduled time.
07:53 Create a group of simply analyses
However, in this video we will focus on how to create a series of simple analyses which provide a great way to visualize, slice, and analyze data and get the most usable insights from our sales data when we combine them on the dashboards. The goal is to prepare several
analytical dashboards for authorized end users who will use the dashboards to monitor and analyze the most interesting sales data from higher-level insights to more detailed views on particular subjects. Each
dashboard visualization could be based on an analysis or query. So in this tutorial we will create a series of very simple analyses that we will use in dashboards later.
The first analysis contains only the Sales amount as a measure and Product categories of the categories. This analysis simply shows the sales we had in the Product subcategory. We will save it as Sales by category.
The second analysis includes the Product subcategory dimension of the categories. We will add a Level filter on this dimension to display only the top 10 items. We will add the Customer type to the series axis. We will save this as Sales by subcategory and customer type. In the third analysis we will add Product subcategory with top 15 items, but also add the Customer type dimension to the filters and select the Person element to isolate all sales in our data that are related to the internet sales. We will save this item as Internet sales by subcategory. The fourth analysis contains only the Countries dimension attribute and Sales amount as the measure, while the fifth analysis contains only the Cities related to the sales. The last analysis for our first dashboard describes the Sales amount measure over the Year-Month dimension attribute and gives an insight into how our sales changes in time. We will save this item as Sales by month.
Now we have prepared 6 analyses that display aggregates by the most interesting dimensions in our Analytic Model and they will be a great starting point for our first Dashboard.
The goal of the first dashboard is to be an overview of general facts about the sales of our products, considering time, geography, and product categories.
To create a new Dashboard from the Home or Dashboard application section, click on the New Item button and choose New dashboard.
To display the sales by the Product categories dimension we will add a TreeMap chart which is ideal to display proportions related to sold items and the sales amount values.
Drag and drop the TreeMap chart to the preferred position and you can even resize it by clicking and dragging its lower right corner.
To connect the chart with the data (previously created analysis or query), click the edit icon in the tile upper right corner which will open the edit dialog form. On the second tab click on the data-picker and choose the existing analysis or query that will be the base for this visualization. Here we will choose the Sales by category analysis. Click the OK button on the edit form tile and you will have the first chart on the dashboard displaying data from our first analysis. It gives an insight into the sales volume by each of the product categories. Go to the edit form tile again and under the Options tab set Value labels to All-Short, which controls the display of the value labels on the chart bars and segments. In this case we have defined that all items on the chart will have value labels and that values will be presented in the short form to save the chart visual space.
Next we will add a column chart, resize it to 440x330, and connect it with our second analysis - the Sales by subcategory and customer type. We will set the Value labels property to All-Short again and press the OK button.
The column chart visualizes the product subcategories with an insight into the Customer type series. We immediately see that the impact of the sales related to Companies is much higher than that of sales related to Persons.
The current dashboard area is not sufficient to display all the charts we had in mind for this dashboard, so we will resize the dashboard area by selecting one of the area size options with the upper right Size button and choose 1,200px. The third dashboard chart will focus on Internet sales, so we will add a pie chart to the previous chart, resize it to 400x330, and connect it with the Internet sales by subcategory analysis. Set the Value labels to All-Short again.
Then we will add a Geo map chart to the left below the existing charts and resize it to 510x410. In the tile edit form we will select the Data tab and set the Geo map type to World. We will connect this geo map to the Sales by countries analysis. This chart gives a geographical insight into our sales data. When using geo maps, we could connect any geo map for any country or region. The only important thing is that the names of the geo map regions/countries match the items in our analysis or query. We will again open the edit form tile and under the Options tab select geo locations input to Sales by cities analysis. When displaying geolocations, we can choose what colors to apply to render high-to-low values, as well as what radius to choose for high-to-low values for the same or another measure. Geolocations are displayed as circles that visualize the impact of the data for particular geolocations, typically cities, but it could be any other location of significance. Use the administration section of the application to manage existing geolocations in the system.
To overview the sales over the time dimension we will add the line chart to the right of the geo map and connect it with the Sales by month analysis. We will immediately see the sales performance visualized in time and a flashing point for the performance of the last month. We will save the work on the dashboard and name the dashboard Sales overview.
We have added several charts to the dashboard. They give a general insight into the sales of our products, but we do not clearly understand from the charts whether our sales are successful.
Whenever you can define thresholds for the success or failure of your business goals, you can prepare the Key Performance Indicators that would instantly tell you whether your business goal is on a path to success.
Create a new KPI object in the KPI section of the application or on the dashboard. The KPI requires thresholds which will be evaluated at the runtime to visualize the KPI status. Each threshold could be defined as a static number, but also as an existing analysis or query that will return the value for the KPI threshold. The first value from the analysis or query is taken as a relevant value when connecting to the KPI threshold.
For our sales data in this tutorial we will create a KPI that evaluates our sales for the last day and tells us whether we are successful.
First we need to create an analysis (or query) that will return the data where the most recent values are at the top and the oldest values at the bottom.
We measure the Sales amount which we will add to the analysis. Then we will define the filter that will slice the data for the last 30 days, and finally we will add granularity of the categories. In this case it is the date, as we prefer to evaluate the KPI for the last date, but it could be the month, quarter, year, or any other granularity we prefer for some other KPI. Now this analysis in the top cell returns the data for the last day of our sales, which is relevant for the KPI value, while other cells in this analysis present historical data of the sales when looking at the KPI. Save the created analysis as Sales last 30 days.
We can now create the KPI and connect the KPI value to the created analysis. We’ll add the fixed value of 20,000 for the success threshold, and define the fail threshold as 10,000. We could also have defined the success and fail thresholds to take the relevant value from the query or analysis.
The KPI success models could be the 'Higher-is-better' model which is most often used, but also 'Lower-is-better' or the 'Middle Range Success', where the success is higher if the KPI value falls closer to the targeted range of values defined by the thresholds. Because the KPI value and thresholds could be defined from any data, we can design KPIs for various business situations if we combine them with a different success model.
Test the KPI with the current values to see how it evaluates. Notice that the first value from the analysis is evaluated as a KPI value, while the second value is taken as the previous value which is important to calculate the 'Last change' value. In our case it reflects the change in the sales amount from the day before. Optionally select the chart that would visualize recent history values using the rest of the rows from the analysis, which helps the end user to get the insight and trend of recent value changes.
Using the same principles we will create the Internet sales last 30 days and add both KPIs to our sales dashboard.
Now we can see that the sales overall are successful today, while internet sales have failed to reach the goal. So when the end user opens this dashboard on any day in future, he will immediately see that the recent sales follow business goals or perhaps there is concern that requires attention or even reaction on the business side.
17:47 Dashboard filters
We have added all visualizations for our first sales dashboard, but the dashboard now shows all the data from the last 3 years of sales (which is too much) and we would prefer to see the sales overview data for the last year.
Here we will use the dashboard filters to slice the dashboard data to the required view. In this example we will add a filter that relates to the Year dimension attribute and we will select the preferred year.
When the dashboard filter is applied, it slices all the charts on the dashboard that are related to the same data source as the dashboard filter. We could add multiple years or any number of filters for other dimension attribute filtering. The filter panel size and visibility are saved along with the dashboard and reopen with the same layout the next time this dashboard is opened. If required, we could manage each chart individually not to accept the dashboard filters or to accept only particular filters. For now we will only add the year 2019 to the dashboard filter in our Sales overview dashboard.
Each dashboard chart could be expanded to the full screen view and give you more focus when presenting the chart data.
The expanded full screen view includes all analytic and data export options.
When looking at the chart data, there is often a requirement to see more details of the observed values. Kyubit BI gives you several ways to dig further and drill the data to the bottom of your interest.
The Quick explore option gives the user the ability to click on any chart point from the dashboard and see the point values distributed over another dimension attribute. On the first chart Sales by category we will set the Quick explore to the Country dimension, so every time a user clicks on the category on the initial chart a small pie chart will be opened visualizing the same category sales over the country dimension. We will add the Year-Month quick explore dimension On the geo map and choose the Line chart for the quick explore action. Now anytime a user clicks on a Product category a small pie chart will display how the same value is distributed over the months. If we click on a particular country, a small line chart will be visualizing the sales in that country over the months and get insights into the sales patterns and trends in our data.
A dashboard user can set a chart to act as a data slicer. As soon as a user clicks on the chart point in this chart mode, all other charts of the same data source on the dashboard will be automatically sliced with the selected point.
Selecting the TV and video and Cell phones in the Sales by category chart would immediately slice the values in other charts based on the same selection and show us the sales data for the particular elements of the product category. It can work any way around and be used from other charts to slice the data in numerous ways to get insights that we prefer. For example, we can quickly see the sales amount over the months for the Australia, India, and China selection combined. Be aware that the dashboard filters are always applied on all the data we see on the dashboard if they're not manually managed on the chart level.
Click on the filter icon to exit the Chart as slicer mode.
All charts based on the Analytic Model (or OLAP data) support in-place drilldown and drill-by actions. With a right-click on the mouse a user can drill the data to the lower level of hierarchy or select a dimension attribute to be used to drill the current chart data. Because in our case the product subcategory is a child of the product category in the defined hierarchy, the drilldown action on the Home appliances category will show its subcategories and sales amounts for them.
We could also explicitly drill by a particular dimension attribute, such as the City, which would present Home appliances sales amount distribution over the cities. This is even better visualized when opened in the Expanded view chart.
The drilldown could be applied in multiple steps using multiple dimension attributes and reversed to the previous view (drillup) any time by clicking on the return icon at the top.
22:08 Dashboard dynamic text/HTML values
Finally, to finish our Sales overview dashboard layout we will add an informative dashboard header and footer that will describe what this dashboard is about and provide important insights and conclusions that an end user can read without even looking at the dashboard charts. We will create additional analyses for our Sales overview dashboard that list the top Cities, Weekdays, and Products by the sales amount measure. Go to the dashboard design view, details, and Header/Footer tab. Here we can set the text or HTML we prefer for the dashboard header and footer. Additional magic is done by inserting dynamic values from an existing query or analysis. Using this feature we could add important insights for the dashboard user. In this example we will add a text for the best performing store in our sales data, conclude what is the most successful weekday for the sales of our products, and what is the most sold product. All this text will be available for the dashboard user immediately as he opens the dashboard. While editing text for the header, click on the curly brackets icon that will allow you to select a query or analysis. Its first category element will be used to insert into existing text. In static text the dynamic value placeholder is defined by double curly brackets and the ID of the query or analysis as an indication that this is the place where the dynamic value will be inserted at runtime. In our case we will take the name of the first element from analysis 17276 which is the city of Berlin. Should you want to display its value and not the name, just add the hash (#) character after the ID.
Because the header and footer support HTML we will enlarge the text using the SPAN HTML element and its style attribute. We will also highlight the dynamic values in bold.
The layout for our first dashboard is finished and immediately tells users that Berlin is the most successful store, Thursday is the most successful day, and the Adventure Works monitor is the most successful product.
Dynamic text and HTML descriptions are also supported on the chart level. Each chart could have its description with HTML and dynamic values.
24:49 Chart data caching
Every time a user opens the dashboard, the application will retrieve the data from its data sources to display on the dashboard by default. If our data sources, such as OLAP or the Analytic model, do not change frequently or we have too many users accessing the dashboard in a short time, it would be wise to cache the dashboard data and prevent the application from unnecessarily retrieving data from the data sources to save valuable processing CPU time on the data source server. The data are cached on the chart level, so open the dashboard in the design view and in the edit form tile enter the Cache time in minutes, which represents the time the application will keep the results in the memory before retrieving new data from the data sources. In the case of additional analytic or filtering actions the cache is not used of course, but the initial opening of the dashboard will use the cached data.
We expect our Sales overview dashboard to be opened by many users in a short time and will therefore set all chart Cache value to 30 minutes. Our SQL server (or in other cases Analysis Services) will be grateful.
25:53 Dashboard slides
All the charts on the dashboard could run in the Slides view where all charts are displayed in the full screen view one by one, making this feature ideal for presentations on large screens where there are important value cycles for users to monitor. In the design view > details > slides form you can control the amount of the time between transitions in seconds and optionally slides caching time in minutes.
A number of seconds before transition to the next chart is displayed in the lower right corner.
Click on the chart to manually trigger the transition to the next slide at any time.
26:29 Sharing and permissions
After I save the dashboard, it will be saved in the My Dashboards folder by default, a place where other users do not have access. To share this dashboard with other users we must move it to one of the folders that are shared with other users and groups. In our case we will create the Sales folder and set permission to allow access to another user. You can create a hierarchy of folders and subfolders to organize your BI content and set the levels of access.
We will go to the Administration section and create another user.
The Kyubit BI application can use Windows and Active Directory users and groups to secure the access to its objects or can be configured to work with users and groups created in the Kyubit application itself, which is ideal when end users do not belong to the Active Directory domain (For example, accessing the application for internet users).
In this case we will set the Read permission for Michael, which means that Michael can see, but cannot change the dashboard, which he would be able to do with the Write permission. To give access to any user opening this dashboard, add Everyone to the permissions list, which will grant unrestricted access to the dashboard and its data.
27:54 Scheduled 'Analytic Model' updates
We have a dashboard for the sales data overview that we share with other authorized users. What about refreshing the dashboard to show new data for tomorrow and every new day? If your data are in the OLAP database, the processing of OLAP cubes is done outside the Kyubit application on the Analysis Services level. However, in our sales data example we have built the Analytic Model from the CSV file or SQL query results and it needs to be refreshed with new data, typically every day or even sooner. To schedule the refresh routine for our Analytic Model we need to go to the Schedule section and choose New job. Set the job name in the Job form, make sure that the Process Analytic Model item is selected, select the Analytic Model for the scheduled process, and set the time scheme you prefer for the job to execute. It can be run daily, weekly, monthly. Daily recurring processing can be configured to run processing every hour of the day, for example. In our example we will configure the processing job to execute from Monday to Friday at 7am. We will set the recurring processing to occur every 5 hours until 14 hours, which means that the processing job will occur at 7 and 12 o'clock. If the Analytic Model is based on the SQL query results, processing will run the same SQL query again to get the data from an external data source and process the Analytic model again. In the case of a CSV file based Analytic Model it will be the same as in our case. The CSV file must be imported to the Analytic Model (at the time of the creation of the Analytic Model) from the shared folder (a location path starting double backslash character) from where it could always be accessed by the Kyubit service and web application to process the new data.
If the processing fails, consult the Analytic Model Log for more details on the processing. While processing new data, reports and dashboards based on the same Analytic Model cannot be used, so plan to process outside working hours if the processing lasts longer than it is reasonable for a user to wait. The processing lasts from seconds to minutes depending on the size of the data and the hardware related to the SQL server.
SQL based Analytic Models are more convenient in most cases as there is probably some relational database that can be used to get important business values for analysis. But still, you need some technical guy to write an SQL query. A CSV based Analytic Model is more convenient for users without any technical knowledge who update their Excel values from time to time, export them as a CSV file, and import it to Kyubit BI for Analytic Model manual or scheduled processing.
With processing jobs established every day to occur for Analytic Model we are sure that every morning our users will see the most recent data opening reports, dashboards, and KPIs.
30:56 Dashboard subscriptions
If you do not prefer to open the dashboard in your browser every time to get new business insights, subscribe to
scheduled delivery of reports and dashboards to your mailbox at a scheduled time. From the dashboard Options menu or the Schedule section create a new subscription for the dashboard and set a time scheme for the subscription to run and deliver the dashboard as a pdf attachment. Optionally set the pdf page setup and CC emails that would be included in the subscription. In our case the Sales overview dashboard will be delivered to me every Monday at 8am. I will also add an optional condition and set the subscription to run and deliver the dashboard only if one of the KPIs on the dashboard reaches the targeted status. In this case I will set it to receive the dashboard only if the Internet Sales Last 30 days KPI is in the Fail status. With the conditional approach we can create a subscription that acts as an alert which will notify us about events in our business that require our attention.
32:01 Dashboard with a context
We will create a new dashboard whose purpose is to show data related to a specific Product subcategory from our sales data.
Unlike the overview dashboard that shows a little bit of everything, this dashboard focuses on the specific dimension.
Similarly to our first dashboard, we will firstly create a series of simple analysis objects for our new dashboard.
We will quickly show the structure of the new analyses.
We have the Store weekend sales that includes the filters: last 6 months, Saturday, Sunday, and Customer type set to Companies, with the Date dimension for the categories and Sales amount as the measure.
The Sales by customer type analysis with the Customer type dimension attribute on the categories axis.
Analysis for the Sales current year with the Month dimension attribute for the categories, filtered by the current year.
Likewise, the Sales last year analysis with the filter for the last year.
The Sales by weekday-month gives insights into the sales amounts related to Weekdays in categories and Month on the series axis.
And the final analysis, the Sales, that is the simplest and consists of only the Sales amount measure with no dimensions in the categories and series.
We will create a new KPI the Product performance that is based on the Sales analysis and success threshold of 500,000 USD and fail threshold of 200,000 USD for the tutorial purpose.
We will start creating the new dashboard by adding a Product subcategory filter, for example we will set the Televisions as a filter element because this dashboard will always be filtered by the particular product subcategory.
We will set the dashboard name as Product performance In the dashboard details, choose the Gray color pallet to visually distinguish this dashboard, and set the Dynamic title attribute which will include the selected dashboard filter in the dashboard title, which helps the current user to easily identify which product subcategory these dashboard data are about.
In the new dashboard we will add the Sales last 30 days KPI on the top left. Notice that even this KPI is used on the Sales overview dashboard in this context (because of the filter) it shows the values related only to this product subcategory.
The Card visualization simply shows value changes and takes the last value from the analysis or query as the relevant value for the chart. We will connect it with the Store sales at weekends analysis. It is convenient to emphasize the last value while showing the recent values.
The doughnut chart is connected to the Sales by customer type analysis and shows the ratio between the customer types for the given product subcategory.
The next chart is more interesting: the Combo chart displays the values from two analyses or queries that share the same data on the categories axis.
Because we have the Sales current year and Sales last year analyses both of which have months on the category axis, their data can be compared and rendered in the same Combo chart.
Two sets of values will be displayed in any combination of the Line chart and the Column chart defined for each set. Now we can easily visually compare the data month by month for the two years of sales.
Optionally set the Delta chart that will visualize the difference between values in the two sets or even display the cumulative difference throughout the year.
We will connect the HeatMap chart with the Sales by weekday-month analysis to visualize successful sales patterns by months and weekdays. The HeatMap cell is darker as the sales amount is higher and we can easily identify low-sales and high-sales days. Add the Color ranges to quickly identify values that fall in the specific range of values. For the sales data we will add a range for highly successful days when the sales are higher than 50,000 USD with two shades of green. And we will add two shades of red for low sales days where sales fall below 20,000 USD. Now the HeatMap immediately shows which days have fallen into the defined ranges. This will provide useful information for our marketing team.
We will add the Product performance KPI which will be displayed as a Gauge visualization that visually describes how close we are to the 'success' and 'fail' thresholds.
We will add the Product performance KPI again, this time presented as a Goal meter visualization. It is similar to the Gauge, but visualizes the percentage of reaching the goal, which is defined as a success threshold in the KPI.
The last chart is the Geo map connected to the Sales by county and Sales by cities analyses, but this time showing data only for the filtered product subcategory.
37:31 URL filtering
Now we have a 'product-subcategory' centric dashboard and to see the data for any other subcategory a user can edit
dashboard filters.
If you are opening this dashboard from another dashboard or even another application, you can set the dashboard filter value by using the URL query string parameters, and in our case display the sales values for the product subcategory defined in the URL. Simply add a query string parameter that starts with the @ character to the existing dashboard URL followed by the name of the filter with no empty spaces, equals, element name that should be used as a filter value. For example, ProductSubcategory=Refrigerators is all it takes for the URL for the dashboard to present the data for the product subcategory Refrigerators. If multiple values should be set for the filer, just separate them with the semicolon.
38:36 Create relations between dashboards
We have two dashboards related to our sales data.
Now we will create a link between the two dashboards to create a master-detail analysis experience for the users.
We will configure charts presenting the product subcategory data on the Sales overview dashboard to open the Product performance dashboard in a separate browser tab that shows the details for the selected item when clicked on a particular subcategory. This way when a user observes more general data on the overview dashboard, the user can decide to deep-dive into data about the specific chart point, in this case a product subcategory item.
On each chart on the dashboard there is an Open custom URL field that is used to configure which URL will be opened when the chart point is clicked. This can be any URL within the Kyubit BI application or any other external website or web application. When setting the URL, it is optional to include the placeholders that describe the clicked point with available metadata. The placeholder will be replaced with the actual data at the moment when the chart point is clicked. Firstly we will include a URL that opens the Product performance dashboard. Then we will add a query string parameter that matches the filter name on the child dashboard, in this case the Product subcategory. This will equal the category element name in curly brackets, which represents a placeholder that will be replaced by the real element name of the clicked point on the category axis. Various placeholders are available that are related to the selected point, series element name, dimension name, measure, and so on. We will define the same custom URL on both charts having the Product subcategory dimension on the category axis.
Now anytime a user clicks on a specific Product subcategory in the Sales overview dashboard, the child dashboard Product performance will be opened for exactly the same Product subcategory item to show its details.
A user observing our sales data can click on the Washers & dryers subcategory and from the Product performance dashboard immediately conclude that washers and dryers are sold mostly to individuals, not companies, the general product KPI has reached its success goal, the best months for sales are June and July, sales have significantly dropped compared to the last year except for March, and looking at the US territory, they’re mostly sold to California and Washington.
Refrigerators are sold to companies and the current year is relatively successful, as was last year. The Digital SLR cameras subcategory has not reached its KPI goal, but we can see that the sales are best in January and February, and so on.
Following this pattern we could create a chain of dashboards that lead the user analysis from a higher-level overview to a specific point of interest, providing rich data visualizations, KPIs, and metrics at each step.
In our effort to get to the most specific details in our sales data we finally reach the points about a particular username and address, phone number, or email address so that we could perhaps do some marketing activities with our data.
Technically we could add these data as a dimension attribute in our Analytic Model, but this would not be wise as it would take additional processing time and disk memory space for the data we would never use as a dimension in our analysis. You will most probably never analyze the Sales amount over phone numbers or email addresses as their nature is unique and there are no aggregations underneath that could be useful in the analysis.
The Details data type will come in handy here. We can use it while defining the Analytic Model. We will set the Details data type for the Email, Address, Customer, and Phone columns and reprocess our Analytic Model.
Now we can define Drillthrough columns that could render Dimension and Details data when clicking on the cell in analysis or a specific point in the dashboard chart.
Here is an example: start a new analysis based on the same Analytic Model and add the City dimension attribute on the category axis. Now we know the sales amount per city, but who are the customers, and what products have they bought when? Right-click on any of the cells with values and choose Drillthrough by > Custom Drillthrough. On the page that will open first select the Set drillthrough column button. Give your drillthrough a name, for example 'Sales details' and choose which dimension attributes, details, and measures you would like to include as columns in your drillthrough report. We will add the Sales amount, Date, Product name, Customer, Phone, Address, and Email columns. We will save the drillthrough definition for future use. Now every time we right-click on the value cell and choose Drillthrough by, the saved drillthrough will appear. Finally, selecting the drillthrough item shows a table of values in a separate page. We can now see who are the customers with their details and the details of their purchases. Saved drillthrough actions are also available when right-clicking on the dashboard chart point. When the drillthrough table is opened, it can be exported as an Excel file.
Drillthrough actions are very useful when finding details from the bottom of our data and are often used as the final step in analyses.
When using OLAP data, Drillthrough actions defined in the OLAP cube could be automatically used or, similarly to the Analytic Model, OLAP dimension attributes could be used to define Drillthrough actions in the Kyubit BI application.
44:27 Hierarchical dashboard charts
To present how more complex and hierarchical data structures could be visualized on the dashboard we will create another dashboard that focuses on presenting details for a particular month of the sales we have in our data.
For a start we want to visualize hierarchical structures when more dimensions are used combined to show drilled-down data from one dimension to another.
For example, we will start a new analysis, add the Sales amount measure, and set the following dimension attributes on the category axis: Product category, Product subcategory, and Country.
We will expand all product categories and the first two subcategories in each category. Sorting could be applied on each added level. We will also add level filtering to show only the top 5 subcategories and only the top 5 countries.
Now we have a hierarchical overview of the sales data from the product category to the countries where they are sold. We will save the analysis and create a new dashboard.
For the new dashboard we will add the Year-month dimension attribute as a filter. Set the dashboard name to Month analysis, set the Dynamic title option, and choose Warm as the dashboard color palette.
To render our hierarchical analysis we will add the Sunburst chart which is convenient for displaying hierarchical data for one measure with no series. When connected to the analysis, the Sunburst chart will automatically display all expanded dimension levels as they are saved with the analysis. The result is a visually attractive display of hierarchical data. Now we see the immediate impact of particular items related to our sales data without any action from the user. The sales data are drilled down in the preferred way at the moment the dashboard was opened. Move the mouse over particular items to show their data. Click on the items to expand the display of the lower-level data that are not already expanded or right-click on the item to drill down using some other dimension. By adding just the one Sunburst chart to the dashboard users will have a nice tool they can visualize the data with and play around with different dimensions to get interesting insights.
We will create another hierarchical analysis, this time with two measures that will be used as a series of values. In this hierarchy countries, regions, and cities will be expanded to get a geographical insight into the sales data.
Countries and regions will be sorted in the descending order and cities will be limited to show only the top 5 cities related to the Sales amount measure. The Table chart should be used for visualizing the hierarchical data with a series of values.
In the Options table we will set the Allow analytics setting to Analyze which tells the chart that the user can see the defined analysis hierarchy and also drill down data using any other dimensions to get additional insights.
The Table chart displays the hierarchical analysis with the option to expand/collapse any element of the table. Remove any dimension attribute and add a new one to explore the data for different aspects. For example, we will remove the Cities and add the Weekdays dimension attribute and immediately see the sales for each region or state by the weekday sales figures. For the Month analysis dashboard we will add a simple List chart that shows analysis with best-selling products that are sorted in the descending order. A quick and simple performance visualization of the top product sales. Another Sales by day and product subcategory analysis is displayed using the Table which is convenient for displaying a lot of values on the dashboard at the same time. Day by day we see the sales performance per all product subcategories.
48:10 Calculated Measures
For the last Bubble chart visualization we need sales Margin data. Even if we do not have the Margin measure in our sales data, we could use existing measures to create a new CALCULATED measure.
In the grid analysis drag and drop any measure to start the analysis, click on the Options, or right-click on the empty area and choose Create user calculated measure. The dialog form will open to enter the expression that will be used to create a new calculated measure. In this example the expression is very simple - the sales amount minus product costs, but expression could be more complex with parentheses and multiple measures involved. We need to set how the values will be formatted, test if the expression is valid, and save the new Calculated measure that could be used on multiple analyses and by other authorized users. The new calculated measure will appear under the measures section with a different icon. Drag and drop to the grid to see the Calculated measure values and use it like any other measure in the analysis. We are adding the Product subcategory dimension attribute. We have the Sales amount measure that shows the SUM aggregate for each element, but we would like to see the sales count for each item. Even if Sales amount is already added as a measure, we will add another Sales amount measure instance, right-click on the measure and set its cell aggregate operation to Count. Also right-click on the measure to change its position as preferred.
After saving as the Sales & margin analysis we will add a Bubble chart to the Month analysis dashboard and connect to this analysis.
The Bubble requires at least two measures to render values on the X and Y axis. If a third measure exists, it is rendered as a bubble RADIUS on the chart.
Users can visualize the Product subcategory sales performance, which is better as the bubble is higher on the X axis, which represents the Sales amount SUM, more to the right on the Y axis, which represents the Sales amount Count, and the larger the bubble radius, which represents the sales margin. We can immediately see that the Lamps subcategory performs interestingly and will consider these three measures combined.
50:33 User Properties (Personalized dashboard data for the current user)
Until now we have prepared dashboards that show the same data for any user who connects.
But suppose that we have regional managers who are primarily interested in the sales data for their country. Do they need to add a filter for the country every time they open the dashboard to see its sales data? No.
Kyubit BI allows you to configure the user properties and add an analysis or dashboard filter not with a specific value, but connected to an existing user property. While the analysis or dashboard is opening, the application will use the current user property to filter the data. Let's configure one example. We have two users in the Administration section - John and Michael. First we will add a new user property on the application level and name it Country.
Now we can edit user John and set his Country property to the United States value. We will set the property value to 'Australia' and ‘Canada' for Michael the same way.
In this example we will set a new filter for countries on the dashboard level, but it could also be on the analysis level. We will expand the filter dialog to full view and click on the User properties button. The available user properties to be used are listed. We will select the Country property. Because I am currently logged in as John, the dashboard is now sliced with the United States. When I log out and log in as Michael, the same dashboard now displays the data for Australia. The User properties feature could be used with Analytic Models, OLAP cubes, SQL, and MDX queries on the analysis or dashboard level. Users in the Kyubit application could be Windows, Active Directory users, or users created in the Kyubit application. The appropriate authentication model is quickly configured depending on the scenario of use.
52:54 More about analysis and report features
More about the analysis usage. Remember that the analysis is a building block of the dashboards. Each chart in the sales example dashboards is based on a particular analysis. If allowed by the dashboard designer, a user can CONTINUE ANALYSIS from the chart to the full
self-service analytics grid view where more space and options for serious analysis are available. In this example we will continue the analysis with the Sunburst chart. Notice that the dashboard filters are passed to the grid analysis, so the user could continue with the same values visible for him in the dashboard. For instance, in the full analysis grid view a user can add the Customer type dimension attribute to the series and City to the categories to see its values, add measures, optionally save this analysis under some name, export it to pdf or an Excel file, subscribe to receive emails at scheduled times, and many more actions.
The analysis can be opened as a
report. That will be more convenient for users who prefer to see only the results or values with not too many actions and options on the user interface. You can switch to full analysis grid view where all analysis options are available. We will add a few enhancements to this analysis so that the report will be more informative and guide users’ focus to important details.
Add the grid KPI in the Company column to show the Success status for all values above 300,000 USD and the Fail status for all values below 100,000 USD.
Set a similar KPI for the Persons column with 10,000 and 1,000 KPI thresholds. A grid KPI can be defined on the Grid, Column, or cell level.
Now we have the grid KPI icons. Additional set formatting of the columns or particular cells. Make conditional formatting depending on the KPI status. In this example we will set a background color if the first column KPI is evaluated as a success. The foreground color to red and the text to bold if the second column value is evaluated as a Fail KPI status. In the Report > Visuals section set the Value bars option to Compare totals. Now this analysis shows important aspects of the results in the report view and it will be easier for the end user to spot important points of the report. There are export and subscription options in the report view.
55:39 Decomposition Analysis
Another great way to analyze data is the
decomposition analytics. It can be used with the OLAP cubes or analytic models created in the Kyubit application.
The idea of this analysis is to filter down the data in multiple visual steps where parent chart selection filters the charts below.
Let's see how it looks with our sales data. First we will select a single measure for analysis, which we will set to the Sales amount. We will add the first level of decomposition to the Customer type dimension, the second level to the Countries dimension, the third level to Product category, and the fourth level to the Product subcategory. As soon as we click on the first chart and Company column, all charts below are sliced with the selection. In the second chart we could select EU countries, such as Germany, France, Spain, and Italy. Observe how the charts below are immediately filtered as soon as we click on any value on the parent chart. For the Product category we will change the Chart type to Pie. Selecting the category items on this chart will change the value on the bottom chart which is the final chart in this decomposition. An aggregation of all filters applied from the upper charts is displayed along with the level of each chart. At any time we can select any value on an upper chart and all the lower charts will refresh the results.
Each level can be opened in the Expanded view, exported to a pdf or an Excel file, or continue analysis from that point with the grid analysis features.
We can also add filters that would be applied at the start of all existing decomposition levels. For example, we will add the Weekdays dimension and select the Saturday and Sunday elements. We can now save this decomposition as 'EU, weekend sales analysis', share it with other users, or use it on the dashboard. If added to the dashboard, the final (lowest) chart is displayed as a result of this decomposition analysis.
58:12 Scorecards
If you created many KPIs in the application, a great way to visualize them at once is the scorecard chart. The scorecard is a list of selected KPIs that use less space and shows multiple KPIs. To create a Scorecard navigate to the
KPIs & scorecards section and choose a new scorecards option. Set a scorecard name in the scorecard design form and assign KPIs that will become elements of this scorecard. Test the visualization with current KPI values and save the scorecard. The scorecard can be displayed on the dashboard, so we will edit the layout of the existing dashboard and add the Scorecard visualization. The scorecard uses less space than individual KPIs added to the dashboard. The scorecard also displays KPIs with additional insights into KPI Target, Target percent, Trend line, and visualization of KPI value progress owing to the KPI thresholds.
59:24 Multi-page reports
To end of this step-by-step self-service analysis video tutorial we will create the final data-insight object, which is a Multipage report.
The Multipage report is a report that can display multiple reports and dashboards on a single URL. Select a new multipage report option and simply select reports and dashboards that will be included in the multipage report. To switch between multipage report items the user has to click on the buttons visible at the top representing a report or a dashboard. By using the multipage report we can organize the created content into one meaningful whole related to the specific topic and don’t have to make users search and open each item separately. The multipage report has the option to share the same filter values among the containing reports and dashboards or that each element uses its own filters.
1:00:24 Mobile devices view
At the same time we are ready to open the created dashboard using the mobile view designed for mobile devices.
Open your favorite browser on your mobile device and set the URL to your Kyubit application. If your Kyubit application is not exposed to the internet and your mobile device is using the same Wi-Fi network, you can access the application using the existing IP address and port number.
Enter your credentials into the login form and a list of all available folders with dashboards will be displayed.
Here we have the folder with this tutorial dashboards and our Sales overview dashboard will be opened in the mobile view, which means that the dashboard charts and visualizations are opened one after another, optimizing the user interface for small screens. Scroll to the bottom to browse all dashboard charts.
In the
mobile view BI analytics a user can also use drilldown, drill-by, and drill-through actions to analyze and discover related data. Tap on the chart point and a menu with additional actions will appear. After the drilldown action tap on the 'back' button to move the chart back to the previous state. The drill-through action will present a larger scrollable list of details related to this action. You can save your favorite dashboard as a link on your home screen to have the option to quickly see new dashboard values at a single tap on your mobile phone or tablet.
To sum up...
We have an Analytic Model about our sales activities that refreshes its data every day and a series of analysis objects based on the same Analytic Model that we used to build the dashboard with analytic abilities.
The same dashboard shows fresh business data to authorized users, while we set up an email-based scheduled subscription for the dashboard.
That is a start of our BI environment. And even if this single dashboard showing new sales overview data every day would be useful to have in its own right, the full potential of our Self-Service BI approach is to create multiple mutually aware dashboards and reports that are personalized for the current user. Orchestrated dashboards and reports are leading user analytics from high-level to specific low-level data insights using stunning analytic visualizations.
We hope you will use this video tutorial to start analyzing and visualizing your data. As you can see, the Kyubit
business intelligence platform presents a set of tools to quickly create a BI environment. Of course, it takes planning to see what data to include. Define your potential measures and dimensions. If your data are stored in OLAP/Tabular/SQL databases or Excel/CSV files, create analytics and dashboards to get additional business value from your data. The Kyubit
BI platform are designed to provide simple and user-friendly tools to support you on your way to enrolling your business intelligence environment on your PC, company network, or globally available BI portal on the web.