A Report by Any Other Name

Student Success Plan (SSP) is exploring alternatives to its current reporting subsystem (Hibernate+Jasper). Along the way, developers have found themselves asking "What is a 'report' anyway"? What makes a "report" different from any other UI-rendered application feature? This presentation will discuss distinctions and tradeoffs between several different approaches to thinking about and implementing report-ish features with a special emphasis on OLAP-backed report delivery, all based on the SSP development team's efforts to make its reports more useful and usable for both developers and end users.

#apereo13

Transcript

1. A report by Any other name ...would probably be just another app screen Thursday, June 6, 13
2. Overview • Intro • Reports Defined (or not) • Reports in SSP (w/o OLAP) • Reports in SSP (w/ OLAP) • Reports Defined (or not) Thursday, June 6, 13
3. Intro Thursday, June 6, 13
4. Me Thursday, June 6, 13
5. Software Architect at Unicon, Inc Thursday, June 6, 13
6. Plenty of Sakai Thursday, June 6, 13
7. Little bit of uPortal, CAS, etc Thursday, June 6, 13
8. Recently *lots* of SSP Thursday, June 6, 13
9. Thursday, June 6, 13
10. Open Source Consulting Thursday, June 6, 13
11. Commercial and Edu Thursday, June 6, 13
12. 1993 Thursday, June 6, 13
13. Open Source Support Thursday, June 6, 13
14. Hosting and Managed Services Thursday, June 6, 13
15. SSP Thursday, June 6, 13
16. Thursday, June 6, 13
17. Thursday, June 6, 13
18. “According to Sinclair statistics from 2005 – 2011, students using SSP were five times more likely to graduate. For quarter to quarter retention rates ... transitioned SSP students ... had a 37% higher rate of retention compared to students who qualified for the program but did not participate and a 26% higher rate of retention than students not designated “at risk”. http://www.studentsuccessplan.org/index.php?option=com_content&view=article&id=81:ssphistory&catid=43:main&Itemid=110 Thursday, June 6, 13
19. Thursday, June 6, 13
20. Reports Defined (or not) Thursday, June 6, 13
21. “Reports” are always separated in the requirements Thursday, June 6, 13
22. Because... Thursday, June 6, 13
23. They’re not like other app screens or features Thursday, June 6, 13
24. ...Right? Thursday, June 6, 13
25. reports are... Thursday, June 6, 13
26. Scheduled... Thursday, June 6, 13
27. ...or On Demand Thursday, June 6, 13
28. They’re as current as the last ETL job... Thursday, June 6, 13
29. ...or update in Real Time Thursday, June 6, 13
30. They’re Rolled Up... Thursday, June 6, 13
31. ...or Drilled-Down Thursday, June 6, 13
32. Pre-defined... Thursday, June 6, 13
33. ...or User-defined Thursday, June 6, 13
34. Offline... Thursday, June 6, 13
35. ...or Online Thursday, June 6, 13
36. Graphical... Thursday, June 6, 13
37. ...or Tabular Thursday, June 6, 13
38. Printable... Thursday, June 6, 13
39. ...or Interactive Thursday, June 6, 13
40. <Ugh/> Thursday, June 6, 13
41. SSP has “reports” Thursday, June 6, 13
42. And even uses a “reporting” library Thursday, June 6, 13
43. But they’re not actually any different than any other app screen Thursday, June 6, 13
44. They’re just more painful to implement Thursday, June 6, 13
45. And need to guess exactly right at the detail level Thursday, June 6, 13
46. Custom report requirements? Thursday, June 6, 13
47. That’s a new app screen! Thursday, June 6, 13
48. Top to bottom Thursday, June 6, 13
49. ... or a gnarly SQL query Thursday, June 6, 13
50. select p.first_name, p.last_name, p.school_id student_id, isnull(ms.name, 'UNKNOWN') marital_status, isnull(pd.gender, 'UNKNOWN') gender, isnull(c.name, 'UNKNOWN') citizenship, isnull(vs.name, 'UNKNOWN') veteran_status, primary_caregiver = case when pd.primary_caregiver is null then 'UNKNOWN' when pd.primary_caregiver = 1 then 'YES' else 'NO' end, isnull(ss.name, 'UNKNOWN') student_status, orientation_complete = case when pep.new_orientation_complete is null then 'UNKNOWN' when pep.new_orientation_complete = 1 then 'YES' else 'NO' end, registered = case when pep.registered_for_classes is null then 'UNKNOWN' when pep.registered_for_classes = 1 then 'YES' else 'NO' end, isnull(pel_view.pel_el, 'UNKNOWN') education_level, isnull(eg.name, 'UNKNOWN') education_goal, planned_major = case when peg.planned_major is null then 'UNKNOWN' when peg.planned_major = '' then 'UNKNOWN' else peg.planned_major end, isnull(peg.how_sure_about_major, -1) major_surety__1_unsure_5_sure, career_decided = case when peg.career_decided is null then 'UNKNOWN' when peg.career_decided = 1 then 'YES' else 'NO' end, planned_occupation = case when peg.planned_occupation is null then 'UNKNOWN' when peg.planned_occupation = '' then 'UNKNOWN' else peg.planned_occupation end, isnull(peg.how_sure_about_occupation, -1) occupation_surety__1_unsure_5_sure, confident_in_abilities = case when peg.confident_in_abilities is null then 'UNKNOWN' when peg.confident_in_abilities = 1 then 'YES' else 'NO' end, need_program_info = case when peg.additional_academic_program_information_needed is null then 'UNKNOWN' when peg.additional_academic_program_information_needed = 1 then 'YES' else 'NO' end, isnull(pfs_view.pfs_fs, 'UNKNOWN') funding_source from person p left outer join person_demographics pd on p.person_demographics_id = pd.id and pd.object_status = 1 left outer join marital_status ms on pd.marital_status_id = ms.id left outer join citizenship c on pd.citizenship_id = c.id left outer join veteran_status vs on pd.veteran_status_id = vs.id left outer join person_education_plan pep on p.person_education_plan_id = pep.id and pep.object_status = 1 left outer join student_status ss on pep.student_status_id = ss.id left outer join (select pel1.person_id, left(pel1.pel_el, LEN(pel1.pel_el) - 1) pel_el from (select distinct pel.person_id, (select el.name + ',' as [text()] from education_level el join person_education_level pel2 on pel2.education_level_id = el.id where pel2.person_id = pel.person_id for xml path ('')) pel_el from person_education_level pel where pel.object_status = 1) pel1) pel_view on pel_view.person_id = p.id left outer join person_education_goal peg on p.person_education_goal_id = peg.id and peg.object_status = 1 left outer join education_goal eg on peg.education_goal_id = eg.id left outer join (select pfs1.person_id, left(pfs1.pfs_fs, LEN(pfs1.pfs_fs) - 1) pfs_fs from (select distinct pfs.person_id, (select fs.name + ',' as [text()] from funding_source fs join person_funding_source pfs2 on pfs2.funding_source_id = fs.id where pfs2.person_id = pfs.person_id for xml path ('')) pfs_fs from person_funding_source pfs where pfs.object_status = 1) pfs1) pfs_view on pfs_view.person_id = p.id where p.student_type_id is not null order by p.last_name, p.first_name https://gist.github.com/dmccallum/4708795 Thursday, June 6, 13
51. http://www.gregthompson.org/images/stock_photos/CB014896.jpg Thursday, June 6, 13
52. So what might SSP do differently? Thursday, June 6, 13
53. OLAP Thursday, June 6, 13
54. OLAP Basics Thursday, June 6, 13
55. APP Thursday, June 6, 13
56. APP OLTP Thursday, June 6, 13
57. APP ETL Warehouse OLTP Thursday, June 6, 13
58. APP ETL Warehouse Mart Mart Mart OLTP Thursday, June 6, 13
59. APP ETL Warehouse Mart Mart Mart OLTP Thursday, June 6, 13
60. APP ETL Warehouse Mart Mart Mart OLTP OLAP Thursday, June 6, 13
61. FACT DIM_A DIM_B DIM_C DIM_D Thursday, June 6, 13
62. FACT DIM_A DIM_B DIM_C DIM_D DIM_A.3 DIM_A.1 DIM_A.2 Thursday, June 6, 13
63. Thursday, June 6, 13
64. Thursday, June 6, 13
65. Thursday, June 6, 13
66. Thursday, June 6, 13
67. Thursday, June 6, 13
68. Thursday, June 6, 13
69. Thursday, June 6, 13
70. Mondrian Thursday, June 6, 13
71. Embeddable OLAP server Thursday, June 6, 13
72. http://mondrian.pentaho.com/images/arch_mondrian_v1_lrg.png Thursday, June 6, 13
73. http://www.break.com/pictures/lsd-wins-2363721 Thursday, June 6, 13
74. Actually it’s not that bad Thursday, June 6, 13
75. Basically just an ORM Thursday, June 6, 13
76. ETL ETL Mondrian MDX QUERY MDX->Relational Mapper Execution/ Cache DB CUBE DEFS (Schema) ETL Thursday, June 6, 13
77. MDX instead of HSQL (or similar) Thursday, June 6, 13
78. Multi-Dimensional eXpressions Thursday, June 6, 13
79. SQL-Like queries in terms of the abstract cube model Thursday, June 6, 13
80. Let’s say you want to see each fact measure... Thursday, June 6, 13
81. Broken down by all the possible values of some dimension... Thursday, June 6, 13
82. SELECT [Measures].Members ON COLUMNS, [MyDim].[MyDim].Members ON ROWS FROM [MyCube] Thursday, June 6, 13
83. Measure.1 Measure.2 MyDim.1 MyDim.2 MyDim.3 1.20 167 2.22 298 1.01 991 Thursday, June 6, 13
84. Or Multiple Dimensions at once Thursday, June 6, 13
85. SELECT CrossJoin([MyOtherDim].[MyOtherDim].Members, [Measures].Members) ON COLUMNS, [MyDim].[MyDim].Members ON ROWS FROM [MyCube] Thursday, June 6, 13
86. MyOtherDim.1 MyOtherDim.1 MyOtherDim.2 MyOtherDim.2 Measure.1 Measure.2 Measure.1 Measure.2 MyDim.1 MyDim.2 MyDim.3 1.09 822 9.01 8811 3.67 676 4.02 2311 8.00 129 0.33 109 Thursday, June 6, 13
87. So maybe for some sort of “event” fact Thursday, June 6, 13
88. SELECT CrossJoin([EventSource].[EventSource].Members, [Measures].Members) ON COLUMNS, [Time].[Month].Members ON ROWS FROM [Events] Thursday, June 6, 13
89. Event Source 1 Event Source 1 Event Source 2 Event Source 2 Latency (Avg) Count Latency (Avg) Count Jan Feb Mar 1.09 822 9.01 8811 3.67 676 4.02 2311 8.00 129 0.33 109 Thursday, June 6, 13
90. Much more to it, of course Thursday, June 6, 13
91. Calculations, predicates, Dimension Hierarchies, etc Thursday, June 6, 13
92. But generally... Thursday, June 6, 13
93. You write the MDX, Modrian writes the SQL Thursday, June 6, 13
94. ...And handles the caching, security, etc... Thursday, June 6, 13
95. Or get a Presentation Tool to write the MDX for you Thursday, June 6, 13
96. Er, Data Visualization Tool Thursday, June 6, 13
97. Saiku Thursday, June 6, 13
98. Previously Pentaho Analysis Tool Thursday, June 6, 13
99. Think JPivot Thursday, June 6, 13
100. But more lickable Thursday, June 6, 13
101. http://analytical-labs.com/images/screenshots/screenshot_6.png Thursday, June 6, 13
102. It’s Just jQuery Hitting JSON REST APIs Thursday, June 6, 13
103. So take some all or none of the actual UI Thursday, June 6, 13
104. Saiku+SSP Thursday, June 6, 13
105. Thursday, June 6, 13
106. Thursday, June 6, 13
107. So it’s an analytic report Thursday, June 6, 13
108. But the UI emphasis is on filtering rather than dimensioning Thursday, June 6, 13
109. ... i.e. where rather than group by Thursday, June 6, 13
110. Thursday, June 6, 13
111. Do support CSV and PDF... Thursday, June 6, 13
112. But again... nobody’s really happy with it Thursday, June 6, 13
113. Each Report design and implementation always one-off Thursday, June 6, 13
114. And Jasper isn’t really helping with much Thursday, June 6, 13
115. Certainly doesn’t help deployer explore data on their own Thursday, June 6, 13
116. But SSP is all about making previously invisible data visible Thursday, June 6, 13
117. We won’t ever be able to handle *all* possible reports OOTB Thursday, June 6, 13
118. But dimensionalizing opens up new possibilities Thursday, June 6, 13
119. Imagine a “Current Student/Coach Assignment” fact table... Thursday, June 6, 13
120. Thursday, June 6, 13
121. Then explain the rolap mappings to Mondrian Thursday, June 6, 13
122. No need to edit schema metadata by hand Thursday, June 6, 13
123. Modrian Schema Workbench Thursday, June 6, 13
124. Thursday, June 6, 13
125. Thursday, June 6, 13
126. Thursday, June 6, 13
127. Thursday, June 6, 13
128. <Schema name="New Schema2"> <Cube name="Current Caseload" visible="true" cache="true" enabled="true"> <Table name="current_student_fact" schema="public"> </Table> <Dimension type="StandardDimension" visible="true" foreignKey="student_id" highCardinality="false" name="Student"> <Hierarchy name="Student" visible="true" hasAll="true" allMemberName="All Students" primaryKey="id"> <Table name="student" schema="public"> </Table> <Level name="Student" visible="true" table="student" column="id" nameColumn="full_name" ordinalColumn="full_name" type="Integer" uniqueMembers="true" levelType="Regular" hideMemberIf="Never"> </Level> </Hierarchy> </Dimension> <Dimension type="StandardDimension" visible="true" foreignKey="coach_id" highCardinality="false" name="Coach"> <Hierarchy name="Coach" visible="true" hasAll="true" allMemberName="All Coaches" primaryKey="id"> <Table name="coach" schema="public"> </Table> <Level name="Coach" visible="true" table="coach" column="id" nameColumn="full_name" ordinalColumn="full_name" type="Integer" uniqueMembers="true" levelType="Regular" hideMemberIf="Never"> </Level> </Hierarchy> </Dimension> <Dimension type="StandardDimension" visible="true" foreignKey="program_status_id" highCardinality="false" name="Program Status"> <Hierarchy name="Program Status" visible="true" hasAll="true" allMemberName="All Program Statuses"> <Table name="program_status" schema="public"> </Table> <Level name="Program Status" visible="true" table="program_status" column="id" nameColumn="display_name" ordinalColumn="display_name" type="Integer" uniqueMembers="true" levelType="Regular" hideMemberIf="Never"> </Level> </Hierarchy> </Dimension> <Measure name="Count" column="cnt" datatype="Integer" aggregator="sum" visible="true"> </Measure> </Cube> </Schema> Thursday, June 6, 13
129. Point Saiku At It Thursday, June 6, 13
130. type=OLAP name=ssp-reports driver=mondrian.olap4j.MondrianOlap4jDriver location=jdbc:mondrian:Jdbc=jdbc:postgresql://localhost:5432/ssp- reports;Catalog=file:/path/to/ssp-reports- schema.xml;JdbcDrivers=org.postgresql.Driver; username=sspadmin password=sspadmin Thursday, June 6, 13
131. Thursday, June 6, 13
132. Remember what we’re after Thursday, June 6, 13
133. Thursday, June 6, 13
134. Student counts by Status and Coach Thursday, June 6, 13
135. Thursday, June 6, 13
136. Thursday, June 6, 13
137. Thursday, June 6, 13
138. Rollups... Thursday, June 6, 13
139. Separate Report certainly works Thursday, June 6, 13
140. Thursday, June 6, 13
141. But should get it for free Thursday, June 6, 13
142. Saiku rollup rendering sometimes buggy Thursday, June 6, 13
143. Thursday, June 6, 13
144. Thursday, June 6, 13
145. Thursday, June 6, 13
146. And we get quasi ad- hoc queries for free Thursday, June 6, 13
147. ... e.g. “All students for a coach” Thursday, June 6, 13
148. Drill-down sort of works Thursday, June 6, 13
149. Thursday, June 6, 13
150. Little better dropping down to MDX Thursday, June 6, 13
151. Thursday, June 6, 13
152. But back to filters vs dimensions... Thursday, June 6, 13
153. Thursday, June 6, 13
154. Oops. n-many report executions to get caseload levels per “service reason” Thursday, June 6, 13
155. Make that a dimension and it’s just one Thursday, June 6, 13
156. Think of a new dimension? Thursday, June 6, 13
157. Might have to add it to your ETL Thursday, June 6, 13
158. But no more one- off Hibernate queries Thursday, June 6, 13
159. And (theoretically) no patches to SSP itself Thursday, June 6, 13
160. What about Jasper? Thursday, June 6, 13
161. Actually uses Mondrian under the covers Thursday, June 6, 13
162. For Saiku-like features you need JasperReports Server Thursday, June 6, 13
163. Which also gets you nice things like report scheduling Thursday, June 6, 13
164. But Community/ Commercial distinction can be fraught Thursday, June 6, 13
165. Oops... no ad hoc report authoring in community edition Thursday, June 6, 13
166. JasperReports Library is LGPLv3 Thursday, June 6, 13
167. But Server is AGPLv3 Thursday, June 6, 13
168. Pentaho’s model and feature set quite similar Thursday, June 6, 13
169. Licensing is more complicated Thursday, June 6, 13
170. Shipping the entire BI Platform would be a non-starter (GPL) Thursday, June 6, 13
171. Individual components generally OK Thursday, June 6, 13
172. So back to the original question... Thursday, June 6, 13
173. What do I think distinguishes a report? Thursday, June 6, 13
174. Nothing really Thursday, June 6, 13
175. Reports are just High Altitude app features Thursday, June 6, 13
176. And often less opinionated Thursday, June 6, 13
177. High Altitude? Thursday, June 6, 13
178. 1. Meta Thursday, June 6, 13
179. There’s the “stuff” you do day to day... Thursday, June 6, 13
180. ...The “stuff” the app is really meant to Do Thursday, June 6, 13
181. Commits... Thursday, June 6, 13
182. Email... Thursday, June 6, 13
183. Early Alerts... Thursday, June 6, 13
184. Journal Entries... Thursday, June 6, 13
185. Timesheets Thursday, June 6, 13
186. Then there’s the meta-stuff Thursday, June 6, 13
187. The Stuff About the Stuff Thursday, June 6, 13
188. Stuff Thursday, June 6, 13
189. Stuff Thursday, June 6, 13
190. Stuff Thursday, June 6, 13
191. Stuff Meta- Stuff Meta- Stuff Thursday, June 6, 13
192. 2. Novel Thursday, June 6, 13
193. Unexpected drill- downs Thursday, June 6, 13
194. Mashable data Thursday, June 6, 13
195. Thursday, June 6, 13
196. Thursday, June 6, 13
197. Altitude Doesn’t necessarily require a different stack Thursday, June 6, 13
198. But OLTP isn’t great at... Thursday, June 6, 13
199. Novel views/ relationships Thursday, June 6, 13
200. ...nor preserving historical data Thursday, June 6, 13
201. But you can get a long (long) way Thursday, June 6, 13
202. SSP should probably take the OLAP plunge Thursday, June 6, 13
203. At least for Analytic reports Thursday, June 6, 13
204. Makes less sense for operational reports Thursday, June 6, 13
205. And Saiku-Ad-Hoc still requires a full Pentaho install Thursday, June 6, 13
206. http://themagicaltrout.files.wordpress.com/2012/06/screen-shot-2012-06-11-at-10-20-54.png Thursday, June 6, 13
207. But OLAP will be enough to chew on for a while... Thursday, June 6, 13
208. ...Cube Design... Thursday, June 6, 13
209. ...Event Capture and ETL... Thursday, June 6, 13
210. ...Offline delivery?... Thursday, June 6, 13
211. ...printability... Thursday, June 6, 13
212. ...AuthN/AuthZ... Thursday, June 6, 13
213. ...simple/advanced UX flavors... Thursday, June 6, 13
214. But once we get all that sorted out... Thursday, June 6, 13
215. Be prepared for User Glee Thursday, June 6, 13
216. http://www.gregthompson.org/images/stock_photos/weirdguy.jpg Thursday, June 6, 13
217. dmccallum@unicon.net Thursday, June 6, 13