Fundamentals Flashcards
(37 cards)
Chronicles
Post-relational database
Clarity
- Is the relational database
2. The process of moving production data to this relational database (ETL process)
Operational Reporting
Time sensitive / relatively small / Accessible in Hyperspace / Actionable (used as part of a workflow)
Operation Rptg Tools
Application reports: DAR, ASAP Track Board,
Reporting Workbench: flexible, more report like
KB-SQL: mainly used in the Clarity extract process
Chronicles Ad-Hoc: available in Text only
Cache Script: programming language on which Chronicles is built
Analytical Reporting
Comprehensive, Summarized, Standardized. A year, a month, many departments… trending
What is the main difference between grouping in SQL and grouping in Crystal Reports
SQL’s Group By collapses the results into groups, no longer displaying the details.
Aggregate functions perform a specific operation over all rows in a group.
COUNT(*) - Counts all rows
COUNT(value) - counts all non-null values
AVG(value) - averages all non-null values
MAX(value) - returns highest value
MIN(value) - returns lowest value
SUM(value) - returns the sum (total) of all non-null values
In Crystal why would you use ‘Show SQL query’?
To verify two things:
- All the record selection criteria is in the WHERE clause.
- SQL syntax makes sense.
Why do NULLs exist in Epic?
- fields are not required
- data in one table may be more up to date than another
- poorly or incorrectly formatted data may be deleted, resulting in a null.
IsNull( ) returns True if the field contains a null value. Why is it handy?
Use it to give nulls a default value, *No Attending Provider If… Then… Else
What are two ways of handling null values?
Assign a default value.
Filter them from your report.
If you are using a function or operator and want more information about it, what are two ways to open Crystal Reports online help?
F1 and the ? button.
True or False: a clarity table with only one column listed as the primary key will always hold no-add, single-response data.
False. ID (no-add,single) or CSN (overtime, single)
Your clarity ETL administrator has created a custom Clarity column in your environment to extract a previously unextracted item. Which tool(s) can you use to look up information about that clarity column?
Clarity Compass
What is the difference between a foreign key and a primary key?
A foreign key serves as a link between two tables. A primary key is a unique identifier.
If you include multiple database objects in your report and do not link them Crystal will attempt to combine every row in the first table with every row in the second. The result is this…?
Cartesian product
What are the Data Types in SQL…
VARCHAR, NUMERIC, DATETIME, FLOAT (cdr is the only float date), INTEGER (category)
SQL queries are written with their clauses in what order?
SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
SQL query clauses are processed in what order?
FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY
On the SQL Editor what are two ways to run your query?
F5 or by clicking the Execute! button.
UNION or UNION ALL are used for what purpose?
Combine two separate groups of results. Buy default they return only distinct values. Use UNION ALL to allow duplicate values.
True/False: After inserting a Command object you can add additional tables in Crystal.
False. Crystal will often perform a Cartesian product of the results and filter it locally.
True/False: A Command Object always makes your report run faster!
False. It does give you more control over what actions are performed on the server.
True/False: Formatting doesn’t matter when pasting a SQL query into a command object.
True
