[aperi-dev] Re: R3 Reporting data model design

[Bill Warren <warrenw1@xxxxxxxxxx>]

There was a tentative stab at defining a few views earlier, in the reporting prototyping late last summer. Here, as an FYI, are the results of that effort: (See attached file: create_reporting_views.sql), (See attached file: model_doc.html).

The current Aperi 0.3 release reporting development includes support equivalent to these views, and a small sample of reports.

In BIRT there is another of level of support for making a standard query available to the user of the Report Designer. That level is dubbed a BIRT "data set."

For Aperi 0.3, the default plan is not to install the above views in the DB. Rather, we will include the queries that make up each view in a pre-defined BIRT data set. These data sets will be included in a BIRT Report Library that will be available to all Aperi users of the Report Designer.

It is worth noting a few benefits of the data set:

• A user of our pre-defined data sets does not deal directly with the query. We can later replace the full-blown query with a reference to a view in the DB. We can even transparently replace the direct use of the DB with a different data source technology; e.g., a custom data source, defined as an Eclipse extension to BIRT, that makes calls to an Aperi Server API
• In addition to the columns that come from the DB, it is possible to add columns that are calculated in the data set. The current Aperi 0.3 work includes defining these data-set-only columns to provide default display formatting of colums that aren't in displayable form in the DB

As I say, the current 0.3 reporting development plan includes some work based on these views.  Only a little of this work is done at this time.  The rest is part of my wrap-up work, to be finished by the end of the month.  There is still a little time to make some adjustments to better align 0.3 with longer-term reporting-view strategy.  Anything that is more than rather straight-forward adjustments, won't be achievable, on the current schedule, by me alone.

Bill

Aperi Open Source Development
IBM Tivoli, San Jose, CA    (408)284-5199 (t/l 953)

Attachment: create_reporting_views.sql
Description: Binary data

Reporting Data Model – Preliminary

09/19/2006

Views for Reporting

[TODO: Fill in “Description” column from TPC schema doc, when I track down doc that has a description of each column.]

View	Property Group	Column	Data Type	Description
v_report_switches		fabric_label	char(64)
	Switch – Info	switch_label	char(255)
		switch_devtype	smallint
		type	smallint
		ip_address	char(255)
		management_id	char(128)
		mgmt_url_addr	char(255)
		domain	char(128)
		contact	char(255)
		switch_wwn	char(16)
		location	char(255)
		parent_switch_wwn	char(16)
	Switch – Product Info	vendor_name	char(128)
		model_name	char(128)
		serial_number	char(255)
		version	char(255)
View	Property Group	Column	Data Type	Description
v_report_computers		host_url	char(255)
		hosting_server	char(250)	For a virtual server, the name of the hosting physical computer
	Platform	os_type	smallint
		os_version	char(32)
	Timestamps	last_boot_time	timestamp
		last_probe_time	timestamp
		discovered_time	timestamp
	Physical – Product Info	manufacturer	char(64)
		model	char(64)
		serial_number	char(64)
	Physical – Configuration	cpu_architecture	smallint
		processor_type	char(32)
		processor_speed	smallint
		processor_count	smallint
		ram	integer
	Capacity Info	swap_space	integer
		disk_capacity	decimal(13,0)
		disk_free_space	decimal(13,0)
		fs_free_space	decimal(13,0)
		owned_disk_cap	decimal(13,0)
		owned_disk_free_sp	decimal(13,0)
	Membership	group_name	char(64)
		domain_name	char(128)
		cluster_name	char(64)
	Status	probe_status	char(1)
View	Property Group	Column	Data Type	Description
v_report_storage_subsystems		subsystem_name	char(255)
		os_type	smallint
	Physical – Product Info	vendor_name	char(128)
		model_name	char(128)
		name	char(255)
	Physical – Configuration	cache	bigint
		code_level	char(255)
	Capacity Info	n_disks	integer
		disk_capacity	double
		disk_freespace	decimal(10,0)
		n_luns	integer
		lun_capacity	bigint
		overhead	bigint
		vg_capacity	bigint
		vg_freespace	bigint
		dg_freespace	bigint
		lgldisk_assigned_capacity	bigint
		lun_capacity_zos	bigint
View	Property Group	Column	Data Type	Description
v_report_volume_groups		name	char(128)
		freespace	decimal(13,0)
		capacity	decimal(13,0)
		volgroup_type	smallint	(volgroup_type in (VolGroup.VERITAS[=0], VolGroup.LVM[=1]))
		config	smallint
		status	smallint
		format	smallint
		nbrvolumes	integer
		nbrdisks	integer
		disk_group	char(10)
		lss	char(2)
		surf_lun_cap	decimal(13,0)
		unsurf_lun_cap	decimal(13,0)
	Timestamps	last_probe_time	timestamp
		discovered_time	timestamp
	Other	host_name	char(250)
		os_type	smallint
View	Property Group	Column	Data Type	Description
v_report_disks		computer	char(255)
		preferred_volume_name	char(256)
		capacity	decimal(13,0)
		free_space	decimal(13,0)
		os_type	smallint
	Stats	failure_predicted	char(1)
		bytes_written	double
		bytes_read	double
		statistic_time	timestamp
	Stats – Error Info	recovrd_wrt_errs	integer
		unrecovrd_wrt_errs	integer
		recovrd_rd_errs	integer
		unrecovrd_rd_errs	integer
		recovrd_vfy_errs	integer
		unrecovrd_vfy_errs	integer
		non_medium_errs	integer
		primary_defects	integer
		grown_defects	integer
	Timestamps	discovered_time	timestamp
		update_time1	timestamp	(Scan time, for local disk)
		update_time2	timestamp	(Removed time, for local disk)
	Physical – Product Info	vendor_name	char(128)
		model_name	char(128)
		serial_number	char(255)
		firmware_rev	char(8)
		manufacture_date	timestamp
	Physical – Configuration	removable_medium	char(5)
		multi_port	char(5)
		rpm	integer
		write_cache_enable	char(1)
		read_cache_disable	char(1)
		heads	integer
		cylinders	integer
		sector_size	integer
		sectors_avg_track	integer
		sectors_outr_track	integer
		sectors_innr_track	integer
		logical_block_size	integer
	Physical – Status	minutes_powered_on	integer
		temperature	smallint
	Configuration – Settings	desired_noerrrcvry	char(1)
		desired_awre	char(1)
		desired_arre	char(1)
		disk_identity	char(1)
		target	smallint
	Configuration – Status	spare_blocks	integer
		no_error_recovery	char(1)
		auto_write_realloc	char(1)
		auto_read_realloc	char(1)
	Status	write_protected	char(1)
		use_count	smallint
	Other	flags	smallint
		unsupported_model	char(5)
		subsystem_serial_number	char(255)
View	Property Group	Column	Data Type	Description
v_report_fs_or_logical_volumes		computer	char(255)
		path	char(256)
		filesystem_type	smallint
	Timestamps	scan_time	timestamp
		removed_time	timestamp
		fs_last_scan_time	timestamp
		fs_discovered_time	timestamp
	Configuraton Info	resource_type	smallint
		logical_disk_type	char(1)
		is_swap	char(1)
		mirror_count	smallint
		fs_maxfiles	integer
		fs_mount_point	char(256)
	Capacity Info	capacity	decimal(13,0)
		overhead	decimal(13,0)
		fs_physical_size	decimal(13,0)
		fs_capacity	decimal(13,0)
		fs_used_space	decimal(13,0)
		fs_free_space	decimal(13,0)
	Status	use_count	smallint
		fs_use_count	smallint
		fs_used_inodes	decimal(12,0)
		fs_free_inodes	decimal(12,0)
	Membership	fs_group	char(64)
View	Property Group	Column	Data Type	Description
v_report_luns		ss_display_name	char(255)
		display_name	char(128)
		os_type	smallint
		ss_vendor	char(128)
		ss_model	char(128)
	Timestamps	update_timestamp	timestamp
	Configuration Info	device_id	char(255)
		logical_disk_type	char(5)
		is_swap	char(5)
		mirror_count	smallint
		snap_source_id	integer
		is_vcmdb	char(5)
		replication_mode	char(5)
		not_exposed	char(5)
		lun_identity	char(5)
		format	smallint
		name	char(255)
		package_redundancy	smallint
		data_redundancy	smallint
	Capacity Info	capacity	bigint
		overhead	bigint
	Status	use_count	smallint
		is_assigned	smallint
View	Property Group	Column	Data Type	Description
v_report_exports		computer_name	char(255)
		export_name	char(256)
		path	char(256)
		protocol	char(1)
	Timestamps	discovered_time	timestamp