Platform exercises

Zilliant Platform Bootcamp

Platform exercises files.zip

Platform Exercises I

Customer Requirement:
The Customer needs a way to link customer data with salesperson data to see who owns an account.

Acceptance Criteria
As an IQI user, I must be able to review Customer data and see their assigned Sales Rep by name.

Creating Worksheets, Related Fields and Calculated Fields

1. Create a new Platform Exercises workbook
2. Create a new Customer Data worksheet from the Customer managed data source
3. Create a new Salesperson Data worksheet from the Salesperson Data.csv file
   • Salesperson Id as Business Key
   • All fields as String
4. On the Customer Data worksheet, add a relationship to the Salesperson Data worksheet
   • Join Type = Left
   • Join Conditions:
     1. Salesperson Id equals Salesperson Id
5. Add the Salesperson Name related column from the Salesperson Data worksheet to the Customer Data worksheet
6. Add a Sales Rep calculated column on the Customer Data worksheet concatenating the Salesperson Id and Salesperson Name fields
   • Value type = STRING

{RW0_SalespersonName} + ' | ' + {SalespersonId}

Platform Exercises II

Customer Requirement:
The team needs a process to aggregate and analyze order data by customer and product, calculating revenue, profit, and margin for better decision-making on product pricing and sales strategies. This will help streamline margin calculations and automate updates, ensuring the most accurate and up-to-date insights are always available.

Acceptance Criteria
As an IQI user, I must be able to review the average margin by Customer and Product for all orders starting in 2023.

Platform Basics.pptx

Aggregate Worksheets

1. In the Supporting Analysis folder, create a new Aggregate Worksheets workbook
2. Create a new Order Data worksheet from the Order managed data source
3. Create a new Order Line Data worksheet from the Order Line Item managed data source and hide the worksheet
4. On the Order Data worksheet, add a relationship to the Order Line Data worksheet
   • Join Type = Inner
   • Join Conditions:
     1. Order Id equals Order Id
5. Add the following related fields from the Order Line Data worksheet to the Order worksheet
   • Order Line Item Id
   • Product Id
   • Quantity
   • Cost
   • Revenue
6. Create a new CP Aggregate Data aggregate worksheet based on the Order Data worksheet, grouping on Customer Id and Product Id and creating a Total Revenue calculated field aggregating the SUM of Revenue
7. Create a Profit calculated column in the Order Data worksheet
   • Value type = NUMBER

{RW0_Revenue} - {RW0_Cost}

8. Create a Margin calculated column on the CP Aggregate Data worksheet calculating the weighted average margin for each customer and product combination
   • Value type = NUMBER

SUM({Profit}) / NULLIF(SUM({RW0_Revenue}), 0)

9. Create a Last Order Date calculated column on the CP Aggregate Data worksheet calculating the max order date for each customer and product combination
   • Value type = DATE

MAX({OrderDate})

Entity-2-Entity Job Workflow

1. Create a new Customer Product Margin worksheet in the Platform Exercises workbook from the Customer Product Margin.csv file
   • Customer Id and Product Id as Business Key
   • Customer Id and Product Id as String
   • Average Margin as Float

E2ES (Entity to Entity Service)

1. POST E2E Mapping

Job Workflow Creation

1. Configuration > Job Workflows
2. Create New
3. Job Workflow Name = Refresh CPM
4. Configure Job Workflow Step 1
   • Step Type = E2E Copy
   • Job Step = RefreshCPMargin
5. Save

Action Button

1. On the Customer Product Margin worksheet, create a new action button to run the Job Workflow created above
   • Label = Refresh Margin
   • Job Workflow = Refresh CPM
   • Scope = CPM
   • Confirmation Message = 'This will refresh the Average Margin calculation.'
2. Run the Refresh Margin action button from the Customer Product Margin worksheet

Platform Exercises III

Customer Requirement:
The team needs a way to analyze and rank orders by customer and product, focusing on the most recent orders. By grouping data into customer and product attributes, they can gain deeper insights into sales performance and margins. The solution should allow easy access to detailed order data for specific customers and products while providing key performance indicators (KPIs) for margin analysis.

Acceptance Criteria
As an IQI user, when I review the average Customer Product Margin, I must be able to see the last 3 orders for that customer-product combo. I must also be able to see the following attributes for the Customer and Product respectively:
Customer:

• Customer Name
• Sales Rep
• Customer Type
• Industry Segment

Product:

• Product Description
• Product Level 1
• Brand

Master Detail & Column Groups

Master Detail

1. Open the Aggregate Worksheets workbook
2. Add an Order Rank calculated column on the Order Data worksheet calculating a numerical rank of the order by date for each customer and product combination
   • Value type = NUMBER

ROW_NUMBER()
OVER(PARTITION BY {CustomerId}, {RW0_ProductId}
ORDER BY {OrderDate} DESC)

3. Add a Top 3 calculated column on the Order Data worksheet calculating boolean flag for the most recent three orders for each customer and product combination
   • Value type = BOOLEAN

CASE
    WHEN {OrderRank} <= 3 THEN 1
    ELSE 0
END

4. Open the Platform Exercises workbook
5. Add a Top 3 calculated column on the Customer Product Margin worksheet with a boolean flag set to one that can be used to join with only the Top 3 records on the related order data
   • Value type = Boolean
   • Visible = unchecked

1

6. On the Customer Product Margin worksheet, create a new master detail configuration relating to the Order Data worksheet
   • Detail Workbook = Aggregate Worksheets
   • Detail Sheet = Order Data
   • Join Conditions:
     1. Customer Id equals Customer Id
     2. Product Id equals Product Id
     3. Top 3 equals Top 3
7. On the Customer Product Margin worksheet, expand the rows to confirm that the related most recent three Order Data lines now who up as the detail worksheet
8. Apply necessary formatting updates to the Order Data worksheet and hide columns that aren't relevant to show in the detail from Customer Product Margin; use the Set worksheet settings to default option on the Order Data worksheet to save the formatting

Column Groups

1. On the Customer Product Margin worksheet, add a relationship to the Customer Data worksheet
   • Join Type = Inner
   • Join Conditions:
     1. Customer Id equals Customer Id
2. Add the following related fields from the Customer Data worksheet to the Customer Product Margin worksheet
   • Customer Name
   • Sales Rep
   • Customer Type
   • Industry Segment
3. On the Customer Product Margin worksheet, add a relationship to the Product worksheet in the Input Data workbook
   • Join Type = Inner
   • Join Conditions:
     1. Product Id equals Product Id
4. Add the following related fields from the Product worksheet to the Customer Product Margin worksheet
   • Product Description
   • Product Level 1
   • Brand
5. Add a Customer Attributes column group on the Customer Product Margin worksheet grouping Customer Id and the related fields from the Customer Data worksheet
6. Add a Product Attributes column group on the Customer Product Margin worksheet grouping Product Id and the related fields from the Product worksheet

KPI Cards

1. On the Customer Product Margin worksheet, add a Min Margin KPI card calculating the minimum of the Average Margin column

MIN([AverageMargin])

2. On the Customer Product Margin worksheet, add a Max Margin KPI card calculating the maximum of the Average Margin column

MAX([AverageMargin])

Platform Exercises IV

Customer Requirement:
The team needs to create interactive visualizations and dashboards to track margin performance by customer and product over time. By providing easy access to historical margin data and customer-specific insights, they aim to enhance decision-making and monitor sales effectiveness. This solution should allow seamless navigation between different views and ensure that key metrics are easily accessible for both individual customers and overall performance.

Acceptance Criteria
Scenario 1:
As an IQI user, when I review Customer Product Margin, I must be able to view analytics displaying the average margin for a given customer-product combo over time (summarized month-by-month).
Scenario 2:
As an IQI user, when I review Customer Product Margin, I must be able to view analytics displaying the average margin (comprehensive, not product-specific) across all customers.

Tableau, Worksheet Analytics and REPORT

Analytics Navigation Creation

1. Create a new Tableau workbook using the existing Train_Tableau_View published data source found by going to Explore > Default
2. Rename the worksheet as CPM Sheet
3. Add a calculated field for Margin

SUM([Rw0 Revenue]-[Rw0 Cost])/SUM([Rw0 Revenue])

4. Create a time series visual showing Margin by month
5. Rename Rw0 Productid as Productid
6. Add Customerid and Productid to the Filters shelf
7. Modify the title to include a reference to the Customerid and Productid

Customer: <Customerid>
Product: <Productid>

8. Create a dashboard with the name CPM History and add the CPM Sheet to the dashboard
9. Remove the default Phone dashboard layout, set the size to Automatic, and the fit to Entire View
10. Publish the workbook as CPM in the Default project, selecting the option to Embed password for data source
11. Configure analytics navigation on the Customer Product Margin worksheet to reference the new published view
    • Label = Show Margin History
    • Workbook = CPM
    • Worksheet = CPMHistory
    • Query Parameters:
      1. Customerid = Customer Id
      2. Productid = Product Id
12. On the Customer Product Margin worksheet, right click on a row and click on the Analytics > Show Margin History link to confirm that the configured analytics work as expected

Analytics Worksheet Creation

1. Add a new Customer Margin dashboard to the CPM Tableau workbook
2. Create a new Customer Margin Sheet with a bar chart view showing AGG(Margin) sorted descending by Customerid and add it to the Customer Margin dashboard
3. Remove the default Phone dashboard layout, set the size to Automatic, and the fit to Entire View
4. Publish the workbook to save the new view
5. In the Platform Exercises workbook, create a new Customer Margin analytics worksheet:
   • Tableau Workbook = CPM
   • Tableau View = CustomerMargin
6. After creating the analytics worksheet, update the General Properties to deselect Add Toolbar and select Add Tooltips

Platform Exercises V

API Interactions

LEDS (Logical Entity Data Service)

1. GET Customer records
2. GET Customer records filtered
3. GET SalespersonData records
4. POST SalespersonData records
5. PATCH SalespersonData records
6. GET Customer view records (update Customer_View_xx)

EIS (Entity Import Service)

1. POST Import mapping
2. POST Upload the Salesperson Data 2.csv file
3. POST Import file
4. GET Import status
5. GET Import report
6. GET Import errors

EXS (Entity Export Service)

1. POST Invoke export (update Customer_View_xx)
2. GET Export status
3. GET Export report
4. GET Download the file

JOB Service

1. POST Run Refresh CPM Job
2. GET Job Status

REPORT Service

1. GET Customer Margin Report
2. GET CPM History Report

Platform Challenge Assignment

Create a new worksheet to contain the Customer Id, Customer Name and associated Sales Rep, Manager and Sales Region, with an Action Button that copies the data from the Customer Data worksheet for all customers in the US Sales Regions.