Consume Credible Metric Data Effortlessly with Microsoft Excel
Excel: important skill for data analysis
Excel is probably the most commonly used spreadsheet for data statistics and data analysis. Compared with BI, Excel is less expensive and easier to use. People can do various data analysis jobs with Excel.
However, like other BI tools, when using Excel for data analysis, it is often necessary to reprocess the current data source, e.g., applying functions, or using pivot tables for customized aggregations. In this case, issues will inevitably arise even if everyone uses the same set of data. For instance, the definition of a metric might be inconsistent among different teams, and there might be duplicate metrics, reducing the reusability of metrics.
Metrics store and Kyligence Zen
It is actually quite common to encounter such issues when using Excel for collaborative work. Take the retail industry as an example, where data and metric analytics are widely needed. For instance, Peter, a regional manager, and Jane, a financial manager, both use Excel for sales order analysis. The regional manager is more concerned with the breakdown of sales data, while the financial manager prefers to see the trend. Obviously, with this approach, they are likely to disagree with each other due to inconsistent metric definitions or are reluctant to use the report already created
In blog “The Missing Piece in the Modern Data Stack”, Benn Stancil, Chief Analytics Officer of Mode, presents the chart below. As can be seen from the chart, in traditional architectures, the logic of metrics is “firmly” coupled with the application layer. This has resulted in inconsistent metrics policies or reuse problems.
In the new architecture, by extracting the metrics logic (i.e., establishing a metrics store), a single source of truth (that is, consistent metrics definition) can serve as the application layer. In such an architecture, we can continue to consume credible metrics data with lightweight BI tools like Microsoft Excel.
Kyligence Zen makes it easy for customers to perform metric-centered data analysis. As an intelligent metrics-driven management and decision-making platform, Kyligence Zen enables enterprises to build an agile, consistent, and low-cost metrics system. For data consumers, Kyligence Zen provides an open API to easily connect with various application tools and platforms, such as Microsoft Excel, and helps to achieve efficient digital-driven collaboration.
Create credible metrics with a consistent metrics catalog
Let’s check how to quickly set up a consistent metric catalog in Kyligence Zen:
The Metrics for New Retail Profit and Loss Analysis have been published in Kyligence Zen Metrics Template market. You may click the link and reproduce the use case in your own Kyligence Zen account.
Upon completion of data preparation and batch creation of metrics, we can see these metrics in the metric catalog of Kyligence Zen and achieve consistent metrics management. In this way, we can increase metrics credibility and bring together team members to work collaboratively, enabling metrics reuse.
Use Microsoft Excel to connect Kyligence Zen and consume metrics data
Next, we will use Excel to consume the metrics defined in Kyligence Zen, helping enterprises with a metrics-driven business and management approach.
To integrate Excel with Kyligence Zen, we need to install the Kyligence Zen add-ins. For specific steps, please refer to Install Kyligence Zen Excel Add-In | Kyligence Zen. Upon completion of installation, you can connect to the standardized metrics service provided by Kyligence Zen via Excel to create charts and pivot tables for analytics related to metrics data.
Connect to metrics services via Excel
Create a new worksheet in Excel, click the Kyligence plugin icon, create a connection, connect to the Kyligence Zen server, and enter the user information to complete the login.
For specific steps, please refer to Use Excel for Data Analysis | Kyligence Zen.
Analytics based on credible metrics
After connecting to Kyligence Zen Server, you can create pivot tables by adding charts and adding metrics from the metrics catalog. Given the fact that these metrics are all from the metric catalog of Kyligence Zen, they have consistent metrics definitions and better reusability, so everyone in the organization can work on credible metrics.
This solves the issue encountered by Peter, the regional manager, and Jane, the financial manager. Kyligence Zen lays a solid foundation for interdepartmental collaboration.
As mentioned above, Peter, the regional manager, gives particular attention to the breakdown of two metrics, i.e., total sales and net profit margin. By importing metrics data and identifying the analytics dimensions needed, Peter can create a pivot table and consume the metrics data directly to guide him in making further decisions.
Jane, the financial manager, wants to see a visualized trend chart directly. Based on the centrally managed metric catalog, she can determine whether the current allocation of budget is appropriate based on the changes in the trend line.
Both Peter’s decision-making and Jane’s budget resource allocation are highly convincing, as they are based on analytics on credible metrics with consistent metrics definitions. Consuming credible metrics data via Excel empowers businesses with effortless self-serve digital analytic capability.
Conclusion
As we can see from the above examples, the concept that “the metrics can be defined once, and then reused anywhere” can be implemented by defining metrics in the metric catalog of Kyligence Zen. Meanwhile, metrics with consistent definition also lay a solid foundation for mutual trust, enabling team members to make informed decisions.
Kyligence Zen also provides open standard interfaces, organizations can consume credible metrics data in Microsoft Excel and other commonly-used BI tools, and reuse metrics in various business scenarios. By doing so, Kyligence Zen empowers businesses by making everyone a data expert, reducing time spent on gaining insight from data and unleashing date values for the goal of building an e-enterprise.
Experience Kyligence Zen now
The Metrics for New Retail Profit and Loss Analysis have been published in Kyligence Zen Metrics Template market. You may click the link and reproduce the use case in your own Kyligence Zen account.
Note : The dataset is from Kaggle, with Mohamed Harris as the author. Some columns of the data used in this article are adjusted on the basis of the original dataset.
