Please enable JavaScript.

Coggle requires JavaScript to display documents.

Advanced Project Design (Using MSTR multisource (MSTR Multiple Source…

- - - - combine data from multiple data sources:
        
        create a Report Services document and include reports in the same project that connect to different data sources as its datasets.
      - You cannot access two distinct data sources within a single standard or non-standard report unless you use MultiSource Option.
  - - - lowest level attribute
    - - higher level of aggregation
      - created in the data warehouse, and has to be updated, by the ETL team, in the ETL process.
      - two actions that you need to perform to integrate an aggregate fact table into an existing project
        
        Add the table to the project using the Warehouse Catalog.
        
        If necessary, map the existing attributes and facts to the aggregate table.
    - - table editor
      - logical size editor
        
        lock the size: so the size does't get updated when update the schema
  - - - Creating aggregate fact tables
      - Creating tables for very large result sets and then using other applications such as MS Excel or MS Access to access the data;
      - Creating tables for off-line analysis.
    - - data mart report
        
        new or existing report
      - data mart table
        
        to create, execute the data mart report
- - - - e.g. the LU_REGION table
      - This is called "Duplicate Tables".
        
        You create a duplicate table when you map the same project table to more than one database instance.
        
        you can have any type of table (lookup, relationship, or fact) duplicated across multiple data sources.
        
        Duplicate tables are beneficial only for lookup and relationship tables.
        
        Duplicate tables must have identical table name, column names, and same/compatible data types for corresponding columns.
        
        The table associated to the primary database instance cannot have extra columns.
        
        You can have extra columns in the tables associated to secondary database instance.
  - - - determines the optimal database instance for each pass of SQL
      - Identifies when joins need to occur across database instances.
  - - - The Engine moves the attribute data to the fact source.
  - - - Uses Engine data type definition, not physical database definition.
- - - - Fact Degradation
  - - - Enables you to extend a fact's level to a lower level within the same hierarchy. This concept is the opposite of aggregation.
      - Creating a Fact Degration
        
        Choose the attribute level to which you want to lower the fact.
        
        Select an attribute the SQL Engine can use to join the fact table with the attribute to which you want to lower the fact.
        
        Determine how to perform the join.
        
        2(1)only to the attribute you choose (2)also join to the children of the attribute you choose.
        
        If necessary, define an allocation expression (how to do the mapping).
    - - Enables you to extend a fact's level to include a level from a different hierarchy not currently related to the fact.
      - three ways to create extensions
        
        Through a table relation
        
        You specify a particular table to use for the join. You should select this option if you always want the Engine to use the same table for the join.
        
        Through a fact relation
        
        Instead of a table, you specify a fact. This option enables the Engine to choose any table containing that fact for the join. You should select this option if you want to allow the Engine choose the optimal table for a particular query.
        
        Through a cross product
        
        You tell the Engine to perform a Cartesian join between the lookup and fact tables in question.
        
        this is not recommended.
        
        you can do this if there is no other option.But this causes a large overhead and may not give you the result you want.
      - Extension through (a Table Relation) or (a Fact Relation)
        
        Choose the attribute level to which you want to extend the fact.
        
        Choose the table you want the SQL Engine to use to perform the join.
        
        Select the attribute of set of attributes the SQL Engine can use for the join.
        
        Determine how to perform the join.
        
        If necessary, define an allocation expression.
    - - Enables you to prevent unnecessary cross joins to lookup tables when reporting on fact.
      - Doesn't resolve a problem, but it prevents the SQL Engine from spending a lot of energy trying to run a report which doesn't bring back appropriate data.
- - - - can be used for very complex tasks
      - You implement these transformations using a transformation table in the warehouse.
  - - - The member attributes are attributes to which the transformation applies, which is the lowest-level attribute on the report to which you want to apply the transformation.
    - - Each member attribute has a corresponding expression that needs to be resolved in the report SQL to obtain valid data.
        
        在table-based的情况下，expression就是column name
    - - The member tables store the data for the member attributes.
    - - The mapping type determines the way the transformation is created based on the nature of data.
        
        one-to-one mapping type
        
        many-to-many mapping type
        
        one day corresponding to many days.
- - - - Physical tables into logical partitions, not actual separate tables.
      - the DB software handles this completely. Just write SQL in MSTR
    - - This goes in the DB server itself.
      - Warehouse and Metadata Partition Mapping
        
        MicroStrategy supports application-level partitioning for fact tables through one of the two methods.
        
        Warehouse partitioning mapping
        
        This method is based on a physical warehouse partition mapping table(PMT) that resides in the data warehouse and describes the partitioned base tables that are part of a conceptual whole.
        
        将partition base tables放在data warehouse里而不是分割前的table
        
        The partition mapping schema object is generated by MSTR automatically, when the Partition Mapping Table in the data warehouse is included into the project.
        
        对比：只添加Partition Mapping Table到project中，通过pre-query确定应该run SQL against data warehouse的哪一张表
        
        Metadata partition mapping
        
        This method is based on rules logically defined in MicroStrategy Architect and stored in the metadata.
        
        对比：将所有Partition Base Table都添加到data warehouse中，不需要在data warehouse中定义PMT，但是在添加这些表到MSTR project中之后，需要在MSTR中定义PMT
        
        在这里定义PMT的时候实际上是对每个PBT，选取了attribute element的一部分
- - - - 2.fact
      - 3.table
      - 1.attribute
      - 4.hierarchy
    - - 5.transformations
        
        e.g. Last Year's
      - 6.partition mapping
        
        divide large table into smaller tables in the data warehouse
        
        the reason is that sometimes the table is too large to query
      - 7.functions and operators