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

Modified on Wed, 10 Jul at 2:05 PM

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.

Date

Network Type

State

District

Network Frequency

Network Data Points

09-Mar-24

Type 2.5G And 4G

Bihar

Banka

Medium Bandwidth

6

09-Mar-24

WiFi And 4G

Bihar

Banka

High Bandwidth

86

09-Mar-24

WiFi

Bihar

Banka

High Bandwidth

57

09-Mar-24

Type 2.75G

Bihar

Banka

Low Bandwidth

11

09-Mar-24

Type 3G

Bihar

Banka

Medium Bandwidth

71

09-Mar-24

WiFi And 2.75G And 4G

Bihar

Banka

Medium Bandwidth

10


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


State

 

District

High Bandwidth

 

Low Bandwidth

Medium Bandwidth

 

 

Count

Contribution

Count        

Contribution

Count        

Contribution

Bihar

Banka

799

87.51%

11

1.20%

103

11.28%



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.








Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article