DR Reporting Made Easy with Report Builder 3.0. MUSE International Tuesday Workshop – Session 807 May 31, 2011, 1:00P – 3:30P Glen D’Abate. Introductions. Instructors Acmeware Course Participants. Course Overview. Objective.
MUSE International Tuesday Workshop – Session 807
May 31, 2011, 1:00P – 3:30P
The objective of this workshop is to provide an overview and demonstration of SQL Server 2008 Release 2 (R2) Business Intelligence (BI) Report Model development process as well as a demonstration of the easy-to-use SSRS Report Builder 3.0 application.
SSAS is easiest to use. Drag & Drop, Slice & Dice. Only provides numeric analysis. Difficult to develop & modify data.
Report Builder allows non-technical users to build and format custom ad-hoc reports. Report developers need not understand data relationships. Requires IT to build Report Models which limit available data columns. Complex data analysis / manipulation is not possible.
Report Projects require programmer-level technical skill set to develop reports. Uses Visual Studio programmer environment for report development. Most flexibility in report design and can link to complex T-SQL statements for reports requiring data manipulation and analysis.
Other Data Sources
Data Repository Tables Directly
Visual Studio Business Intelligence Studio (BIDS)
Models are Based on Data Defined and Available from Data Sources
BIDs is installed from the SSRS 2008 R2 Media, Selecting the option under “Shared Features”
Web Server Hosting SSRS Report Manager
Launches Report Builder application. Can open existing reports or build new reports from scratch.
Can also be Launched from Start Button
Office 2010 Look & Feel
Design View for construction report format and layout
Grouping and Report Hierarchical or Cross Tab Layout
column data field in dataset
Identifying the appropriate source of specific application data from within the DR is one of the most challenging aspects of any report development using the DR
Data is highly de-normalized in the DR. “Name” in the AdmVisits table is very different than “Name” in DLabTest table, and very different than “Name” in MriPatients table.
Many factors determine what data to include in datamart including focus of Report Model, inclusion of Inpatients and/or Outpatients, length of history required and more.
Internal ID fields are not for Report Builder but used in Report Views
Typical data fields available in Report Model
DR Fields modified to better format
New Age field computed for Report Model
Inpatient or Outpatient Locations recombined to one field
Visit Provider Type data flattened for ease-of-use in Model
Cancelled Admissions or other filtering (e.g., remove confidential patients) can be include in datamart extract
The DR table column RowUpdateDateTime can be used to identify “changes” to data for incremental updates
Restricting the volume of data and highly indexing data, especially at the Report View highest aggregate level (i.e., typically visit for MEDITECH application data) is highly effective at making report output very responsive
Customer Defined Screen queries and responses (includes multiples) and time dated repeat results
ADM registration data as well as some ABS data
Charge, receipt, adjustment, refunds and other transaction level details
Visit level financial (B/AR) data
ITS Report summary information (though no text reports yet)
Laboratory Specimen Tests & Results
Materials Management Data
Patient Allergy information including free text descriptions
Creating SSRS Report Model Projects Trade-off
What we are working with today
Importing / Exporting Data
Report Server Projects use the capabilities of the full Visual Studio IDE for Report Development
Give a meaningful Report Model name
Add to existing Solution or Create a new Solution container
Previously defined data connections
Your DR Server or IP address or the SQL Server where your datamart resides
Can use a SQL service account or Windows credentials (depends on security configuration)
Datamart database or DR livedb, livemdb, livendb, livefdb, etc.
Establishing a Data Source for the Report Model lets it know where to go to find data on upon which reports (using Report Builder) will be built
Solution is simply a container in which Projects are loaded
Project contains the objects (i.e., code) that comprise a Report Models
Data Source Views
Deployment Folder Locations
Project Properties determine where the Report Models will be deployed (must be a web server where the Report Manager application is installed)
Tabs for .dsv and .smdl
Available Tables/Columns as Determined by Data Source View Design
Example View Design
Creating Data Source Views Trade-off
Acmeware typically has a Development project and a Production project
Add a New or Existing Data Source View
New Data Source View launches wizard
Allows an existing .dsv file to be copied into project (e.g., copying a development .dsv to production)
DR lacks defined FK constraints
We typically uncheck this and build the relationships from scratch
Selecting this option usually selects some of the correct columns but inevitably the defined relationship will need to be modified after completing the wizard
While adding foreign key constrains to a datamarts would be ideal, in practice, this is very difficult since MEDITECH does not ensure the sequence in which data hits the DR (and therefore the potential order in which it hits a datamart). For example, it is technically possible for a new Lab Test Print # to appear in a patient result before the corresponding Lab Test Dictionary receives an entry for the Print #.
Creating a Data Source View to allow for ad hoc Abstract Module reporting
One patient visit may have multiple diagnosis, DRGs, or ICD-9 Procedures
Meaningful name to identify .dsv file when building Report Models
Default relationship created by wizard is typically incorrect in the context of MEDITECH DR (and corresponding datamart) schema design
Relationship Arrows should point from summary level tables to detail level tables
All three keys are required to identify a unique record in this table. This is not typical to a relational database schema.
The labels do not seem to make sense but his configuration has been tested and appears to be what is required
Summary level data is the “Source”, Detail level data is the “Destination”
Properties for selected object (Registration Data table/entity)
Modifying the Friendly Name Property will default to the Entity Name in the Report Builder
Creating Report Models Trade-off
Like .dsv files, Report Models (.smdl) files can be created as new its using a wizard or can be imported as existing Items
We recommend having a Production Project to which .dsv and .smdl files are only copied (i.e., never modify in PROD). If Data Source Name is the same in DEV and PROD, no changes are necessary when importing to PROD.
A single .dsv file is used by a single Report Model (.smdl file)
We have primarily taken the default generation rules
Recompile statistics if the .dsv changed in any way. I typically always choose the “Update model statistics” option.
Use a meaningful name as this will be seen in the Report Builder tool
Do not publish a new model with the same name because you will invalidate existing reports that are generated against this model. If you do create a new model with the same name and try to publish the model, you will see an error message. Always work on the same model to ensure that the IDs remain the same.
Internal fields should be hidden
Roles can be given a friendly name
ValueSelection property determines how a column will be treated when a filter is applied in Report Builder
A Solution, one or more Projects, or one or more Report Models may be built or deployed
Build and deployment Status
Report Models can bee seen in Report Manager assuming appropriate security settings
Launch Report Builder to develop ad hoc reports using the Report Models we have created
Reporting Builder Wizard (.dsv) File
Report Data Filters (.dsv) File
Using a fixed from/thru date range
Report Parameters & Formula Fields (.dsv) File
Formatting and Layout Options (.dsv) File
Group Features (.dsv) File
Charts & Maps (.dsv) File
Acmeware Educational Sessions