Data Analytics: Exploiting the
Data Warehouse
Helena Galhardas
DEI/IST
References
• A. Vaisman and E. Zimányi, Data Warehouse Systems:
Design and Implementation, Springer, 2014 (chpt 9)
Outline
• Data Mining
– leave that for the SAD@IST course
Key Performance Indicators
• Dashboards
3
Key Performance Indicators (KPIs)
• Complex measurements used to estimate the effectiveness of an organization in performing their activities and to monitor the performance of their processes and business strategies • Typically included in dashboards and reports
• KPIs have a current value which is compared against a target, a
threshold, and a minimum value
• Classification along the temporal dimension:
– Leading KPIs: Reflect expectations about what can happen in the future. Ex: expected demand.
– Coincident KPIs: Reflect what is currently happening. Ex: number of current orders.
– Lagging KPIs: Reflect what happened in the past. Ex: include earnings before interest and taxes or customer satisfaction.
Key Performance Indicators: Other
Classifications
• KPIs can be also classified as being input to or output from a process:
– Input KPIs: Measure resources invested in or used to generate business results. Ex: headcount or cost per hour.
– Output KPIs: Reflect the overall results or impact of the business activity as a quantification of performance. Ex: Customer retention and
employee turnover (the rate at which an employer gains and loses employees)
• KPIs can be also classified as qualitative or quantitative:
– Qualitative KPIs: Measure a descriptive characteristic, an opinion, or a property. Ex: Customer satisfaction measured through surveys, where survey data are quantitative, but measures are based on a subjective interpretation of customers’ opinions.
– Quantitative KPIs: Measure characteristics obtained through a mathematical expression. Most common kinds of KPIs. Ex: Units per man-hour.
5
Key Performance Indicators: Guidelines (1)
1. Identify the sources from which we can obtain relevant information• Primary sources
– Front-line employees: Are at the core of the value chain and know the important factors to achieve the operational goals
– Managers: Provide their perspective across the value chain and their strategic knowledge – Board: Defines the organizational goals and suggests KPIs that are highly prioritized and
sometimes non-negotiable
– Suppliers and customers: Bring an external perspective to what should be measured and improved
• Secondary sources: Include strategic development plan, annual business/ strategic plan, annual reports, internal operational reports, and competitor review reports
• External sources: Include printed catalogues, online catalogues, annual reports of other organizations, expert advice, and questions in discussion forums
Key Performance Indicators: Guidelines (2)
2. Assemble a (preferably small) team3. Categorize potential metrics, to look at the business from different perspectives, e.g., from a financial and customer’s perspective and with respect to employee’s expectations
4. Brainstorm possible metrics to discuss many possible measures before deciding the final set
5. Prioritize the initially defined metrics. For this:
– Give a precise definition of the metric
– Define if the indicator is leading or lagging (even number is recommended) – Verify if the metric is likely to have a relevant impact
– Check if the metric is linked to a specific set of business processes that we can drill into if it deviates from the desired values
– Check if we have at least one to two metrics for each key category defined in the second step
7. Perform a final filter on metrics
8. Set targets for the selected metrics (use historical information as a guide) 7
Key Performance Indicators: Examples
for the Northwind DW
• Sales department: Wants to monitor sales performance and order activity
• Marketing department: Wants to follow shipping efficiency to estimate customer satisfaction
• Human resources department: Wants to measure how sales employees are performing to estimate the end-of-year bonuses
• KPIs for the above:
1. Sales performance: Monthly sales amount with respect to the same month of the previous year. Goal: Achieve 15% growth year over year
2. Number of orders: Number of orders per month. Goal: Achieve a 2% monthly increase
3. Shipping efficiency: Delay in the shipping of orders. Computed as the monthly average of the difference between the due and the shipped dates. Goal: The indicator remains on the positive side
4. Shipping costs: Relative cost of shipping with respect to the sales amount. Computed as the quotient between the freight costs and the total sales amount for the current month.
Goal: Shipping cost does not exceed the 5% of the sales amount
5. Salespersons reaching quota: Percentage of employees reaching their selling quota yearly. Goal: salesperson reaching quota must be at least of 75%
Outline
• Data Mining
– leave that for the SAD@IST course
• Key Performance Indicators
Dashboards
9
Dashboards
• Most popular visualization tools in BI
• Collections of multiple visual components (such as charts or KPIs) on a single view
• Enable to effectively measure, monitor, and manage business performance
• A classic definition (by Stephen Few): “A dashboard is a visual display of the most important information needed to achieve one or more objectives, consolidated and arranged on a single screen so the information can be monitored at a glance”
• Time horizon and scope of data needed differ significantly based on the roles in the organization:
– Executives: Focused in enterprise-wide strategic goals, high-level view covering months or years
– Business managers must achieve daily or weekly performance goals => require narrower time frame, and the ability to quickly investigate the amount and cause of variation of a parameter
– Business analysts approach performance data with ad hoc questions => may
Types of Dashboards
• Strategic dashboards: Allow for a quick overview of the health of an organization assisting executive decisions such as the formation of long-term goals
– Do not require real-time data – Focus is in the past performance – May be quantitative or qualitative
– Should have an interface that quickly guides decision makers to know if indicator is on track
• Operational dashboards: Designed to monitor the company operations
– Monitoring operations requires more timely data tracking constantly changing activities
– The display media on operational dashboards must be very simple to avoid mistakes – The timeliness of operational data vary: If things are on track, periodic snapshots
may be sufficient. If a measure deviates from the goal, operational managers may want real-time data
• Analytical dashboards: support interaction with the data, such as drilling-down into the underlying details, to enable the exploration not just to see what is going on but to examine the causes 11
Guidelines for Dashboard Design (1)
1. Right visual elements and interactions must be carefully chosen2. Must account for placement, attention, cognitive load, and interactivity
3. A dashboard is meant to be viewed at a glance, so they must be arranged in a display that can be viewed all at once in a screen, without having to scroll or navigate
4. Important information must be noticed quickly
5. Crucial to know who the users of the dashboard will be
6. Choose data visualizations that convey the information clearly, are easy to interpret, avoid excessive use of space, and are attractive and legible
Guidelines for Dashboard Design (2)
7. Analytical dashboards should provide interactivity, such as filtering or drill-down exploration
8. Operational dashboards should display any variations that would require action in a way that is quickly and easily noticeable, e.g., using KPIs
– Red to show performance below target, green for good performance,
yellow if no action is required
– If multiple KPIs are used in a dashboard, use color coding consistently for the different KPIs
9. Avoid to include distracting tools in a dashboard like motion and animations or too many colors
10. Dashboard visualization should be easy to interpret and self-explanatory
11. Test with the user
13
