Thursday, January 23, 2014

Technology Decision Factors

When making a decision about what technology to use for a project, product, or service architecture there are many factors to consider that will impact the total cost in ownership for a company.

Skilled Workforce

Risk: Availability, Salary Cost, Training Cost, Churn Cost

Commentary: The skilled workforce factor sometimes gets forgotten in the hunt for a technology.  If a technology is highly specialized it will decrease chances of hiring someone with the appropriate skill level to deal with the technology.  It will also increase the cost of training and/or salary.  There can be a high churn rate for the skilled resource as they can get head hunted by other companies or they become concerned about their career as the skill set exercised is too narrow for their long term  career goals.  Skill sets must span both Development, Test, and Operations as well as training for end-users of the technology if necessary.

Technology Support

Risk: Code Stability, Documentation Quality, Tech Support, Market Survival, Backwards Compatibility

Commentary: If a technology is bleeding edge or produced by a one man band, it can suffer from poor documentation, poor technical support, instabilities, and may not survive its incubation period.   It is also important to have the assurance that the company producing the technology are committed to backward compatibility so you don't lose your coding investment.

Operational Cost 

Risk: Deployment and Provisioning Costs, Maintenance Costs, Stability, Tech Support, Hot Patch Support

Commentary: The day to day operational costs of technology is very important to take into consideration.  On-Site hardware costs, installation costs, backup costs, failover costs, global data and processing distribution costs, tuning costs, hardware maintenance costs, Software upgrade costs, hot patch costs, production trouble shooting costs, and operation staff costs is a lot to calculate.  On-Site may have a much hire cost of ownership then subscribing to cloud services.   Cloud Services provide a much simpler and predictable cost model and usually provide elastic provisioning to grow or shrink to your needs.  Something to really contemplate when selecting your technology platform.

Security Cost 

Risk: Customer Safety, Business Safety

Commentary: Data and processes in the hands of the wrong people can cause irreversible damage to a customer or the business.   Personal Identification Information (PII) and Business Critical Information must be secured and not taken lightly.  Is the usage of the technology managing such information?  Can the Technology tightly secure the information?   Can it keep up with latest security features and patches to protect against security breaches?  Can it detect security breaches?  Same questions for any sensitive processes.

Business Functional Requirements

Risk: Functionality,  Elastic Scalability, Reliability, Availability 

Commentary: I placed this last because these are the most obvious factors to consider.  Does the technology provide the proper level of functionality to support the business?   Can it scale with the company as its needs grow and shrink through out time?  Its is stable and reliable?  Can it deal with the 99.9, 99.99, 99.999 uptime availability requirements?   What is the Mean Time To Recovery (MTTR)?


Bottom Line: Remember that you get what you pay for, but don't pay for more than what is warranted.  And in a competitive world, the total cost of ownership of your enterprise technology balanced with the need to enable your company to compete is critical to get right.


Please See the following docs for the cost benefits for cloud technology solutions.
Cloud Economics
Cloud Platforms for Business Owners
Cloud Optimization - A Framework for making business decisions about cloud computing

Sunday, January 19, 2014

On-Site + Azure Cloud = Hybrid

On-Site + Azure Cloud = Hybrid:  There are many reasons why a company will not place there enterprise in the cloud quite yet.
  • The data warehouse may contain business critical information that would be detrimental to the company if exposed to the public and the company is not comfortable with atomic data being hosted in the Cloud yet until security has improved.
  • Or it may be that the ETL process may end up being too brittle due to unreliable internet connection in the area and latency is important.
  • Or the ETL process may require more bandwidth then is available in the area.
So this Hybrid Topology may prove to be useful compromise.  It ETLs a small aggregated set of data into a datamart up in the Azure cloud for an analytics server and a reporting tool, also hosted in Azure, can access.  This leaves the Enterprise Data Warehouse On-Site close to the physical business.     

Thursday, January 16, 2014

Traditional Data Warehouse Design

Traditional Data Warehouse Design:  Before Massive Parallel Processing (MPP) was available to the masses, a data warehouse was typically housed in a single Server.  It maybe clustered with another server for fail over proposes.  This design is good for your first steps in data warehousing, but is limited to the volume of data it can service.   SQL Server 2012 database size limit is about 500 TB.   But saying that you are still limited by the number of CPUs and the amount of memory your service has which limits your performance.    Its not a scalable solution in a world which the volume of data is accelerating exponentially and the demand for low level adhoc discovery analysis is on the rise.  

Features It Should Have:
  • Conformed Dimension Definitions
  • Conformed Measure Definitions
  • Conformed Atomic Level Fact Definitions
  • Managed Dimensional Data
  • Documented Data SLAs going In and Out of the Warehouse
  • Documented Data Lineage
  • Uniform ETL Pipeline Process Management
  • Uniform handling late arriving data
  • Uniform Error Handling
  • Uniform Variance Checks
  • Uniform Security Policies and Management
  • Shared Codebase and Framework
  • Shared Environment
  • Uniform backup and replication of data
  • Uniform Data Life Cycle Management:  Data Archival and Deletion as it ages 

  • Keep in mind that most businesses are in a business information warfare.  More than likely you are competing in a shrinking market as more and more businesses are competing for the same customers.  So any leverage you get is critical to your success.  If you don't leverage big data then your competition will.

    Commentary: The Case of the Missing Enterprise Data Warehouse

    Symptom: You have 100 reports that are unreliably generated for you and the reported numbers conflict from report to report.

    Root Cause:  Your company has no Enterprise Wide Data Warehouse.

    How Did that Happen?   Here is the Typical Evolving Scenario: 
    You or your management commissions someone to provide a specific report.  That report was quickly put together and placed on a server under someone's office desk.  Rinse and Repeat...   Eventually you realize you have a series of reports that give conflicting numbers.  So you have someone investigate why and you get the following answer:  You have 100 Reports coming from 10 Reporting servers under 10 different office desks across 3 office sites.   There are 20 different Extraction, Transformation, and Loading Pipelines all with a different codebase and with different logic.  Looking even deeper you may find that the ETL Pipeline's error handling, late arriving data processes, cleaning processes, data enrichment processes, the data conformation processes, and data variance checks all vary in design or just plain absent.   Then the real bad news hits you:  What you thought were the same measures in different reports are not the same.  They technically have different  meanings even though the have the same name.

    No Data Warehouse Diagram:

    Hidden Costs in Not Having a Enterprise Wide Data Warehouse:
    • Increased Development Costs for maintaining multiple and fragmented codebases doing same thing.
    • Increased Operations and Development Costs in troubleshooting late or missing reports and erroneous numbers.
    • Increased Business Risks in data landing in the hands of the wrong people due to your report servers not being properly secured.  This results in these servers being hacked or just plain walking out the door.
    • Decreased Business Opportunities and Optimization caused by relying on in accurate and inconsistent reports.
    Solution:  Build a Enterprise Wide Data Warehouse by providing the following.
    • Conformed Dimension Definitions 
    • Conformed Measure Definitions
    • Conformed Atomic Level Fact Definitions
    • Managed Dimensional Data
    • Documented Data SLAs going In and Out of the Warehouse
    • Documented Data Lineage
    • Symmetrical ETL Pipeline Process Management
    • Uniform handling late arriving data
    • Uniform Error Handling
    • Uniform Variance Checks
    • Uniform Security Policies and Management
    • Shared Codebase and Framework
    • Shared Environment
    • Uniform backup and replication of data
    • Uniform Data Life Cycle Management:  Data Archival and Deletion as it ages
    Traditional Data Warehouse Diagram:

    Thursday, January 09, 2014

    Pipeline Framework Fundamentals

    An ETL framework provides and enforces pipeline standards across the data warehouse for the purposes of increasing stability and reliability.   The following features should be part of the framework.
    • Pipeline Coordination and Scheduling: Automatically manage parallel processes against data that are located at specific compute nodes.   Also deal with late arriving data.
    • Reentry: Ability to restart a process at the point of failure.
    • Data Life Cycle Management: Automatically archive aging data and the removal of expired data from the warehouse.
    • Standard Import and Export: Standard handling of exporting and importing data from and to external landing locations and related error handling and protocols. 
    • Data Lineage and Traceability: Tracking the flow of data from its initial entry into the warehouse through the pipelines to its final destinations.  This also needs to enable the ability to determine downstream impacts when data is missing or processes fail. 
    • Data Service Level Agreement Management: Track the scheduled window of time a dataset should be available for downstream usage and what internal and external parties depend on the data.
    • Data Replication Management: Automatic replication of specific data for backup purposes. 
    Checkout http://hortonworks.com/hadoop/falcon/ for Hadoop based data warehouse pipelines.  This sounds awfully promising and looks like it covers most everything.  Due out Q1 2014.  Preview is available.
    Checkout http://pragmaticworks.com/Products/BI-xPress.aspx for SSIS based data warehouse pipelines.
    Checkout http://ssisetlframework.codeplex.com/ for SSIS based data warehouse pipelines.  It doesn't cover everything but its a great place to start and its free.

    Baseline Conceptual Models: Warehouse Shard Configuration Model

    Warehouse Shard Configuration Model:  Data is partitioned or distributed across compute nodes.  This information is critical for bringing processes to the data located on the each compute node and executing it.  This model is dependent on Pipeline Configuration Model.

    Please see (Baseline Conceptual Models Commentary) for further details on what conceptual models are to be used for.

    Wednesday, January 08, 2014

    Baseline Conceptual Models: Pipeline Configuration Model

     
    Pipeline Configuration Model:  Pipelines in a data warehouse will have many scheduled jobs that process data from raw to atomic fact to a final report.  Some processes are a dependent one or more datasets and can output one or more datasets.  Were these processes are executed is dependent on where the data is located.   This model is dependent on Data & Schema Model and Warehouse Shared Configuration Model.

    Types of Pipelines:  There are different pipelines for different stages of the data.
    • 3rd Party Dataset Pipelines are extracting, minor transformation, and loading processes that pull data from external companies.
    • Raw Log Pipelines are extracting, minor transformation, and loading processes that pull data provided from other systems within the company.  Examples are Website Logs, Sales or Purchase Orders, and Inventory Movement.
    • Master Data Domains Pipelines are extracting, minor transformation, and loading processes that pull from a Master Data Service or collected from across multiple company systems. 
    • Dimension Pipelines are transformation processes to transform Master Data Domains into dimensions.
    • Staging Pipelines are transformation processes that cleanse, enhance, or conform data.
    • Atomic Fact Pipelines  are transformation processes that transform logs into atomic facts.
    • Aggregated Fact Pipelines  are transformation processes that transform atomic into aggregate facts.   
    • Report Pipelines  are transformation processes for transforming atomic or aggregate facts into reports. 
    Note:  Pipeline Data entity provides information about the input and output data going into or out of a process and is important for in tracking course grain data lineage.

    Please see (Baseline Conceptual Models Commentary) for further details on what conceptual models are to be used for.

    Baseline Conceptual Models: Data Service Level Agreement (SLA) Model

    Data Service Level Agreement (SLA) Model:  When sourcing data it is a good idea for the data provider and the downstream customer to document and agree upon the specifics of the data that is to be provided and the scheduled window of time it is expected for the data to be available for download.  This helps provide two things:  Set expectations properly between the data provider and the downstream customer and second it enables the ability for the data provider to see who is impacted by delays in the pipeline and communicate to the downstream customers accordingly.  This model is dependent on Data Catalog & Schema Model and the Party Model.

    Data SLA:  SLA's should be predetermined by the data provider based on the average time it takes for the pipeline to process the necessary data and make it available at a drop point plus some buffer time for the outliers.  A customer then agrees upon the SLA or make a request for a modified SLA.  Modifying the SLA may require new development or tuning of the pipeline.

    Data SLA Landing Locations:  SLA's also includes one or more drop locations on a specific reoccurring schedule.

    Please see (Baseline Conceptual Models Commentary) for further details on what conceptual models are to be used for.

    Baseline Conceptual Models: Data Catalog and Schema Model

    Data Catalog & Schema Model:   In a data driven pipeline for a data warehouse the data definitions stored in a meta data repository and is used to speed up pipeline development as well as enforce design conformity.  Entities are organized into a category hierarchy for simple exploratory navigation.   Throughout the live cycle of an entity can be revised several times.  Each revision of the entity will have one or more attributes.   Attributes are first predefined independently from any entity.  Then each entity will bind to the appropriate predefined attributes.  This enforces a solid naming convention and definitions throughout the pipelines in the data warehouse.

    Entity Types:  In a data warehouse data can be classified into different types to signify the value it brings to the warehouse.
    • 3rd Party Datasets are upstream data that are provided by external companies.
    • Raw Logs are upstream data that are provided by other systems within the company.  Examples are Website Logs, Sales or Purchase Orders, and Inventory Movement.
    • Master Data Domains are data that came from a Master Data Service or collected across multiple company systems.  This kind of data is used to build dimensions.
    • Dimensions are data elements that enable filtering, grouping and labeling facts (measures).
    • Staging Datasets are data that has been cleansed, enhanced, or conformed and ready for further processing.
    • Atomic Facts are the lowest level of grain for a set of measures.   Measures can be filtered, grouped, and labeled by a predefined set of dimensions
    • Aggregated Facts are an summarized set of measures that have been summed up from an atomic fact.  Used for business snapshot purposes like end month or end of year reporting or can be used for performance tuning.   
    • Reports are usually simple facts (sometimes non-pivotable) that the end user uses as a statement of measure on key performance (KPI) for the business. 
    Risk Assessment:  In an enterprise data set there can be information the could cause damage to the business or customer if released to the outside world.   Each attribute should be assessed in order to evaluate the risks for the company.

    Data Variance Check Definitions:  An attribute to an entity may have a

    Attribute Types:  Each attribute in a entity has a specific meaning in a data warehouse.  
    • Dimension Keys are surrogate key that is used to make to a dimension. 
    • Business Keys are values used to lookup the dimension key using in the key mapping phase of an ETL.  These are found in the upstream source streams.
    • Measures Attributes are numeric values representing a count or output of an equation.
    • Regular Attributes are general values used for pass-through processes or user consumption.
    Data Lineage:  Tracking data lineage from one data source to another within the warehouse is a critical to track provenance of the data and enables faster diagnostics when data goes bad.  This a fine grain data lineage feature.

    Please see (Baseline Conceptual Models Commentary) for further details on what conceptual models are to be used for.

    Baseline Conceptual Models: Repair Order Model

    Repair Order Model:  A repair order can be for a customer or can be an internal repair order for specific number of non-functional inventory.  A repair assessment is performed to determine what needs to be repaired.  If the repair is not under warranty then a repair quote is made and given to the customer.  If the customer agrees to the repair quote a repair sales order is created for "Parts and Labor" incurred by the repair.    This model is dependent on Product Service Request ModelInventory Control Model, Sales Order Model, Repair Assessment Model, and Repair Quote Model.

    Please see (Baseline Conceptual Models Commentary) for further details on what conceptual models are to be used for.

    Baseline Conceptual Models: Repair Quote Model

    Repair Quote Model:  Before repairs not covered by warranty can be performed a quote is provided to the customer to agree upon before the repair is performed.   The repair quote includes "Parts and Labor".  This model is dependent on Repair Order Model, Product Offer and Price Model.

    Note:  Products are goods and services.  Labor falls under service.

    Please see (Baseline Conceptual Models Commentary) for further details on what conceptual models are to be used for. 

    Baseline Conceptual Models: Repair Assessment Model

    Repair Assessment Model:  Before a repair can be performed on a part a series of tests will need to be performed.  These tests are predefined per part along with a set of predefined set of results that provide suggested repair and labor solutions.  This model depends on Repair Order Model and Part Bill of Material (BOM) Model.

    Please see (Baseline Conceptual Models Commentary) for further details on what conceptual models are to be used for.  

    Baseline Conceptual Models: Technical Support Request Model

    Technical Support Request Model:   Once it is determined that technical support is required a request is made and the customer is patched through or a technician may need to be dispatched.    There can be many Technical Support Requests relating to a Service Request.   This model is dependent on Service Request Model, Party and Account Model, and Location Model.

    Please see (Baseline Conceptual Models Commentary) for further details on what conceptual models are to be used for.

    Baseline Conceptual Models: Call Incident Model

    Call Incident Model:  When a company sells products it is necessary to provide assistance to their customers in support of the goods and services bought.   So a support service is created to take incoming customer calls.  The people that take on the support calls maybe in-house or outsourced to one or more companies for cost savings.  The goal of the support service to increase customer satisfaction through solid product support.  Each call made to the call center is tracked as a "Call Incident".   A call may result in one or service requests or linked to an already open service request if the caller is calling back about an already open service request.   Callers can be individual customers or the can be companies.  Callers also can be vendors or partners that are in need to repair or return products.  This model is dependent on Service Request Model, Party and Account Model, and Location Model.

    Please see (Baseline Conceptual Models Commentary) for further details on what conceptual models are to be used for.

    Baseline Conceptual Models: Service Request Model

    Service Request Model:  A service request is created once a caller has been determined that some kind of action is needed to be taken (Return, Repair, Exchange, or Technical Assistance).  The service request will record further information about the product(s) in question.  This information can be in a form of registration of the product to service a small customer or it can be a list of products that need to be returned from a vendor or partner.  Multiple call incidents can be associated with a single service request.  This model is dependent on Technical Support Request Model, Repair Order Model, Party and Account Model, and Call Incident Model.

    Return Material Authorization (RMA):  Before a customer can successfully return a product the customer must call the support service and receive a RMA number that is placed on the returning package to make sure that product is associated properly to the customer and the service that is expected on the product (Return, Repair, Exchange).  Most companies will just return to sender if this is not on or in the shipping box.

    Product Evaluation:  When a product is returned it is evaluated to determine if the warranty has been violated.  It then goes further to determine if the product is repairable or is to be scrapped.  To speed up service sometimes it can be determined to exchange the product rather then repair it depending on the product and issues at hand.  The product may then go into inventory to away repair.  Once an evaluation has been made an repair order may be issued.

    Note:  Exchanges are just a simple creation of a Return Sales order and a Sales Order.

    Please see (Baseline Conceptual Models Commentary) for further details on what conceptual models are to be used for.

    Monday, January 06, 2014

    Baseline Conceptual Models: Subscription Invoice Model

    Subscription Invoice Model:  Subscriptions are invoiced every billing period (monthly or yearly).  This bill includes metered billable services and seats.   This model is dependent on Entitlement Consumption Tracking Model.

    Please see (Baseline Conceptual Models Commentary) for further details on what conceptual models are to be used for.  

    Baseline Conceptual Models: Sales Invoice Model

    Sales Invoice Model:  Invoices are an statement to the customer of the bill.  Invoices can be adjusted to satisfy customer issues or a sales order can be split into multiple invoices due to availability of items that are able to be shipped.  This model is dependent on Sales Order Model.

    Please see (Baseline Conceptual Models Commentary) for further details on what conceptual models are to be used for. 

    Baseline Conceptual Models: Shipping and Receiving Model

    Shipping & Receiving Model:  A delivery order may contain a small number of items that can be shipped in a single package.   Or a delivery order may contain many items in which may need to be split into many packages.  And a company may save shipping costs by bulk shipping many delivery orders that are going to the same distribution centers.  A shipment will have schedule which lists all the locations a shipment will be stopping at and the estimated time it will arrive for each location.  And finally shipments may be tracked by tracking the trucks and containers they are in by GPS.   This model is dependent on Delivery Order Model, Location Model, and Party and Account Model.

    Please see (Baseline Conceptual Models Commentary) for further details on what conceptual models are to be used for.

    Baseline Conceptual Models: Delivery Order Model

    Delivery Order Model:  Sales orders can split into one or more delivery orders.  This depends on if the full sales order can be fulfilled certain items are back ordered.   The available items are delivered and the back order items are delivered when the became available in inventory.   Delivery orders track both non-serialized and serialized parts for security purposes.  This model depends on Sales Order ModelPart Manufacturing Model, Part Serialization Model, and Inventory Control.

    Please see (Baseline Conceptual Models Commentary) for further details on what conceptual models are to be used for.

    Baseline Conceptual Models: Purchase Order Model

    Purchase Order Model:  A company will purchase products and services in support of its business.  This model covers approved list of SKUs from vendors and manual purchase orders in which the items and services are not list on the approve list.   This model depends on the Party and Account Model.

    Please see (Baseline Conceptual Models Commentary) for further details on what conceptual models are to be used for.

    Baseline Conceptual Models: Production Order Model

    Production Order Model:  A company will make a production order to a manufacture and charge it to a cost center.  This model depends on Party and Account Model and Part Bill of Material (BOM) Model.

    Please see (Baseline Conceptual Models Commentary) for further details on what conceptual models are to be used for.

    Baseline Conceptual Models: Sales Order Model

    Sales Order Model:  This is standard sales order model which covers basic sales and returns.  It also covers consignment orders to take care of retailer partners ordering gift cards and deferring the cost of the gift cards until they are sold and activated at the point of sale.  Repair sales orders are used specifically for charging the customer parts and labor for repairs not covered by warranty.  This model is dependent on the Party and Account Model and the Product Offer and Price Model.

    Please see (Baseline Conceptual Models Commentary) for further details on what conceptual models are to be used for.

    Friday, January 03, 2014

    Baseline Conceptual Models: Inventory Control Model

     
    Inventory Control Model:  Tracking the movement of inventories is critical for product efficiency and security reasons.  There are several types of inventory movements centered around manufacturing, shipping, orders, and manual adjustments.  This model is dependent on Part Manufacturing Model and Part Serialization Model

    Manufacturing Transactions: During the manufacturing process inventory transactions are made as parts or built and consumed.  This includes tracking transactions against serialized parts.
    • Goods Built are transactions that are triggered once a part has been built.
    • Goods Consumed are transactions that are triggered once a part has been used to build another part.  
    Shipping Transactions:  During any shipping process inventory transactions are made to specify that goods are issued out of inventory or received into inventory.  A special Stock Transport transaction is made when transferring stock from one inventory location to another and the transaction will tie both ends of the shipping process.
    • Goods Received are transactions that are triggered once a part has been received into inventory.
    • Goods Issued are transactions that are triggered once a part has been removed out inventory. 
    • Stock Transport are transactions that are triggered when a stock transfer is performed to balance inventory.
    Reservation Transactions:  When a sales order is performed a reservation is made on inventory to guarantee that inventory is reserved for a given sales order so that inventory is not over sold. 
    • Reservation Issued are transactions that are triggered when a sale is performed.
    • Reservation Fulfilled are transactions that are triggered when inventory has been issued for sales order.
    • Reservation Cancelled are transactions that are triggered when a sales order is cancelled and before the inventory is pulled.  
    Manual Adjustment Transactions:  Manual audits of inventory are performed regularly in order to make an accurate accounting of the inventory.   When the audit turns up a different number then what is recorded a manual transaction is made to the inventory record to adjust the count.

    Please see (Baseline Conceptual Models Commentary) for further details on what conceptual models are to be used for.

    Baseline Conceptual Models: Part Serialization Model

    Part Serialization Model:  Some parts are serialized to track each instance of the part.   Serial numbers are guaranteed to be unique for that part and is tracked in a serial master.   Each serial number also has a Bill of Materials that tracks exactly what Serialized and Non-Serialized Parts make up the part.  As each part is created it is placed in inventory.   Over a life cycle of a serialized part it can be returned for repair.  Each time it is repaired an As-Maintained Bill of Materials is built to track what was repaired.

    Serial Part Master:  A serial master will track a part's Serial Number as well as the original manufacture's serial number when re-serialization is required.

    Product Key Card Master:  Product Keys are given to manufactures to place on Cards, DVDs, or inside instruction jackets.  Tracking the packing of the product keys in what part through a parts bill of materials is important for security purposes.  Though this feature may be a luxury for some manufactures.  

    Please see (Baseline Conceptual Models Commentary) for further details on what conceptual models are to be used for.

    Baseline Conceptual Models: Part Manufacturing Model

     
    Part Manufacturing Model:  Parts are manufactured at one or more plant Facility Locations.  The plants have production lines that produce parts in Lots or vats.  Lots are important to track for product quality management.  Parts can vary from lot to lot.  For example:  A fabric can have a dye that slight varies from one lot to another.   This is why people are interested in dye lots for fabric.  The manufacture will consume other parts in inventory to manufacture other parts and then these new parts are placed into inventory at a specific location.  A Non-Serialized As-Built Part Bill of Materials is created for parts.  Please see Part Serialization Model for Serialized As-Build Part Bill of Materials.  This model is dependent on Part Bill of Materials (BOM) Model.

    Please see (Baseline Conceptual Models Commentary) for further details on what conceptual models are to be used for.
          

    Baseline Conceptual Models: Part Bill of Material (BOM) Model

    Part Bill of Material (BOM) Model:  In the manufacturing world parts are the center of their world.  A part makes up a product that a company would then sell to customers.  A company would design a part by providing a Part Specification.   Each part can be made of one or more parts or raw materials.  A company will make a Planned Bill of Material that lists all the parts that is expected or suggested to be used.  This plan is then sent to each manufacture in which they in turn will provide there own Manufacture Bill of Material back to the company.  This represents what the manufacture has scheduled to use when building the part. A Maintenance Bill of Material may be also given for use by a repair center to aid in repairs of the part.  Each part may have a Part Revision when the design doesn't change the Form, Fit, or Function (Known as the 3 F's).   Each part can have a Substitute Part in which can be swapped out for as a simple substitution.  And each part can have a Related Part that is similar, but not exact and can't be used as a simple part swap.  This model is dependent on Party and Account Model and Part Manufacturing Model.

    Types of Parts:  There are many types of parts.  When a part is created it is assigned a Part Number.  When that part is manufactured it is labeled with that part number.   Through the life cycle of a part it can change its part number.  The part number can change from Semi-Finished Good part number to a Finished Good Part Number when the part is completed.  It can change from Finished Good to Non-Functional Unit to Refurbished Unit or Scrapped Unit when the product is returned for repair.   The reason the part number changes is to keep track of the part's condition.  This keeps from accidently selling a scrapped unit to a customer.  It also allows for proper valuation of inventory so that Refurbished or Scrapped Units can be valuated with a monetary value lesser than a Finished Good.  Saves on taxes and what not.
    • Raw Material parts are typically things like steel, wood, silver, silicon, petroleum, or oxygen in which further processing is required to make it into usable part.
    • Sub-Assembly parts are parts that can be used in building another part or can be used to repair a part.
    • Semi-Finished Goods could be parts that are mostly finished but needs to be further processed like specialization or branding to make them a Finished Good.  They can be replacement products without all the fancy packaging to lower costs in repair exchanges. Sometimes I've heard semi-finished goods as being referred to as Brown Box Units.
    • Finished Good parts are usually parts that are considered to be sellable to the end customer. 
    • Digital Good parts are digital bits that are to be placed on a device or some form of medium.
    • Non-Functional Units are parts that are returned for repair.  They are marked with a Non-Functional Unit part number and either placed in a queue for repair or scrapped based on their initial functional evaluation.  Sometimes these Non-Functional Units could be held in inventory until the company decides that replacement demands require allocating resources to repair units.  Its a means of delaying extra costs.  This means that sometimes when you return a product you may not get the same unit back.  It depends on what the product is and how important it is for the customer to get that same product back.  
    • Refurbished Units are parts that have been returned and have been successfully repaired.  They are given a Refurbished Unit part number to signify that this unit has been repaired.   Note that sometimes units are returned at the same time an exchange unit (Refurbished Unit or a Semi-Finished Good) is sent out simultaneously to speed up service for a customer.  This means that an inventory of Refurbished Units can be on hand at any repair or exchange center.    
    • Scrapped Units are parts that are considered non-repairable and are scheduled to be destroyed.  Once these units are destroyed they are removed from inventory tracking and accounting does some form of write-off.
    Products vs Parts:  I've made a division in the concepts between Product and Parts.  When a product is defined it may or may not be mapped to a top level part in which has a bill of materials defining all the parts making up that part. The difference between Parts and Products is that a part is a concept relating to the manufacturing world where as products are more for sales and marketing.   Sales and marketing can make products without manufacturing any parts.  And not all parts are of interest to sales and marketing. Sometimes business will define products to market and allow customers to pre-order before engineering has finished the part design and setup for manufacturing and thus no part number is officially assigned yet.  So it is important to keep these two concepts separate.   Also part numbers are not SKUs.  SKUs are a marketing concept and a part number is about the physical part.  Sometimes people get those two things mixed up or combine them.  This would not a good idea.  There could be Many SKUs that represent the same part in which the SKU is marketing to different demographics with different price points.    
          
    Please see (Baseline Conceptual Models Commentary) for further details on what conceptual models are to be used for.

    Baseline Conceptual Models: Entitlement Consumption Tracking Model

    Entitlement Consumption Tracking Model:  Cloud services need to track the usage of benefit entitlements a customer account is consuming in order to enforce metered benefits and client license usage.  Tracking consumption also enables monitoring of feature usage in which can be used in analysis to determine what features are used most frequently and tune the cloud service accordingly.   Its can also provide insight as to what features are not used as much which can trigger further research into why a feature is not used as expected.  The feature could be redesigned or removed to save maintenance cost.

    Please see (Baseline Conceptual Models Commentary) for further details on what conceptual models are to be used for.  

    Baseline Conceptual Models: Customer Product Benefit Entitlement Model

    Customer Product Benefit Entitlement Model: Once a customer buys a product or signs up for a cloud service the customer is then entitled to certain benefits.  Some benefits is granted to the customer's account and any member in the account automatically inherits the entitlements and some entitlements are assignable and only can be used be a account member if the entitlement is assigned to that member.   Each entitlements behavior is dictated by the benefit type and quantity of benefits are managed accordingly.  This model is dependent on Product Specification Model and Product Offering and Pricing Model.

    For example: 
    • Metered benefit entitlements start of with a specific quantity of time or space and based on usage logs associated with the customer's account the entitlement quantities are reduced accordingly.  
    • Client license benefit entitlements start off with a given number of client licenses in which are assignable to an account member.  The number of client license assignments are limited by the number granted to the customer's account.  
    • General benefit entitlements are features that are neither metered limited by seat.  These are features that are enabled for the overall customer account.
    Please see (Baseline Conceptual Models Commentary) for further details on what conceptual models are to be used for.  

    Baseline Conceptual Models: Digital Good Download Model

    Digital Good Download Model: Software as a Service (SAAS) has made downloading software or other digital goods as a matter of course.  A company will provide golden bits to a download provider that is capable of dealing with large scale downloads and concurrent peaks.  This model provides a means to track the location, download service providers, and the map the digital goods back to one or more product benefits.   The model is dependent on the Product Specification Model and Party and Account Model.

    Please see (Baseline Conceptual Models Commentary) for further details on what conceptual models are to be used for.

    Baseline Conceptual Models: Product Key Unlock & Activation Model

    Product Key Unlock and Activation Model: Product Keys need to be activated by the customer.   This activation should be tracked to deter fraud and also enable cloud features depending on the type of product key.  This model is dependent on the Product Specification Model.

    Product Keys: Products are activated using product keys.   Product keys are mastered by the company and are placed on activation cards, DVD cases, or downloaded from a product key vault by the customer.
    • Unsecured reserved product keys are provided to packaging manufactures so that they can package up your product and place the product keys inside the box or DVD.   
    • Secured reserved product keys are used on product cards that a customer can buy at a store.  The product keys are unlocked at the point of sale by the store so that the customer can use it to activate the product.  
    • Unreserved product keys are keys that are directly downloaded by the customer from a product key vault and may be generated on the fly.
    Please see (Baseline Conceptual Models Commentary) for further details on what conceptual models are to be used for.

    Baseline Conceptual Models: Product Registration Model

    Product Registration Model:  Before a product can be serviced a company usually requires you to register the product with them.  A registration usually includes name, postal address, points of contact, and various optional demographic information.   This information can be used in marketing analysis to better understand their customers in order to better their products and better their marketing of the products.   This model provides a means for a customer or a proxy service to register the product on behave of the customer.   Registration can be of a serialized product or just the part number when the serialize number doesn't exist.  I also allow for transferring registrations when appropriate.  This model is dependent on Part Bill of Material (BOM) Model  and Part Serialization Model.

    Please see (Baseline Conceptual Models Commentary) for further details on what conceptual models are to be used for.

    Thursday, January 02, 2014

    Baseline Conceptual Models: Product Offering and Pricing Model


    Product Offering & Pricing Model:  Products must be offered with a price to be sold.  This model depends on the Product Specification Model.  

    Product Offering:  A product Offering represented by SKU or UPC.  A product can be assigned many product offerings (SKUs) in order to market the product to different demographics and regions. The reasons are that a packaged product may be packed with English, Spanish, and French instructions.  The package may also be packaged with a wall outlet that is compatible for Canada and America only.   So this product should only be marketed to USA and Canada.   You might find that a product may be offered to only a specific demographic as well.  The product may be packaged with a Student or School type packaging and product keys offered at a specialized price.

    Benefit Quantity Offer:  A product offering can also specify the quantity of the benefits offered for a product.  This enables the ability to adjust Benefits Quantities per product offering.  These types of benefits require a cloud service to manage and enforce.

    Product Offer Price: A product offering can be assigned one or more prices over a period of time through promotions of the product. 

    Additional Promotional Benefit Quantity Offer:  A product offering could be promoted with a additional benefits specific to that offer.  These types of benefits require a cloud service to manage and enforce.

    Promotion:  Promotions can span over one or more product offerings.  
    • Product Specific Promotions are used to target specific products to a market.  
      • Standard Pricing Promotions are the standard daily price for a product offering. 
      • Sales Promotions are discounted pricing for a limited time.
      • Coupons are special discounts that customers can apply at the time of purchase and are valid for a limited time.
      • Targeted promotions offered discounts to a specific customer account.
    • Non-Product Specific Promotions are used in general across board of a sales.  Example: 50% off all products. 
    • Special Pricing Agreements are specifically created with one or more promotions that are applied to a company for certain products discounts.    
    Product Bundling:  Products can be bundled post manufacturing as a form of promotion or fire sale.

    SKU (Stock Keeping Unit):  The is used to track a product in inventory.   But as time has passed and the commerce has become a much more complex environment this term has been extended and many times abused.   I would classify SKUs as a sales and marketing concept in which many SKU's can be created for a product depending on the market demographic it is targeted for.

    Note 1:  You will notice that the Benefit Quantity Offer Entity has a suggested Primary Key of Product ID, Offer ID, Digital Benefit Specification ID in which Product ID is BOLD.   Product ID is an example of key folding in which the Product ID from Product folds into Product ID from the Product Offer Entity.  I personally like to call this a data harmonic.  Hope that helps you understanding some advanced modeling techniques that are demonstrated in this model.

    Note 2:  Please apply same logic to Additional Promotional Benefit Quantity Offer as you see applied in Note 1.

    Please see (Baseline Conceptual Models Commentary) for further details on what conceptual models are to be used for.     

    Baseline Conceptual Models: Product Specification Model

    Product Specification Model:  Products are goods and services that a company provides.   This model tries its best to include cloud based products and services as well as your basic product line.  Its not bullet proof but will get you along way in thinking about how you might model your product line.

    Goods: Goods are physical things in which a customer can lay their hands on like a device.   They can also be a card a person can buy that has a product key on it the activate features on a given product.  They can be a gift card that represents monetary value that can be applied to a subscription or a product purchase.  They can also be a digital asset that the customer can download and store on their device.  Goods are made up of physical parts or digital files.

    Services: Services are more complex.   A service can provide a range of access to products, infrastructure, expert labor, trouble shooting, and education to the customer.  They can represent a product or technology infrastructure as a service such as a cloud service like Azure, Google, Facebook, or Amazon.

    Product Relationship:  A product can relate to other products like being optional accessories to a main product.   They can be products relating as part of the same family of products.  They can be competing products if your a store caring competing products.

    Product Keys: See Product Key Unlock and Activation Model
      
    Digital Benefit Specifications:  Products may have one or more digital benefits that can be assigned.  Each benefit can be predefined and assigned to one or more product. 
    • Metered Benefits are benefits that are metered when used such as processing time or storage space.   
    • Client Licenses are benefits that enable a cloud service to provide seats to a service.  This is different then product keys in that client licenses can be as simple as registering an party as an account member to an account and granting them entitlement to a seat.  
    • Download Benefits are benefits that entitle a customer to download a given set of digital assets such as products, documents, etc...
    • Feature Benefits are benefits that entitle a customer a set of cloud side or client site features.
    Note:  In this model a part can be a product.   But please note that a part can be made of one or more parts in the part bill of material (BOM).   And a product can be bundled with other products in an offer.  Please see Part Bill of Material (BOM) Model  and Product Offer and Pricing Model.

    Please see (Baseline Conceptual Models Commentary) for further details on what conceptual models are to be used for.

    Baseline Conceptual Models Commentary

    I am providing a series of conceptual models on this blog that will present the basic concepts that any business will need to master.   This is a best effort on my part on my personal time and you will probably run across flaws.  It is also a living work in progress and may change from time to time when I have time.  These models are not meant be perfect, but are presented to enable people to quickly take and modify for their own business needs as each business is different.  These conceptual models will only show the entity and their relationships with a suggestion for the primary key.  No attributes are given as they are at a conceptual level and may vary depending on the business.   But in my opinion attributes are easy to add after this stage.

    Model Type Definitions:
    Conceptual Model: A normalized model that is designed to clearly define the business entities that a business understands and their relationships between them.   This is to be at a high level and should not cover low level domain entities.  They do not include attributes, but in this case I'm at least providing the suggested Primary Key.  I believe that understanding the definition of an entity is aided by understanding what makes up the Primary Key.  Conceptual models are targeted for business people as well as engineers.

    Logical Model: A normalized model that takes a conceptual model down further to the low level domain entities as well as formally defining the Primary Keys and assigning attribution for each entity.  Logical models are targeted for engineers mostly as I find business people get overwhelmed.  I will not cover this territory in my models presented on this blog for now as details may vary greatly between companies.

    Physical model A schema that satisfies the logical models specification in physical database.  The schema is configured and tuned specifically to the environment in which it is targeted for.  Physical models are targeted for engineers.  I will not cover this territory in my models presented on this blog as its too specific to technology.

    Entity Box Legend:  Blue boxes are master entities.  Light colored boxes are relationship entities.  Box in box are subtype entities.  There really isn't much meaning other than that.  I felt it useful for me.  You can ignore if you desire.

    Disclaimer:  Take these models and use them at your own risk. 

    Baseline Conceptual Models: Account Member & Profile Model

    Account Member and Profile Model: Cloud Services need to have the ability to have members associated to an account in which they can use the service being provided for an account, but do not own the account itself nor have any financial responsibilities for paying for the service being provided.   This model also enables profiles for each member of the account.   This model depends on the Party and Account Model

    Note 1:  I've enabled the ability to create a profile at the account member level rather then the account level or the party level because people may have different profiles for each team the may be working with.  Some more private then others.  Example:  I could be a member to 3 different Accounts.  One is my employer, another is a very public group I'm volunteering work for, and a private account in which I also own as well as being a member.    Each one of these profiles may be different depending on the focus and exposure.  I hope that clarifies where I'm coming from.

    Note 2:  There could be multiple types of profile documents that could be created for a member.  One could contain more PII information that is used by the service and needs to be tightly secured and managed.   Another profile could be a gaming profile in which is more public and has no PII information. Physically speaking you could end up storing these two types of profiles separately due to security issues.   But I've placed them together because logically they are the same concept.

    Note 3:  Attributes can be defined once and then applied to one or more profile documents to save from having to duplicate and manage multiple attributes that are logically the same concept. 

    Please see (Baseline Conceptual Models Commentary) for further details on what conceptual models are to be used for.

    Baseline Conceptual Models: Contract Model

    Contract Model:  Contracts are the staple for any business.   Service Level Agreements (SLA), End User License Agreement, and Pricing Agreements are just the beginning of the types of contracts people and companies will enter into to do business.   This model provides the understanding of participants of the contract and the beneficiaries of the contract.   These two concepts are important as companies can be owned by a parent company in which signs a contract with you that benefits all the subsidiary companies it owns by may not have participated in signing the contract.   The model depends on the Party & Account Model.   

    Please see (Baseline Conceptual Models Commentary) for further details on what conceptual models are to be used for.

    Baseline Conceptual Models: Location Model

    Location Model: Locations are logical or physical spatial maps that can represent a site, store, warehouse, dock, floor, room, or a bin in a room.   It can also represent a logical concept as digital locations.  From an enterprise modeling perspective I mostly use location for inventory purposes.  This model depends on the Party Model.

    Note: There are some stranger concepts of locations in which represent in between locations in which inventory is on a truck going from place to place.  I call that location as "In Transit".  This location is usually used in inventory control.   Inventory can't be removed from a company inventory when your redistributing the inventory to other locations.  Inventory that is in transit still needs to be accounted for as inventory and be given a location of In Transit.

    Please see (Baseline Conceptual Models Commentary) for further details on what conceptual models are to be used for.

    Baseline Conceptual Models: Party & Account Model

    Party & Account Model:  Accounts are a vehicle to track various account activity, settings, entitlements, sales, subscriptions, promotions, and authorization to products and features.   The model works with the Party Model.  Its important to note that this strength of this model resides in the idea that a Party can have many Accounts.  Example:  Company "A" be a Vendor to you in which they provide services.   They can also be a Customer as well as a Partner to you.  Each role the have with you results in an Account in which track sales, purchases, entitlements, transactions, etc... relating to that role.   Accounts are used heavily by companies and is a critical component in your enterprise model.
        
    Note 1: This model shows a Authentication Provider Account which follows a push for "Claim Based Authentication" technique for cloud based user logins.

    Note 2:  You will notice that the Account Contact Point Entity has a suggested Primary Key of Account ID, Party ID, Contact ID in which Party ID is BOLD.   Party ID is an example of key folding in which the Party ID from Contact Point folds into Party ID from the Account Contact Entity.  I personally like to call this a data harmonic.  Hope that helps you understanding some advanced modeling techniques that are demonstrated in this model.

    Please see (Baseline Conceptual Models Commentary) for further details on what conceptual models are to be used for.

    Baseline Conceptual Models: Party Model

    Party Model: Tracking people, companies, and internal company groups along with their relationship to one another and their contact points is an absolute need for any one.   This model presents the standard concept that you will see in the modelling world called the 'Party Model'.  Its important to have a generalization concept the covers both person and organization called the Party because you will come across many business concepts in which either a person or group can have a relationship with.   Examples are Contracts, Memberships, Services, Products, Sales, and Shipment.    This will enable a relationship to just relate to the Party entity rather than making a relationship for both Person and Organization.  From a conceptual modeling purposes this makes the model easy to read which is the main point of what conceptual models are for.  But generalization concepts may not necessarily be the best design from a physical performance perspective if the generation results in a hot table becoming a bottle next in the system.   The needs of the physical world should prevail.

    Note 1:  In this model Contact Points only can exist if they are associated with a party.   But if you are a postal service the address is important all on its own and can exist without a party.  So you will need to make the necessary modifications.    But that usually is a corner case from my experience.

    Note 2:  This model demonstrates role naming:  Person ID from the Human Resource Entity is really a role name for a Party ID from the Party Entity.  Organization ID from the Human Resource Entity is really a role name for a Party ID from the Party Entity.

    Note 3:  You will notice that the Human Resource Contact Point Entity has a suggested Primary Key of Organization ID, Person ID, Contact ID in which Person ID is BOLD.   Person ID is an example of key folding in which the Party ID from Contact Point folds into Person ID from the Human Resource Entity.  I personally like to call this a data harmonic.  Hope that helps you understanding some advanced modeling techniques that are demonstrated in this model.

    Please see (Baseline Conceptual Models Commentary) for further details on what conceptual models are to be used for.

    Baseline Conceptual Models: Scheduled Event Model

    Scheduled Event Model:   Events, Anniversaries, Holidays, Vacations, Meeting Schedules, Pipeline Execution Schedule, and Appointments are just a few types of events people, companies, or machines need to track.  This conceptual model works with the Calendar Model and Party Model to cover the ability to create an event calendar someone can add to there personal calendar to show the scheduled events for a given subject area, group, or person.  Example:  National Holidays, Company Paid Holidays, Company Wide Events, Personal Vacation Schedule, Birthday List, etc....  In application I can add to my personal calendar the list of national holidays for the U.S., the Company Paid Holidays, Company Events, and the Pipeline Execution Schedule to keep me informed.  

    Please see (Baseline Conceptual Models Commentary) for further details on what conceptual models are to be used for.

    Baseline Conceptual Models: Calendar Model

    Calendar Model:  Calendars are ubiquitous and required by any society.  Usually there are two calendars for a medium-to-large business: Gregorian and Fiscal.  This model should allow a business to create any type of calendar including Gregorian, Fiscal, Chinese, Hindu, Hebrew, or Astrological.   It does dictate that you should always have a year, month, week, and day concepts with an optional quarterly concept. 

    Note 1: The Quarter concept could be based on a 13 week cycle(52/4) for each year or could be based on 3 month cycle (12/4) for each year.

    Note 2: The Reserved Name Space is pretty straight forward as it represents for any type of calendar the names for each month and the names for each day of the week.  But the year name space may sound weird until you think of Chinese or astrological calendars in which the year name of a name of a constellation.  Example:  Year of the Dragon.       

    Please see (Baseline Conceptual Models Commentary) for further details on what conceptual models are to be used for.