User defined views ****************** What are user defined views? ============================ User defined views (UDVs) are a way to create or modify views (e.g. ``bframe.prices`` or ``bframe.invoices``) in the bframe schema. While creating a brand new independent view, like ``bframe.my_random_pricing``, might not be useful combining this with the modification of default views can be impactful. For example, ``bframe.line_items`` is simple a union of two types of line item views. .. code-block:: sql -- bframe.line_items sql SELECT * FROM bframe.fixed_line_items UNION SELECT * FROM bframe.event_line_items This abstraction is intended so that new types of line items appended. This can be utilized by modifying ``bframe.line_items`` to include an additional union. .. code-block:: sql -- bframe.line_items sql (modified) SELECT * FROM bframe.fixed_line_items UNION SELECT * FROM bframe.event_line_items UNION SELECT * FROM bframe.my_random_pricing Now when consumers of the view employ ``bframe.line_items`` it will include the additional ``bframe.my_random_pricing``. Downstream applications like business intelligence and billing will automatically include the new pricing since they already use line items. This is a basic example, but it exhibits how UDVs can enable bframe to be extended and customized. Why are user defined views important? ===================================== bframe library intends to be an extensibile set of functions that enable the bframe framework. UDVs are the primary feature that enables this flexibility. If a user understands bframe, then just about anything can be modified to operationalize a unique business model. A goal of bframe is to handle most business modeling use cases but it is recognized that full coverage is impossible. UDVs exist to give users a path to resolution in the scenario that bframe misses a specific requirement. Without UDVs, users would have to settle for less than a perfect fit. How to create user defined views ================================ Creating a UDV is straight forward. A name and SQL template are the requirements for creation. To demonstrate let's create a new type of pricing and enable it for Wikipedia. Wikipedia wants to create a free tier for customers on their standard plan. They believe that folks who try out the product will definitely use more in the future if they are a good fit. The leadership team has decided they can give up roughly $100 in free usage a month without breaking the bank. Creating the new pricing ------------------------ The first step to creating a new type of pricing is creating a product type. We will be modifying an existing product but the steps would be similar for generating one from scratch. The product type determines how metering and rating is applied. The product type will be called ``'TIERED'``. .. tab-set:: .. tab-item:: Query .. literalinclude:: /_static/ex_sql/udv_feat_1_1.sql :language: sql .. tab-item:: Result .. csv-table:: :file: /_static/ex_tables/udv_feat_1_1.csv :class: bmodel-table :header-rows: 1 Now that the product exists we will modify the existing list prices to use it on the Standard pricebook. Additionally for this specific example we also need additional information to understand where a tier begins and ends. The ``pricing_metadata`` field can be utilized for storage in this case. Since we have two tiers we will need to create an additional list price to hold the free tier. .. tab-set:: .. tab-item:: Query .. literalinclude:: /_static/ex_sql/udv_feat_1_2.sql :language: sql .. tab-item:: Result .. csv-table:: :file: /_static/ex_tables/udv_feat_1_2.csv :class: bmodel-table :header-rows: 1 Create the tiered pricing views ------------------------------- Now that products and prices have been established, instantiating the UDVs is the last step to support the free tier. Tiered rated events ~~~~~~~~~~~~~~~~~~~ Rating each event with tiered pricing requires knowledge of the prior events. For example, if we want to know if the next event should be free or charged an understanding of the past events is necessary. Due to this, creating a new view called ``bframe.tiered_rated_events`` is a more straightforward solution. .. tab-set:: .. tab-item:: User defined view .. literalinclude:: /_static/ex_sql/tiered_rated_events.sql :language: sql .. tab-item:: Sample query .. literalinclude:: /_static/ex_sql/udv_feat_1_4.sql :language: sql .. tab-item:: Sample result .. csv-table:: :file: /_static/ex_tables/udv_feat_1_4.csv :class: bmodel-table :header-rows: 1 Tiered line items ~~~~~~~~~~~~~~~~~ Once we have rated events, they need to be aggregated to create line items. This can be accomplished by updating the existing ``bframe.event_line_items`` view or making a new one called ``bframe.tiered_event_line_items``. We will opt for the latter. .. tab-set:: .. tab-item:: User defined view .. literalinclude:: /_static/ex_sql/tiered_event_line_items.sql :language: sql .. tab-item:: Sample query .. literalinclude:: /_static/ex_sql/udv_feat_1_5.sql :language: sql .. tab-item:: Sample result .. csv-table:: :file: /_static/ex_tables/udv_feat_1_5.csv :class: bmodel-table :header-rows: 1 Modify line items ~~~~~~~~~~~~~~~~~ To fully integrate the tiered pricing into bframe the UDVs must be included in the top level default views. In this case that means appending ``bframe.tiered_line_items`` along with other line item types within ``bframe.line_items``. .. tab-set:: .. tab-item:: User defined view .. literalinclude:: /_static/ex_sql/mod_line_items.sql :language: sql .. tab-item:: Sample query .. literalinclude:: /_static/ex_sql/udv_feat_1_6.sql :language: sql .. tab-item:: Sample result .. csv-table:: :file: /_static/ex_tables/udv_feat_1_6.csv :class: bmodel-table :header-rows: 1 Exploring the change -------------------- The free tier is live and the impact can be seen across customers. Let's see what it looks like for one of our larger customers. .. tab-set:: .. tab-item:: Result .. csv-table:: :file: /_static/ex_tables/udv_feat_1_7.csv :class: bmodel-table :header-rows: 1 .. tab-item:: Query .. literalinclude:: /_static/ex_sql/udv_feat_1_7.sql :language: sql As expected only $100 of savings were accomplished which is only a small part of their total cost. To ensure Wikipedia isn't losing out too much we can do a query to see the impact to the bottom line. .. tab-set:: .. tab-item:: Result .. csv-table:: :file: /_static/ex_tables/udv_feat_1_8.csv :class: bmodel-table :header-rows: 1 .. tab-item:: Query .. literalinclude:: /_static/ex_sql/udv_feat_1_8.sql :language: sql The relative difference between a free tier and without one is about 14%. This outcome is much larger than the exective team expected when they first proposed the change. Due to this result, the change isn't rolled out to production and a free tier is no longer in the strategy for Wikipedia.