four phases of report authoring targeted for executives and upper management by: ben aminnia...
Post on 28-Dec-2015
216 Views
Preview:
TRANSCRIPT
Four Phases of Report AuthoringFour Phases of Report Authoring
Targeted for Executives and Upper ManagementTargeted for Executives and Upper Management
By: By: Ben AminniaBen Aminnia
President, L.A. SQL Server Professionals Group President, L.A. SQL Server Professionals Group www.sql.lawww.sql.la Database Architect, Pointer Corporation www.pointercorp.com Database Architect, Pointer Corporation www.pointercorp.com
22
ObjectivesObjectives
There are four phases in report authoring;There are four phases in report authoring; Most textbooks only cover 1.5 of those phase;Most textbooks only cover 1.5 of those phase; We’ll go over all four phase;We’ll go over all four phase; With emphasis on the missing 2.5;With emphasis on the missing 2.5;
33
AgendaAgenda
Phase 1: Phase 1: The UsersThe UsersPhase 2: Phase 2: The DataThe DataPhase 3:Phase 3: The ReportThe ReportPhase 4:Phase 4: The Delivery and AdministrationThe Delivery and AdministrationQuestions and AnswersQuestions and Answers
44
What’s Covered in the What’s Covered in the TextbooksTextbooks
Phase 1: The Users No 00 %
Phase 2: The Data Not Enough 20 %
Phase 3: The Report OK 80 %
Phase 4: The Delivery and Administration So-So 50 %
Overall % Presented for all 4 phases 150 %
Maximum % for all 4 phases 400 %
55
A Typical ScenarioA Typical Scenario
A client calls me or sends me an email …A client calls me or sends me an email …Ben, I need a report;Ben, I need a report;How much is it going to cost?How much is it going to cost?When is it going to be ready?When is it going to be ready?Now, you be “Ben” and I’ll be “the client” …Now, you be “Ben” and I’ll be “the client” …How would you answer these questions?How would you answer these questions?
What questions will you ask me before What questions will you ask me before giving me your cost and time estimates?giving me your cost and time estimates?
Or maybe you have an estimate, even Or maybe you have an estimate, even without asking any questions?!without asking any questions?!
66
Why These Four Phases?Why These Four Phases?
You build the most sophisticated report;You build the most sophisticated report;Then the manager says: “That’s nice but …”Then the manager says: “That’s nice but …”
Why is that % value over there lower Why is that % value over there lower than this % value?than this % value?
It should be higher!It should be higher!
77
Phase 1: Phase 1: Understanding the Understanding the UsersUsers
Reading their mindReading their mindUnderstanding their psychologyUnderstanding their psychologyDo they know what they want?Do they know what they want?Do they have time to tell you what they want?Do they have time to tell you what they want?Is it in writing or just in their head?Is it in writing or just in their head?Is there a sample?Is there a sample?Is it similar to another existing report?Is it similar to another existing report?
88
Phase 1: Phase 1: Understanding the Understanding the UsersUsers
Maybe they can designate someone else (like a Maybe they can designate someone else (like a manager or director who reports to them) to be manager or director who reports to them) to be your contact and who will have time to sit your contact and who will have time to sit down with you and tell you exactly what the down with you and tell you exactly what the boss wants?boss wants?
What problem are they trying to solve?What problem are they trying to solve?WHYWHY do they need this report? do they need this report?Is it for their personal use to manage their own Is it for their personal use to manage their own
business better or is it mandated by some business better or is it mandated by some other authority – inside or outside the other authority – inside or outside the organization – or even a government agency?organization – or even a government agency?
99
Phase 1: Phase 1: Understanding the Understanding the UsersUsers
Did they request the report themselves or did one Did they request the report themselves or did one of their staff tell you what they want?of their staff tell you what they want?
Do they even look at the existing report which you Do they even look at the existing report which you are supposed to improve?are supposed to improve?
How sophisticated are they?How sophisticated are they?If they asked for a If they asked for a weeklyweekly report … report …Do they understand the difference between Do they understand the difference between “Last “Last
week”week” and and “Last 7 days”“Last 7 days”??Which one do they want?Which one do they want?Can you show them a prototype / mock report Can you show them a prototype / mock report
before spending hours / days on development and before spending hours / days on development and formatting?formatting?
1010
Phase 2: Phase 2: Evaluating the Data Evaluating the Data SourcesSources
Some fundamental questions which are Some fundamental questions which are virtually missing from every reporting textbook virtually missing from every reporting textbook ……
Where is the data?Where is the data?How accessible is it?How accessible is it?Is it ready for reporting?Is it ready for reporting?Why is it that textbooks do not cover these Why is it that textbooks do not cover these
topics?topics?Is it because they don’t have the answers?Is it because they don’t have the answers?Or they don’t understand the concept of an Or they don’t understand the concept of an
end-to-end textbookend-to-end textbook??
1111
Phase 2: Phase 2: Evaluating the Data Evaluating the Data SourcesSources
In the view of reporting textbooks …In the view of reporting textbooks …The Data Source is there and ready to be The Data Source is there and ready to be
reported on;reported on;You just need to know the connection string / You just need to know the connection string /
id / password / etc.id / password / etc.Not so easy in the real world!Not so easy in the real world!What if the data source is unreachable?What if the data source is unreachable?What if the data owner doesn’t give you What if the data owner doesn’t give you
access?access?What if some required data elements aren’t What if some required data elements aren’t
being tracked yet? being tracked yet? (e.g. CreateDate vs. LastUpdate)(e.g. CreateDate vs. LastUpdate)
1212
Phase 2: Phase 2: Evaluating the Data Evaluating the Data SourcesSources
And even more interesting …And even more interesting …Not only they may want one date instead of Not only they may want one date instead of
another another (e.g. CreateDate vs. LastUpdate)(e.g. CreateDate vs. LastUpdate)
They may also say: “Why is that other previous They may also say: “Why is that other previous report using report using LastUpdatedLastUpdated? That should have ? That should have used used CreateDateCreateDate as well.” as well.”
1313
Phase 2: Phase 2: Evaluating the Data Evaluating the Data SourcesSources
Some examples from my real-life experience in Some examples from my real-life experience in the past couple of years …the past couple of years …
From a user email elaborating on the report From a user email elaborating on the report requirements: “requirements: “The weekly report needs to The weekly report needs to represent the status of the cases as they were represent the status of the cases as they were on those days even if we pull it a week, a on those days even if we pull it a week, a month etc... later.month etc... later.””
1414
Phase 2: Phase 2: Evaluating the Data Evaluating the Data SourcesSources
From another user email elaborating on the From another user email elaborating on the report requirements: “report requirements: “The report should show The report should show only my staff’s weekly activities; not John’s only my staff’s weekly activities; not John’s staff.staff.””
My 1My 1stst thought: thought: WHERE DeptID=“X”WHERE DeptID=“X”Not so fast!Not so fast!This was a business divorce case!This was a business divorce case!We had to split the database and put it on two We had to split the database and put it on two
separate servers.separate servers.
1515
Phase 2: Phase 2: Evaluating the Data Evaluating the Data SourcesSources
Another example …Another example …We need five SSRS reports and here’s the We need five SSRS reports and here’s the
layout and screenshot of the application where layout and screenshot of the application where the data is entered.the data is entered.
Where’s the data connection information?Where’s the data connection information?Talk to John; Here’s his email and phone #;Talk to John; Here’s his email and phone #;But that’s a different company!But that’s a different company!I ended up building an elaborate SSIS package I ended up building an elaborate SSIS package
to pull the data from the other company’s to pull the data from the other company’s server, before I could start working on the new server, before I could start working on the new SSRS reports.SSRS reports.
1616
Phase 2: Phase 2: Evaluating the Data Evaluating the Data SourcesSources
Another example …Another example …We need some new reports.We need some new reports.Where’s the data connection information?Where’s the data connection information?Talk to Bob; Here’s his email and phone #;Talk to Bob; Here’s his email and phone #;Bob: What’s the connection information to this Bob: What’s the connection information to this
data source?data source?We will not give it to you; Only our staff are We will not give it to you; Only our staff are
allowed to extract this data!allowed to extract this data!
1717
Phase 2: Phase 2: Evaluating the Data Evaluating the Data SourcesSources
The The IdealIdeal vs. the vs. the Real / Practical Real / Practical Data SourceData Source
In an Ideal Data Source …In an Ideal Data Source …All Calculations / Summarizations / All Calculations / Summarizations /
Aggregations are already done;Aggregations are already done;Data has been stored in Analysis Services Data has been stored in Analysis Services
(SSAS) where applicable;(SSAS) where applicable;Shared Data Sources have been defined within Shared Data Sources have been defined within
Reporting Services (SSRS) before report Reporting Services (SSRS) before report authoring starts;authoring starts;
1818
Phase 2: Phase 2: Evaluating the Data Evaluating the Data SourcesSources
The The IdealIdeal vs. the vs. the Real / Practical Real / Practical Data SourceData Source
In the Real World (or at least in most cases) …In the Real World (or at least in most cases) …Calculations / Summarizations / Aggregations Calculations / Summarizations / Aggregations
are missing;are missing;Data may not even be accessible to the Data may not even be accessible to the
reporting world and you may need to run an reporting world and you may need to run an SSIS package to import it periodically;SSIS package to import it periodically;
1919
Phase 2: Phase 2: Evaluating the Data Evaluating the Data SourcesSources
You have to choose between two You have to choose between two methodologies in order to bridge the gap:methodologies in order to bridge the gap:
1.1.A simple data source (e.g. one or more joined A simple data source (e.g. one or more joined tables) followed by a complex report definition;tables) followed by a complex report definition;
2.2.A complex data source (e.g. one or more A complex data source (e.g. one or more stored procedures with temporary tables, stored procedures with temporary tables, groupings, aggregations, etc.) followed by a groupings, aggregations, etc.) followed by a simple report definition;simple report definition;
My choice is the 2My choice is the 2ndnd approach as much as approach as much as possible: possible: GO SP!GO SP!
2020
Phase 3: Phase 3: Composing the ReportComposing the Report
That’s where the textbooks are most helpful.That’s where the textbooks are most helpful.Categories / Templates (lists, tables, 1-to-Categories / Templates (lists, tables, 1-to-
many, cross-tab, tablix, etc.)many, cross-tab, tablix, etc.)Formatting; Fonts; Page Breaks; Formatting; Fonts; Page Breaks;
Headers/Footers; Groupings; etc.Headers/Footers; Groupings; etc.Wizards and other tools to get you started Wizards and other tools to get you started
quicklyquickly33rdrd Party vendors Party vendors
2121
Phase 4: Phase 4: Delivery and Delivery and AdministrationAdministration
How are the users going to get the report?How are the users going to get the report?Are they accessing the Report Server directly?Are they accessing the Report Server directly?Do they have a SharePoint site where their Do they have a SharePoint site where their
reports are deployed?reports are deployed?Is there an existing application where this new Is there an existing application where this new
report will just become another entry under its report will just become another entry under its “Reports” menu?“Reports” menu?
2222
Phase 4: Phase 4: Delivery and Delivery and AdministrationAdministration
Are we building a new application to host these Are we building a new application to host these reports?reports?
Specifying Connections and Credentials for the Specifying Connections and Credentials for the ReportViewer Web Server Control ReportViewer Web Server Control http://msdn.microsoft.com/en-us/library/aa983458(v=VS.90).aspxhttp://msdn.microsoft.com/en-us/library/aa983458(v=VS.90).aspx
Do they want the report emailed to them Do they want the report emailed to them periodically?periodically?
SchedulingSchedulingDeploymentDeployment
PushPush: From Report Builder => Save As …: From Report Builder => Save As … PullPull: From Report Server => Upload File: From Report Server => Upload File
2323
In Summary: In Summary: Here’s what we Here’s what we learned todaylearned today
Understanding the users and their requirements
Two Weeks 80 Hours
Developing Data Sources (Stored Procedures)
Two Days 16 Hours
Developing Report Definitions (RDLs), Deploying, and Scheduling
Half a Day 4 Hours
TOTAL Two and a Half Weeks
100 Hours
2525
Contact InformationContact Information
• Emails:Emails: president@sql.la president@sql.la ben@pointercorp.com ben@pointercorp.com
• Websites:Websites: www.sql.lawww.sql.la www.pointercorp.com www.pointercorp.com www.vipletters.com www.vipletters.com
top related