Business Intelligence in a Cashless Society using SAP BI/BW and QlikView
Cash payments have been decreasing constantly in Sweden in recent decades and are expected by many to continue decreasing as card payments and also mobile payments gain ground as attractive services. Since 2009 there has been an interesting development, as the gross value of cash in circulation denominated in SEK, actually decreased for the first time since 1950. The value of cash in relation to GDP in Sweden had been decreasing for a long time, but now we also saw a decrease in actual gross value. But the low use of cash is somewhat unique to Sweden and the other Nordic countries (Arvidsson, 2013). In 2011, the value of cash in circulation in relation to GDP was 2.4% for Norway, 2.7% for Sweden and 3.5% for Denmark, which can be compared to 7.1% for the USA and 9.4% for the entire European Union in 2010 (Ulvaeus, 2013). Looking at market shares of retail payments, we see that the number of cash payments is low in the Nordic countries. The Nordic countries are among the most cashless societies in the world.
Can developed nation become a 100% cashless society? Well, theoretically they could, as the number of electronic payment services that can substitute for cash transactions is increasing by the minute. If consumers, merchants, banks, telecom operators and other stakeholders all move in this direction, it could happen. This BI project is designed to find out the benefit government/Economy development Organization (EDO) can incur by exploiting the huge data collected in the future cashless society by the online payment.
2. Project objectives and scope
- Short term: The purpose is to analyze the transaction data collected through online payment for improving the infrastructure and improving the life of the citizen.
- Long Term: In the long run the project scope can be enhanced to check crime by the law enforcement department.
- To be implemented in developed region where mobile & internet penetration is highest.
- EDO will collect payment data from vendors for transaction related to Travel ticket, Medicine purchase and Parking Area.
- EDO should make it mandatory that all payment transaction data must be linked to master server of the EDO.
3. Business Case
3.1 Problem Statement
- There are few stations which are beyond the capacity to control the passenger.
- Some parking lots are full at certain time and some are not being used.
- There are certain types of disease breakout in a region and it goes beyond control.
- Products which are imported are sold more than local brand affecting the economy.
3.2 Proposed Solution
- Station with more number of traffic beyond the capacity should be developed further.
- EDO can enhance busy or close those parking areas not being used.
- EDO can take Pro-Active measure against some disease by analyzing which kind of patient are more in certain region.
- EDO can check which product from the foreign player (export products) are being sold more in any particular region so as to encourage local industry to protect the economy.
Solution : Monitoring KPIs will help us to meet these goals.
4. Data Model (Star Schema).
Figure 2 Conceptual Data Model (Star Schema)The star schema(Figure 2) separates business process data into facts, which hold the measurable, quantitative data about a business, and dimensions which are descriptive attributes related to fact data.In the star schema design, fact table sits in the middle and is connected to other surrounding objects (dimension lookup tables) like a star. Each dimension is represented as a single table. The primary key in each dimension table is related to a foreign key in the fact table.This fact table as shown above in the Figure 2 has four main dimensions – Customer, Time, Sales and Material.These dimensions are then linked to the fact table through indexes to enable tables to be joined to permit fast queries, reports and data consolidations to be carried out. In the subsequent section we have defined in details on how we have used this structure for our project.Figure 3 Logical Data Model (Star Schema)Fact tables record measurements or metrics for a specific event. Fact tables generally consist of numeric values, and foreign keys to dimensional data where descriptive information is kept. Fact tables are designed to a low level of uniform detail (referred to as “granularity” or “grain”), meaning facts can record events at a very atomic level. This can result in the accumulation of a large number of records in a fact table over time. Fact tables are defined as one of three types:Transaction fact tables record facts about a specific event (e.g., sales events).Snapshot fact tables record facts at a given point in time (e.g., account details at month end).Accumulating snapshot tables record aggregate facts at a given point in time (e.g., total month-to-date sales for a product).In our project we have used the Fact table containing the dimensions namely: Time, Material, Sales ID and Customer. These are shown above in the Figure 3.
5. Key Performance Indicator (KPIs)
5.1 Definition of KPI in this project
A key performance indicator (KPI) is a business metric used to evaluate factors that are crucial to the success of an organization. KPIs differ per organization; business KPIs may be net revenue or a customer loyalty metric, while ECO might consider unemployment rates or analyzing the people activity to enhance the betterment of people life. Before KPIs can be identified, the following requirements must be met:
- A predefined organizational process.
- Clear business objectives for the process.
- Quantitative and qualitative measurements.
5.2 KPI 1: Busy Location.
KPI: Busy Location helps to understand how much pressure is on a particular station or bus stop by calculating the number of passengers using it. The dimensions used are time and material while the key figure is Quantity which is the number of passengers using the station.Business case: With this information, the traffic authorities and city planners can plan which stations are overburdened and which are not and then allocate the available resources optimally.
5.4 KPI 2: Consumption of a particular medicine in a given area.
KPI: Medicine category will collect the information like time and quantity of a particular medicine purchased at a location and customer details like age group. The quantity is calculated using the formula as mentioned above in Table 3.Business Case: When we know that a particular medicine (especially if it happens to be a treatment of major disease or condition) has been purchased more at one location, we can use this data to ensure the adequate supply of it. Also this data can help the health authorities to have an idea about the spread of various ailments in a given area.
5.5 KPI 3: Sale of imported products as against local products.
KPI: Product category- Here we calculate the proportion of the local made products as against the imported ones. The formula used is shown in the below Table 4.Business case: The ratio of the local goods sold versus imported goods sold gives the authorities an idea about the health of the local businesses and economy. It can be a very helpful index for planning and development programs.
5.6 KPI 4: Calculating the occupancy of parking lots.
KPI: Parking space- Through this KPI we calculate the parking space used and resultant availability of parking in a given area. This is calculated using the formula shown in the below Table : 5.Business case: Parking is a big problem in many cities around the world. With use of technologies we can provide smart solutions for this problem. Comparing the usage of parking spaces with the designed capacity, the authorities can plan the parking spaces more optimally by de-congesting the overcrowded parking spaces.
Excel is the best tool for executive dashboard prototyping, because of its flexibility and development costs. Creating a fully functional prototype is not hard and it should be available for user feedback in a matter days. So, make sure that, every time you spot a dashboard project, a prototype in Excel is included.Since most business intelligence applications are notorious for their lack of basic chart formatting options, it is not hard to simply create chart that IT is unable to implement. If needed, use some advanced Excel charting techniques (Pivot Report).The Figure10 shows the fact table in excel prototype we have used in our project.The following are the dimensions created in our Excel prototype:These dimensions as shown above in the Figure 11, along with the Time ID make up the dimension part of the Star schema. Following are the KPIs as used in our Excel prototype.Figure 6 KPI 1 – Excel prototype (Busy Location)Figure 7 KPI 2 – Excel prototype (Consumption of a particular medicine in a given area)Figure 8 KPI 4 – Excel prototype (Sale of imported products as against local products.)Figure 9 KPI 5 – Excel prototype (Calculating the occupancy of parking lots)
7. SAP Process
The Figure 17 shows the SAP process as implemented in the SAP BI system. We shall now see how the InfoArea, InfoObject, DataSource and InfoSource , looked like in our project. Figures 18, 19, 20, and 21 show the above said objects.7.1 InfoareaFigure 11 Infoarea7.2 InfoObjectFigure 12 InfoObject
7.3 DatasourceFigure 13 Datasource7.4 InfosourcesFigure 14 InfoSources
8. Qlikview Implementation.
9. Lessons Learned.
- Importance of design phase.
- How to Identify and recognize importance of data.
- To Identify the Master Data and Transaction Data.
- To Identify the key figures.
- Formulation of Data – Model (Star Schema).
- Defining KPI’s to analyze performance.
- Implementation in SAP BI System.
- Implementing Star Schema in SAP System with the help of InfoObjects, DataSource, InfoCube, Transformations and Formulas.
- Quality of Data and its importance (Handling date formats, Quantity/Currency formats- external formats).
- Data Extraction using Xtract QV Designer and SQL query application. Usage of reporting tool (Qlikview) for generating different graphical representations.
This BI project is designed to find out the benefit government/Economy development Organizations (EDO) can incur by exploiting the huge data collected in the future cashless society by the online payment. It must be scaled to most of the sectors available so that enhancing/implementing of the economy can be controlled from a centralized BI system. This paper deals only with certain sectors. If consumers, merchants, banks, telecom operators and other stakeholders all move in this direction and work together, a country’s economy can be raised based on this Reporting tool.
. Business intelligence roadmap. The complete project lifecycle for decision-support applications. Boston, MA: Addison-Wesley. Online verfügbar unter http://www.worldcat.org/oclc/50598640.
. Data warehousing and knowledge discovery. 11th International Conference, DaWaK 2009 Linz, Austria, August 31-September 2, 2009, proceedings. Berlin ;, Heidelberg: Springer-Verlag. Online verfügbar unter http://www.worldcat.org/oclc/567206768.