Advanced Data Handling: Using BI Objects as Data Sources in Smarten

Scenario: 

Leveraging a user-acquired result set or sub-cube data from a Smarten front-end BI object as the source for another dataset within Smarten. In simpler terms, user can use this feature to use BI object (such as crosstab, tabular, graph, etc., ) data as data source. 

Requirement: 

Our objective is to utilize highly transformed and aggregated front-end object data as a data source. This data source can then be further analyzed or used to create additional front-end objects.

About the Use case

Let’s analyze the input data we have:

We have state-wise network data along with its corresponding network frequency.

Expected Output: We need the actual count and contribution for each bandwidth within each state and district.

A user can do such transformation using very complex SSDP steps too ( in most of the use cases ), but if the use case needs aggregated front end BI object data, this step is straight forward and does not require complex ETL or SSDP skills. 

Solution:

The following are the logical steps to achieve the scenario:

Logical Steps for Creating a Source Object (i.e., Cross-tab):

1. Consider the measure column (Count column) twice; one will be used for the actual count and the other for calculating the contribution.
2. Apply the row percentage data operation on one of the Measure columns to calculate the contribution for state-wise and city-wise bandwidths.

Logical Steps for Using the Above Object as a Source:

1. Create a source and dataset using the above object, which will be further used for analysis.
2. Rename the columns for better understanding and remove any unwanted columns.

By following these steps, we will obtain the expected result.

Steps to create the example source report in Smarten.

Step 1: Create a data source from the CSV/Excel file and then create the dataset from the source created. For this instance, we will use an Excel file as the source.

Below is the resultant dataset

Step 2: Create a cross-tab object from the above dataset to calculate the contribution for all bandwidths.

• Place "State" and "District" in the rows.
• Place the column named "Network Frequency" in the columns.
• Place "Count" in the data section.

Note: We need to consider the "Count" column twice in the outliner.

After adding the required columns in the outliner, the following object is created.

Step 3: Right-click on the second measure column (i.e., Count_1) to calculate the contribution and select the data operation as "Row Percentage."

We will perform this operation at the object level because, for our scenario, the least granularity is "District," whereas at the dataset level, the least granularity is "Date."

Step 4: After applying the data operation, we obtain the following outcome.

Step 5: Save the object in the desired folder in the Smarten repository.

Steps to Create a Source Using the Above-Created Object in Smarten

Step 1: To use the above object as a source, create a new data source and choose "Smarten BI Object" as the source.

Step 2: Choose the previously created and saved object from its folder location.

After selecting the object, click "OK."

Below is the resultant dataset.

Step 3: Rename the columns according to the requirements. This dataset can now be used for further analysis as well as for preparing front-end objects.

For example:

• · Rename "High_Bandwidth_Count_1" to "High_Bandwidth_Contribution."
• · Rename "Low_Bandwidth_Count_1" to "Low_Bandwidth_Contribution."

Conclusion :

By reusing highly aggregated and complex front-end objects, we can streamline ETL processes and perform advanced data processing more efficiently. This approach not only saves time but also enhances the overall data management and analytical capabilities within Smarten.