Data analysis tools that can also be used by operations: Quick BI ad hoc analysis detailed explanation

Quick BI ad hoc analysis: enabling business to achieve self-service analysis

Whether it is a startup or a large group, data analysis is one of the core tasks of the BI team and is a powerful tool for driving business data and promoting business development. However, with the rapid development of the business, the workload supported by the BI team often grows exponentially. With limited human resources, it is difficult to meet the demands of the business department.

“It is better to teach a man to fish than to give him a fish”!

Quick BI's ad hoc analysis provides flexible data analysis capabilities, allowing data to be retrieved and analyzed at any time.

With IT support, standard metadata, and improved row-level permission management by organizational administrators, business personnel can use ad hoc analysis to improve costs by dragging and dropping, performing analysis and data retrieval with zero SQL, reducing dependence on BI analysts and ultimately improving the efficiency of data analysis and business decision-making.

Scenarios that trigger business personnel's motivation to analyze

Ad hoc analysis can be created proactively with a purpose, such as proactive analysis based on the "product inventory" data set; it can also be triggered when viewing data, such as seeing some abnormal data on the dashboard and wanting to further diagnose the cause of the data anomaly.

Users can then conduct personalized exploration based on the data that needs to be analyzed, such as filtering and sorting specific data, cross-drilling, secondary calculations, etc., and finally share the results with business decision makers via email, messages, etc.

The figure below shows the general process from data to analysis to decision-making within an enterprise. The most important thing about ad hoc analysis is that it simplifies the intermediate analysis process and gives the ability to analyze to every business person.

How to quickly build analysis tables using ad hoc analysis

As a business operation, the sales data we focus on is complex and changeable, and we need to combine data from different scenarios every day. For example, we need to adjust goods based on the inventory status of stores across the country to ensure that key stores have sufficient inventory during the promotion period.

At this time, relying solely on a fixed cross table cannot fully meet the demand for adjusting goods from the sales data of the East China region and the inventory status of stores across the country. The ability of ad hoc analysis can realize flexible viewing and retrieval of data.

1. Data Preparation

Enter the data set and create a new ad hoc analysis through the data set or directly create it in one click from the ad hoc analysis module.

2. Data selection

On the left side of the ad hoc analysis page is the data panel.

First there is data, then there is a table. The left data panel can directly load specific dimension values ​​in accordance with the operating habits. It is more convenient to drag the fields on the left to the table area on the right to generate a report. Here is a dynamic picture to roughly demonstrate how to select fields from the data panel in ad hoc analysis and generate a table by dragging. Ad hoc analysis uses the dimension value mode by default. Each dimension field can directly expand the specific dimension value under the current dimension and drag and select the dimension value. Part of the data screening has been done when selecting data.

Check "Show dimension names only" in the data panel to switch from the default dimension value mode to dimension mode. At this time, just like the data panels of dashboards, spreadsheets and other modules, selecting a dimension will select all dimension values ​​under the current dimension.

You can generate a table by double-clicking or dragging dimensions. For example, selecting the field "Transportation Mode" directly selects all three dimension values ​​under "Transportation Mode".

3. Data screening

After the table has been formed, if you want to further narrow the scope of data query, you can also directly generate query controls by dragging and dropping.

The query controls for ad hoc analysis do not require complex condition configuration.

For example, if you want to filter out product packaging types as "medium box" and "large box" based on the existing table, you only need to directly select the two dimension values ​​​​"medium box" and "large box" in the data panel and drag them to the control area.

If you need to view the filtered data corresponding to a metric, simply drag the metric into the control area and set the specific value.

For example, if you want to view data with order quantity > 1000, simply drag "Order Quantity" into the control area and enter the corresponding value.

4. Data Calculation

In ad hoc analysis, you can right-click in an existing table to perform functional operations. The operations that can be performed vary depending on the selected content.

For example, when you right-click and select two metrics, you can perform percentage, difference percentage, and four arithmetic operations, and the calculation formulas are displayed in the order selected.

In addition to the several quick calculations provided by default, you can also write expressions through custom calculations.

5. Cross Drill

Drill Down & Roll Up

All dimension fields that have a hierarchical structure configured in the data set, as long as they are not the last level in the hierarchy, can be drilled down in the table in ad hoc analysis. Drilling down allows for more fine-grained data queries.

For example, in the geographical hierarchy in the figure below, you can drill down from the original "region" to "province", and from "province" to "city";

Right-click on the dimension value of a sub-level to return to the previous level.

Expand

The expansion is for the entire dimension to which the currently selected row dimension belongs. For example, here is the dimension "Region". Select the dimension value "Northwest" and click Expand. Finally, add all values ​​of "Province".

Ad hoc analysis can also support expanding dimension values ​​at different levels. For example, the table in the figure below originally shows the data of the two regions of "Central China" and "North China" + the four provinces of "Jiangsu", "Zhejiang", "Fujian" and "Jiangxi". When expanding, it can automatically expand to the next level according to the level it is in. The last level can be automatically merged and no longer expanded.