Transcript 1. - dbmanagement.info

Impromptu Administrator
Goal :
Develop an Impromptu application that is easy to
use and simplifies reporting tasks, shielding your users
from the complexities of the database.
Role :
Providing a bridge between those who know the
data and those who know the business.
1
COGNOS - Impromptu Administrator
COGNOS – Impromptu Administrator
Planning.
The Database and Catalog.
Working with Joins.
Working with Folders.
User Profiles.
Performance.
User-Defined Functions.
Wrap Up
Click on the Links to travel
Planning
Planning
1.
Focus on your End Users.
2.
Develop Clear Requirements.
3.
Develop a Logical Design.
4.
Follow a Project Life Cycle.
5.
Start with a Pilot Project.
6.
Build Flexibility in Your System.
7.
Plan Infra Structure.
8.
Plan Effective Training Strategy.
9.
Plan Good Support Strategy.
4
COGNOS - Impromptu Administrator
The Planning Process
1
Plan
4
Maintain
2 Implement
3
5
COGNOS - Impromptu Administrator
Deploy
The Impromptu Project Life Cycle: Designing
Identify Project Participants
Determine Catalog and
Folder Structure
Provide Training to
Project Participants
Develop Join Strategy
for Tables
Identify Database
Tables and Columns
Identify Your Users and
Their Data Requirements
Identify Data Sources
6
COGNOS - Impromptu Administrator
The Impromptu Project Life Cycle: Creating
Deploy and Maintain the
Impromptu Application
Create the Catalog and
Establish Joins
Modify Folder Structur
and Add Calculations
Conditions, and Promp
Provide End Users with
Impromptu Training
and Support
Define and Create User
Profiles and User
Classes
Improve Performance of
Impromptu Application
Create and Distribute
Standard Reports and
Templates
7
COGNOS - Impromptu Administrator
Staging the Data
If you have more than one database, you can:
- merge separate databases into one central source
- create separate catalogs for independent systems.
PC Database
direct connection
or ODBC gateway
Impromptu
Data Mart /
Warehouse
8
COGNOS - Impromptu Administrator
Connecting to the Database
Obtain the parameters needed for Impromptu to
connect to the database.
• for example, gateway type, path, security
information, user id, password, network type.
Database
Connect to the database
9
COGNOS - Impromptu Administrator
Identify Database Tables and Columns
•For performance and security, identify the columns in
required tables which users will not need to access.
CUSTOMER table
X
CREDIT RATING
10
COGNOS - Impromptu Administrator
Identify the Need for Calculations
• Defining common calculations will aid your users.
+ -
Calculations / *
Product Margin = Product Price - Product Cost
Product % Margin = Product Price - Product Cost/Product Price
11
COGNOS - Impromptu Administrator
Identify Your Table Joins
This is one of the most important Administrator
activities.
Poorly designed joins can cause inaccurate report
results.
These tables directly relate to the Core
Core table
table.
ORDERS
ORDRDETL
ORDER_NO
REP_NO
SITE_NO
CUST_NO
ORDER_DT
CLOSED_DT
STATUS
COMMENTS
PRODUCT
ORDER_NO
LINE_NO
PROD_NO
PRICE
QTY
DISC_PRCNT
RETURNED
PROD_NO
PROD_TYPE
PROD_LINE
PRODUCT
PROD_COST
PROD_PRICE
STATUS
PICTURE
SALES_94
SALES_95
COMMENTS
REP
REP_NO
REP_NAME
QUOTA_94
QUOTA_95
SALES_94
SALES_95
12
COGNOS - Impromptu Administrator
Design the Folders
•This is the single most important step in the
Impromptu application development process.
•Know your users’ business requirements for data
before you begin.
•Decide which data items need to be placed together.
•Determine the structure and naming of the folders
and data items.
•Always create a Default Folder named Admin having
default Folder structures and avoid its access to
Users.
13
COGNOS - Impromptu Administrator
The Database and Catalog
A Catalog Contains...
Columns
Folders
Eg: Product
Details.
15
Eg: Product Name
Product
Number
COGNOS - Impromptu Administrator
A Catalog Contains...
+ /
Calculations *
Eg : Profit
Conditions
Eg:Sale Closed on
time
Margin
16
COGNOS - Impromptu Administrator
Prompts
?
Eg: Country.
Sales Rep
Name.
The Data and the Catalog
Grouped the way the users want to use the data
and not the way it is structured in the database.
THE DATABASE
Tables and Columns
THE CATALOG
Folders and Columns
Products
Product No.
Product Type
Product Line
Product
Product Status
Product Picture
Product Note
Price and Cost
Sales History
PRODUCT
PROD_NO
PROD_TYPE
PROD_LINE
PRODUCT
PROD_COST
PROD_PRICE
STATUS
PICTURE
SALES_92
SALES_93
SALES_94
SALES_95
COMMENTS
17
COGNOS - Impromptu Administrator
The Catalog
Corporate
Data
Inventor
y
Quality
•As an Administrator, you should
organize the folders in your
catalog in the way your users
view the business.
18
COGNOS - Impromptu Administrator
Accounts
Receivable
Creating a Catalog: An Overview
Steps to creating a catalog:
1. Create your database connection.
2. Define the catalog.
3. Identify required database tables and
columns.
4. Develop a join strategy for all tables.
5. Determine the folder structure.
19
COGNOS - Impromptu Administrator
Connecting to the Database
• The database definition determines how
Impromptu will connect to the database.
Database
20
COGNOS - Impromptu Administrator
Creating a New Catalog
Creating a new catalog requires:
–
–
–
–
–
a catalog name
a description (optional)
a catalog type
the database definition
the required tables.
Database
21
COGNOS - Impromptu Administrator
Limiting the Tables Retrieved
–You can limit
the tables
retrieved from a
database.
–You can
customize a
search to
include specific
information from
a database.
22
COGNOS - Impromptu Administrator
The Default Folders
–Impromptu automatically creates a folder for
each table in the database.
–In this case Table Names becomes the default
Folder names and the able attributes becomes the
column names
23
COGNOS - Impromptu Administrator
The Content Overview Report
Impromptu can generate a report of your catalog's contents:
Impromptu Version 5.0.119.0
Catalog Content Report
Catalog Information
Catalog Name: C:\EDCOGNOS\IM5002EB\TRAINING.CAT
Description: Training Centers, world wide
Creation Date: Wednesday, March 11, 1998
Database Information
Logical Name:
Physical Name:
Type:
TrainDb
C:\EDCOGNOS\IM5002EB\DATABASE
TrainDb@ANSI
DB
Database Structure
Table : Course
Column : COURSE_ID
Column : CAT_ID
Column : COURSE_CD
24
COGNOS - Impromptu Administrator
Content Overview Report: Benefits
A Content Overview Report can be used for:
•hard copy backup
•verify contents of catalog
•catalog and reports can be reviewed, constructed
and manipulated in a programmatic manner
•log of changes
•analytical tool.
25
COGNOS - Impromptu Administrator
Working with Joins
What is a Join?
A join:
•identifies the column(s) linking two tables
•allows reporting on more than one table at a time.
Order
Table
Rep
Table
Rep No
Rep
Name
Join
Order No
Rep No
Site No
Quota 96
Sales 96
27
Cust No
Order Dt
COGNOS - Impromptu Administrator
Why Do We Need Joins?
Joins allow the report designer to:
•create powerful reports, using columns from different
tables
•eliminate cross-product queries
•optimize performance.
Joins allow the Impromptu Administrator to:
•hide the manner in which two tables are related, so
the report writers don't need to know these details.
28
COGNOS - Impromptu Administrator
Creating Joins in Impromptu
•When you create a catalog with more than one
table, Impromptu prompts you for join information.
•There are two ways Impromptu creates table joins:
– automatically, for all tables at once
– manually, one at a time — the default.
•You determine the strategy in the Tables dialog. The
specifics of the join are handled in the Joins dialog.
29
COGNOS - Impromptu Administrator
Join Strategy - Automatic
Automatic:
Here, Impromptu will create
•assign meaningful keys a compound join based on
•keys should be unique. Site_No and Cust_No as
primary keys in CustSite
and like-named columns in
Order.
30
Order
CustSite
Order_No
Cust_No
Rep_No
Site_No
Site_No
Branch_Cd
Cust_No
Address 1
Order_Dt
Address 2
Closed_Dt
City
COGNOS - Impromptu Administrator
Automatic Joins and Keys
•An Impromptu key identifies one or more columns
which can be used for table joins.
•They are generated at the time the catalog is created,
based on the primary keys in your database tables.
•They can also be manually added to the catalog.
CustSite
Order
Cust_No
Order_No
Site_No
Rep_No
Branch_Cd
Site_No
Address 1
Cust_No
Address 2
Order_Dt
City
Closed_Dt
31
COGNOS - Impromptu Administrator
Join Strategy - Manual
Manual:
Country
Branch
•organize your data
into logical groups
•plan for “core”
tables
•join table groupings
to the “core”
•determine the type
of join that will
ORDRDETL
bring the best
reporting results.
CUSTSITE
Customer
Order
Product
32
COGNOS - Impromptu Administrator
Rep
Join Strategy - Manual
When manually joining the tables, one by one, you
have three choices:
• explicitly name the columns In both tables to join
– Use this when the column names differ In the two
tables.
• let Impromptu generate a join based on keys
– If one table has a key, Impromptu looks for a
column of the same name (key or not) in the other
table.
– Use this when all tables have keys.
• let Impromptu generate a join based on keys or
matching column names
– Use this last, and beware 'false' joins.
33
COGNOS - Impromptu Administrator
Types of Joins
–The join operation joins tables on the basis of
column values in a common column.
–The different types of joins are:
–equi-joins
–non-equi-joins
JOINS
–outer joins
–self-joins
–compound joins
–complex joins.
34
COGNOS - Impromptu Administrator
Equi-Joins (Inner Join)
• Equi-joins retrieve all the rows from one table that have
matching rows in another table, based on equal values.
• In this example, for each order, there are order details.
Order No
1
1
2
2
2
2
2
2
3
3
...
Rep No
1
1
27
27
27
27
27
27
27
27
...
35
Site No
1
1
1
1
1
1
1
1
1
1
...
Cust No
1085
1085
1148
1148
1148
1148
1148
1148
1030
1030
...
...
...
...
...
...
...
...
...
...
...
...
...
Order No
1
1
2
2
2
2
2
2
3
3
...
COGNOS - Impromptu Administrator
Line No
1
2
1
2
3
4
5
6
1
2
...
Prod No
60401
60400
60101
60400
50101
40101
40100
40302
60400
60301
...
...
...
...
...
...
...
...
...
...
...
...
...
Non-Equi-Joins
• Non-equi-joins connect rows from two tables based on
values which are not equal.
Grades
Scores
Grade
A
B
C
D
F
Min Score Max Score
90
100
80
89
70
79
50
69
0
49
<=
Student
Jean
Sue
Bob
Ton
Luis
>=
Join Definition:
Grades.Min Score <= Scores.Score
AND
Grades.Max Score >= Scores.Score
36
Score
83
76
92
67
74
COGNOS - Impromptu Administrator
Student
Jean
Sue
Bob
Ton
Luis
Grade
B
C
A
D
C
Outer Joins
•Outer joins retrieve rows from one table even if
there are no matching rows in the connecting table.
•In this example, you can show all orders by Rep
and include even those reps who have no orders.
Rep No
15
15
16
16
16
16
17
18
18
18
18
18
Rep Name
Chris Cornel
Chris Cornel
Bill Gibbons
Bill Gibbons
Bill Gibbons
Bill Gibbons
Russ Pallomine
Lyn Jacobs
Lyn Jacobs
Lyn Jacobs
Lyn Jacobs
Lyn Jacobs
37
...
...
...
...
...
...
...
...
...
...
...
...
...
Rep No
15
15
16
16
16
16
Order No
17
114
167
193
258
259
18
18
18
18
18
8
29
35
101
189
COGNOS - Impromptu Administrator
...
...
...
...
...
...
...
...
...
...
...
...
...
Self-Join
–Self-joins allow you to add two copies of the
same table in a report so that you can relate
values within a single table.
Employee Table 1
EMP-NO
01
02
03
04
LAST-NAME
Davis
Chung
Reva
Larouche
FIRST-NAME MGR-NO POSITION
Vincent
38 Sales Rep
Sam
04 Clerk
Table 2
Santos
38 SalesEmployee
Rep
Nicole
15 Manager
EMP-NO LAST-NAME
FIRST-NAME
01 Davis
Vincent
02 Chung
Sam
03 Reva
Santos
04 Larouche
Nicole
MGR-NO
38
04
38
15
Report
EMP-NO
01
02
03
04
38
LAST-NAME
Davis
Chung
Reva
Larouche
FIRST-NAME
Vincent
Sam
Santos
Nicole
MGR-NO
38
04
38
15
COGNOS - Impromptu Administrator
LAST-NAME
...
Larouche
...
POSITION
Sales Rep
Clerk
Sales Rep
Manager
Compound Joins
•Compound joins are those where more than one
column matches to another column in another table.
•In this example, Order_Prefix + Order_Suffix in the
first table matches to Order_No in the second table.
Order_Prefix
CA
Order_Suffix ... Order_ No
123456
39
CA123456
COGNOS - Impromptu Administrator
...
...
Complex Joins
Complex joins use an expression to join tables.
•For example, you may need to join two tables using
the first two characters of two columns. The
expression could look like this:
substring (Warehouse, 1, 2) = substring (Location,
1, 2)
Warehouse
...
TX7902
40
...
Location ...
TX3
...
...
COGNOS - Impromptu Administrator
Alias Table
A manually-created duplicate table that can be
used to:
•resolve loop join structures
•create self-joins
•provide alternate join structures.
Database tables
Catalog tables
BRANCH
COUNTRY
PRODUCT
REP
BRANCH
BRANCH 2
COUNTRY
PRODUCT
REP
41
COGNOS - Impromptu Administrator
Alias table
Analyze and Test the Join Structure
– Once the joins have been created, it is a good
idea to analyze the joins for each table.
– Problems in the table joins may give unexpected
results in reports.
– Three potential problems that Impromptu will
identify:
•No Spanning Tree
•Isolated Table
•Loop.
– You can solve the problem by:
•creating an alias table
•editing a join
•removing a join.
42
COGNOS - Impromptu Administrator
Exceptions: No Spanning Tree
Exception: NO
SPANNING TREE
Country
Branch
– there is no
single spanning
tree for all
tables
Cust_Site
Customer
Order_Detl
Order
The two groups of
tables are not joined.
This is a No Spanning
Tree exception.
Product
43
COGNOS - Impromptu Administrator
Rep
To Resolve No Single Spanning Tree
To solve No Single
Spanning Tree, do
one of the
following:
• add a join to
create a
spanning tree
• leave the join
structure as
it is.
Country
Branch
Cust_Site
Add this join
Customer
Order_Detl
Order
Product
44
COGNOS - Impromptu Administrator
Rep
Exceptions: An Isolated Table
Exception:
ISOLATED TABLE
Country
Branch
• there are no
paths defined for
this table
Cust_Site
Customer
The table
Branch is not
joined to the
other tables.
Branch is
therefore an
isolated table.
Order_Detl
Order
Product
45
COGNOS - Impromptu Administrator
Rep
To Resolve an Isolated Table Join
To solve an
Isolated Table
Join issue, do
one of the
following:
• join the
isolated
table(s)
with the
rest of the
join
structure
• leave the
join
structure as
it is.
46
Country
Branch
Cust_Site
Customer
Order_Detl
Order
Product
COGNOS - Impromptu Administrator
Rep
Exceptions: A Loop
Exception: LOOP
there is a loop in the
join structure.
In this example, an automatic
join strategy was done on all
tables at once.
This resulted in an incorrect
join between Customer and
CustOrder (bypassing
CustSite).
Customer
Cust-No
CustSite
Cust-No, Site-No
CustOrder
Now there is more than one
way to go from one table to
another.
This may decrease
performance or give incorrect
results.
Cust-No, Site-No
47
COGNOS - Impromptu Administrator
To Resolve Loop Joins
To solve loop join problems, do one of the
following:
•remove a join, forcing a connection through an
intermediate table
•create an alias table with a separate join
•leave the join structure as it is
•remove a table from the catalog.
48
COGNOS - Impromptu Administrator
Testing Joins
The Test tab allows you to:
•test join strategies prior to using them
•ensure optimal join strategies when a report requires
multi-table joins.
TEST
CustSite
Order1
Product
Cust_No
Order_No
Prod_No
Site_No
Rep_No
Prod_Type
Branch_Cd
Site_No
Prod_Line
Address 1
Cust_No
Product
Address 2
Order_Dt
Prod_Cost
City
Closed_Dt
Prod_Price
(CUSTSITE.”CUST_NO” =
ORDER1.”CUST_NO”
and
CUSTSITE.”SITE_NO” =
ORDER1.”SITE_NO”)
and
(ORDER1.”ORDERNUM” =
ORDRDETL.”ORDER_NO”)
and
(ORDRDETL.”PROD_NO” =
PRODUCT.PROD_NO”)
CUSTSITE
ORDER1
ORDRDETL
49
COGNOS - Impromptu Administrator
ORDER1
ORDRDETL
PRODUCT
Working with Folders
Working with Folders
You can:
• change the names of folders to make more sense
to users
• move folders and columns so they are grouped
more logically
• add new folders that contain a subset of table
columns, or columns from multiple tables
• remove unnecessary folders and columns
• create copies of tables and columns within different
folders
• add pre-defined calculations, conditions, and
prompts to the catalog.
51
COGNOS - Impromptu Administrator
Design the Folder Structure
• Know your users’ business requirements for data
before you begin.
• Decide which columns need to be placed together.
• Determine the structure and naming of the folders
and columns.
-
Customer
Cust No
Customer
Status
Type
Comments
52
Rename
Customers
Customer Number
Customer Name
Move
Remove
COGNOS - Impromptu Administrator
-
Customer Details
Status
Type
Tips for Folder Design
• Use a dictionary or a collection of business terms
from the user community to determine clear
column names.
• Use existing reports to determine which columns
are regularly used together.
• Consider the needs of your user community when
deciding how to organize your folder structure.
• Confirm your design with your Business Analyst
before you create the catalog.
• Rename folders and their columns as early as
possible. That way, when you copy columns to
different places, you don’t need to change their
names as well.
53
COGNOS - Impromptu Administrator
Additional Tips for Folder Design
• Keep folder hierarchies (folders within folders)
simple, with the most frequently accessed
information near the top.
• Put columns that are commonly reported together
into the same folder. Put multiple copies in
different folders if they are used with other reports.
• Consider where to store predefined calculations,
conditions, and prompts.
• Create a special Administrator’s folder that contains
the default folder structure, and deny access to it
for other users.
54
COGNOS - Impromptu Administrator
Keeping Track of Folder Columns
• Since you can rename a column and place
columns from
several tables into a
folder, you may forget where a given column
came from.
• To find its original table and column name,
check its qualified name.
55
COGNOS - Impromptu Administrator
Generating New Folders
• You can generate a folder based on a report or
on a database table.
56
COGNOS - Impromptu Administrator
Calculations, Conditions, and Prompts
• Calculations are expressions that perform
operations on existing columns to create
new columns.
• Conditions are filters that let you refine or
focus users’ access to data. Conditions
evaluate to a true/false value.
• Prompts are runtime requests to the user
for data values. These values are then
used as filters.
57
COGNOS - Impromptu Administrator
Why Store Them in the Catalog?
Store predefined calculations, conditions, and
prompts in the catalog to:
• save time so that users don’t have to re-create
them for every report
• ensure consistency throughout the business in the
way calculations and conditions are created and
used
• easily modify them, automatically passing the
changes to the reports that use them.
58
COGNOS - Impromptu Administrator
User profiles
Mapping User Groups to User Classes
–Group users according to the information they
need and the activities they perform.
Marketing
Executive
Sales
60
Finance
& Admin
COGNOS - Impromptu Administrator
User Classes are Inheritance-Based
• A user class inherits the privileges and the restrictions of
its parent class.
Creator
Sales
Marketing
Sales
Mgmt North
America
Finance
& Admin.
Sales
Mgmt Europe
61
COGNOS - Impromptu Administrator
Executive
Top-Down User Class Management
Create a top-level class like 'All Users'.
•Global changes to all user classes can now be done in
one place (without affecting Creator privileges).
Creator
All Users
Marketing
Sales
62
Finance
& Admin
COGNOS - Impromptu Administrator
Executive
User Classes and Access Control
There are many ways that user classes can help
you control access to data in the database. You
can:
•
•
•
•
limit table access
limit folder access and restrict select values
filter values
set Governor values.
63
COGNOS - Impromptu Administrator
The Creator User Class
Creator
The Creator user class has special capabilities that
other user classes do not. For example:
• can set its own password, and set or change
passwords for all other user classes
• can override its own Governor restrictions for
specific reports
• can access Distributed catalogs directly (the master
copy of the catalog).
People running the User version of Impromptu
cannot select the user class Creator.
64
COGNOS - Impromptu Administrator
Creating User Classes
To create a user class:
•From the Catalog menu, select User Profiles.
•In the User Profiles dialog box, click the User Classes
tab.
65
COGNOS - Impromptu Administrator
Assigning Passwords to User Classes
•Passwords are optional, but recommended in a
production environment.
•Only Creator, and those user classes that can
“Add/modify user classes”, can assign passwords. If
a user class password is lost, they can assign new
ones. Passwords are assigned when the user class is
created.
•To change or remove a password, the user class
definition is edited.
Remember:
If you assign a password to Creator and then lose it,
you must re-create the catalog from scratch!
66
COGNOS - Impromptu Administrator
Defining User Profiles
• the User Profiles dialog box has the following
tabs:
67
COGNOS - Impromptu Administrator
User Profiles: Database
• to set database access parameters.
68
COGNOS - Impromptu Administrator
User Profiles: Table Access

to grant or deny access to specific tables or columns.
69
COGNOS - Impromptu Administrator
User Profiles: Folder Access

to grant or deny access to specific folders or columns.
70
COGNOS - Impromptu Administrator
User Profiles: Filters

to set up conditions that will filter out unnecessary or
sensitive data.
71
COGNOS - Impromptu Administrator
User Profiles: Governors

to apply privileges and restrictions for a user class.
72
COGNOS - Impromptu Administrator
Governors: User Privileges
You typically modify these options when you create user
classes
73
COGNOS - Impromptu Administrator
Performance
Performance
•Sort
•Group
•Filter
•Aggregate
Manipulation
Data Access
75
Display
COGNOS - Impromptu Administrator
Catalog Optimization
The catalog can be optimized using the following options:
– Database interaction
– Client/Server balancing
– Database functions
– Table weighting and qualification
– Governors
– HotFiles.
76
COGNOS - Impromptu Administrator
Database Interaction
Efficiency
Impromptu
SQL Translation
Network Transport
Database
77
COGNOS - Impromptu Administrator
Client/Server Balancing
Query
Impromptu processing
Merge
Report
Database processing
 By restricting use of some or all of the local Impromptu
processing, you can balance the work between the client (your
PC) and the server (the machine with the database).
78
COGNOS - Impromptu Administrator
Database Only
With this setting:
– processing occurs on the database server only
– only database functions are available
– large queries can be executed more efficiently.
QUERY
Report
79
COGNOS - Impromptu Administrator
Limited Local Processing - The Default
With this setting:
•the majority of the processing is done on the
database server by creating multiple queries;
Impromptu then combines the results on the PC
•local sorting is disallowed
•summaries in headings are often disallowed
•database and local functions are available.
Database
PC
80
COGNOS - Impromptu Administrator
Flexible Processing
With this setting:
•Impromptu determines where processing takes place
•both built-in and database functions are available
•there is full querying capability
•the entire result set can be sent to the PC, where local
sorts can be performed.
QUERY
Report
81
COGNOS - Impromptu Administrator
Table Weights
Relative weight specifies the order in which tables
are retrieved and joined.
• Tables with lower numbers are retrieved first.
• When filtering on a table, that table should be read
first for best performance.
• Default is equal weight (5).
Customers
1
82
Orders
Reps
5
5
Branches
Countries
Custsites
10
10
10
COGNOS - Impromptu Administrator
Governors
Governor settings are used to set restrictions on
queries by user class, such as:
•
•
•
•
sorting & indexing
outer joins
suppress duplicates
cross-product queries.
Reporting
Needs
83
COGNOS - Impromptu Administrator
Performance
Issues
Governors …
Governors can also limit:
•retrieval of large text items
to <n> characters
•the number of records
•the number of tables
•query execution time.
Reporting
Needs
84
COGNOS - Impromptu Administrator
Performance
Issues
Performance at the Report Level
1
2
3
85
COGNOS - Impromptu Administrator
Reports: Data Source and Retrieval Options
Performance can be improved by accessing a local
data source (Local snapshot, Thumbnail, or
Cache), or limiting the amount of data read.
• 'Retrieve only the required rows' retrieves only the
data needed for the current screen display.
• A Thumbnail is a local cache for a specific number
of rows. This is the same as using both 'Cache
query results' and 'Limit data to'.
86
COGNOS - Impromptu Administrator
Improving Data Access Time
Performance can be
improved by accessing
a local data source
(Local snapshot,
Thumbnail, or Cache),
or by limiting the
amount of data read.
87
COGNOS - Impromptu Administrator
Alternative Data Sources.
Local Snapshots.
1. Is a saved report (.IMR file) which also contains
all the data queried at the time the Snapshot was
taken.
2. Is a static data source does not require access
to the database, or catalog, once it is created
3. Is suitable for working disconnected from the
n/w.
88
COGNOS - Impromptu Administrator
Alternative Data Sources...
Thumb nail :
1.
A Thumbnail is a temporary cache on the PC.
2. With the Thumbnail, you only retrieve the remote
data once, and then you work locally with your cache.
3.
When you exit Impromptu, the cache is deleted
89
COGNOS - Impromptu Administrator
Alternative Data Sources...
HotFile :
1. Is a flat sequential file (not indexed).
2. Can be added to the catalog, or used in
a report as if it were a database table.
HotFiles enable you to:
1.
2.
3.
4.
database.
Compare historical data
Access subsets of large tables
Speed up processing
Access data from more than one
90
COGNOS - Impromptu Administrator
Query Options for Performance
• You may define additional options for handling
the query.
• avoid unnecessary
retrievals
• prevent outer joins
• limit retrieval of large
text strings.
91
COGNOS - Impromptu Administrator
Client/Server Balancing
Client (Your PC)
Server (database)
• Recall that whenever your data is on a remote server,
you improve performance by shifting the workload to the
server (database) side.
• Avoid activities which must be done locally, after the
data has been returned from the server.
92
COGNOS - Impromptu Administrator
Balancing: Summaries
–SQL summary functions in the database are
best:SUM, AVG, MIN, MAX, COUNT
–Impromptu can also do summary calculations,
after the data has been sent from the database to
the client. These non-SQL summary functions
include:
• running summaries:
– you report details, and then add summaries in a
footer.
• extended summaries
– you have a summary in the report heading, or as a
column beside the details.
93
COGNOS - Impromptu Administrator
External Influences
–Evaluate whether the
following may be reducing
performance:
•network issues?
•non-indexed tables?
•slow on another query tool?
•hardware/software limits
reached?
94
COGNOS - Impromptu Administrator
User Defined Functions
What is a User-Defined Function?
– A User-Defined Function is a
custom function created
outside of Impromptu.
Build UDFs
•These UDFs exist in either a
database or a library file.
– As an Impromptu
Administrator, your job is to
make the UDFs known to
Impromptu, so that the
people creating reports can
access them.
– If you have the programming
knowledge, you can also
create your own UDFs.
96
Store UDFs in a
database or library
COGNOS - Impromptu Administrator
Things You Should Know About Functions
Database functions
•take place in the database.
Built-in functions
•may be done on your PC or in the database.
Database Only
PC or Database
PC Only
97
COGNOS - Impromptu Administrator
Categories of Functions
built into Impromptu
in the database
in Impromptu and
the database
• Database functions tend to offer the highest
performance.
98
COGNOS - Impromptu Administrator
Combining Functions
•Functions can be combined so
that the output value of one
function acts as the input to
another function.
•Sometimes it is necessary to
convert one data type to another
when combining functions.
•Impromptu supplies many
conversion functions to help you
do this.
99
COGNOS - Impromptu Administrator
User Defined Functions
•A User-Defined Function is a custom function created
outside of Impromptu.
•These UDFs exist in either a database or a library file.
•As an Impromptu Administrator, your job is to make
the UDFs known to Impromptu, so that the people
creating reports can access them.
100
COGNOS - Impromptu Administrator
UDFs (cond…)
Impromptu reports can access three types of functions:
•
•
•
Those built into Impromptu.
Those stored in external library files(e.g.,DLLs).
Those stored in a database.
101
COGNOS - Impromptu Administrator
Procedure
– Place the Declaration of the Function into the
COGUDF.SQL File.
– Place the Function details (Name and List of its
Properties) in IMPFUNCT.INI file.
• [Built-in Function List] --> Name.
• [Function-name]
--> Properties List.
102
COGNOS - Impromptu Administrator
Wrap Up
Wrap up
? What is the Impromptu base product?
Impromptu User
? How does Impromptu deliver results?
run routine queries
run ad hoc queries
access standardized calculation and
conditions.
104
COGNOS - Impromptu Administrator
Wrap up
? What are the four general steps in a project?
Plan
Implement
Deploy
Maintain.
? Name three ways to connect Impromptu to the
database.
a native driver (built into Impromptu)
ODBC
a database vendor's gateway.
105
COGNOS - Impromptu Administrator
Wrap up
? Name two techniques for creating Impromptu joins.
manually--one join at a time.
automatically--let Impromptu create them for you.
? Name six types of joins.
equi-join
non-equi join
outer join
self join
compound join
complex join.
106
COGNOS - Impromptu Administrator
Wrap up
? Name four items contained in folders.
calculations
conditions
prompts
columns.
? What are conditions?
pre-defined filters.
107
COGNOS - Impromptu Administrator
Wrap up
? Name six things you can do to folders.
rename folders
move folders
add new folders
remove folders
copy folders
add calculation, conditions and prompts.
? Name five ways that user classes can help you control
access to data in the database.
limit table access
limit folder access
restrict select values
filter values
set Governor values.
108
COGNOS - Impromptu Administrator
Wrap up
? Name four privileges assigned only to the Creator.
set its own password
set or changes passwords for all other user classes
override Governor restrictions
access Distributed catalog directly.
? When would you not be able to access the Creator user
class (assuming you know the password)?
when you are running the User version of Impromptu.
109
COGNOS - Impromptu Administrator
Wrap up
? Where do the following icons indicate function
processing is taking place?
 PC or the Databaseabase
 PC only
PC only
 Database only.ly
110
COGNOS - Impromptu Administrator
Wrap up
? What are the steps to providing access to an external
UDF in a DLL?
 Add the function declaration to COGUDF.SQL.
 Add the function name and its properties to
IMPFUNCT.INI.
? How is the process different for providing access to a
database UDF?
 Use COGUDFxx.SQL and xxFUNCT.INI instead, where
xx indicates the specific database system.
111
COGNOS - Impromptu Administrator
Wrap up
? Name five types of restrictions applied in the Governor
tab.
restrictions on queries, e.g., outer join
user privileges such as creating and editing reports
user privileges such as modifying the catalog
limits for data and table retrieval
limits for query execution time.
? Name three local data sources.
Snapshot
Thumbnail
Cache.
112
COGNOS - Impromptu Administrator
Wrap up
? What are the two .INI files necessary to run Impromptu?
COGNOS.INI
IMPROMPTU.INI.
? What aspect of Impromptu does the COGNOS.INI file
control?
locations of Cognos applications and
components.
? What aspects of Impromptu does the IMPROMPTU.INI
file control?
preference settings
control settings
statistical information.
113
COGNOS - Impromptu Administrator