Transcript Metadata Modeling Mentoring

Metropolitan State College
Of Denver
Introduction to Metadata
Modeling Mentoring
04/14/2011
Topics
•
•
•
•
•
•
•
•
•
•
Ground Rules
Cognos Connection (the Studios)
Metadata Models
Process Flow
Importing Metadata
Preparing Metadata
Create/Publish Packages (of Metadata)
SQL Types & Minimized SQL
Shortcuts
Reporting Traps
Ground Rules
•
•
•
•
•
Ask Questions!
Weds: 9 – 4 (lunch: 12-1)
Ask Questions!
Help Shape the Training
Introductions
Cognos Connection
•
http://147.153.36.29/cognos10/
•
Cognos portal to all managed content (reports, analysis, queries, etc.)
•
Run/View reports
•
Create new queries
•
Create new reports
•
Create new analysis’
Metadata Models
•
•
•
•
Metadata is “data about data”
Ensures predictable results
Prevents runaway queries
Provides logical view of underlying data
structures
• Operational vs. Reporting (star schema)
– Operational: Normalized, optimized for writing
– Reporting: Denormalized, optimized for reading
The SGHE Model
• One file split into a
– Data View
– Business View
• Holds ALL reporting areas
• If Customizing, create separate namespaces, for
upgrade path.
• Let’s take a look
Metadata Models
Operational
Reporting
Process Flow
Process Flow
• Framework Manager
– Create Project
•
•
•
File – New
Create Database View namespace
Create Business View namespace
– Import Metadata
•
•
Right-click Business View namespace – Run Metadata Wizard
Select your objects
– Prepare Metadata
•
•
Create your joins
Build your Business View
– Create Packages
•
Right-click Packages – Create – Package (name Training_<username>)
– Publish Packages
•
Right-click Training_<username> in Packages – Publish Package
• Query Studio
Import Metadata
Metadata Wizard
Pay attention as your instructor demonstrates this part of the
process.
We will be importing the following tables from ODS:
• TELEPHONE_CURRENT
• PERSON_DETAIL
• INTERNET_ADDRESS_CURRENT
Try and do this on your own.
Preparing Metadata
Creating Joins
Pay attention as your instructor demonstrates this part
of the process.
We will be creating the joins within the imported tables:
Person Detail.PERSON_UID = Telephone Current.ENTITY_UID
Person Detail.PERSON_UID = Internet Address Current.ENTITY_UID
Try and do this on your own.
Preparing Metadata
Establishing the Business View
Pay attention as your instructor demonstrates this part of the
process.
We will be creating custom query subjects with logical names for
the imported tables.
Try and do this on your own.
Preparing Metadata
Create a Calculation
Let’s discuss the various calculations that are common within the
tables we have imported.
Pay attention as your instructor demonstrates the creation of this
calculation.
Try and do this on your own.
Create/Publish Packages
• What are packages?
• Why do we need packages?
Pay attention as your instructor demonstrates the process of
creating and publishing a package.
Try and do this on your own. Please make sure you name your
package based on the convention: Training_<username>.
SQL Types
•
Cognos SQL
•
Native SQL
•
Pass-Through SQL
Pros
– improves query subject performance by removing unused elements at run time.
– works with any supported database
Cons
– non-standard SQL not supported
Pros
– performance is optimized across all related query subjects
– You can use SQL that is specific to your database
Cons
– only SQL that your database supports will work
– may not be portable to another database
Pros
– you can use any SQL supported by the database
Cons
– FM cannot optimize
– may not be portable to another database
Shortcuts
• Two types of shortcuts:
– Regular
• a simple reference to the original object
– Alias
• like a copy of the original object, with it’s own properties
and behavior
• often used to resolve circular joins
Reporting Traps
• Transitive Relationships (Circular Relationship)
– Cognos query engine uncertain of correct path
– Resolve by creating shortcuts
Reporting Traps
• Connection Traps
– Critical join potentially missing
– Resolve by creating shortcuts and establishing all joins
Reporting Traps
• Ambiguous Joins
– Typical with fact tables with multiple date fields
– Resolve by creating shortcuts and naming appropriately