J. Biomedical Science and Engineering, 2013, 6, 1072-1076 JBiSE
http://dx.doi.org/10.4236/jbise.2013.611134 Published Online November 2013 (http://www.scirp.org/journal/jbise/)
Web-based: A data warehouse on osteoporosis data
warehouse in the osteoporosis community health
information management system
Qiang Wang, Yingchao Shen
Orthopedic Surgery, Changshu Hospital Affiliated to Nanjing University of Chinese Medicine, Changshu, China
Email: RabbitShen@126.com
Received 13 September 2013; revised 18 October 2013; accepted 27 October 2013
Copyright © 2013 Qiang Wang, Yingchao Shen. This is an open access article distributed under the Creative Commons Attribution
License, which permits unrestricted use, distribution, and reproduction in any medium, provided the original work is properly cited.
Objective: To establish an interactive management
model for community-oriented high-risk osteoporosis
in conjunction with a rural community health service
center. Materials and Methods: Toward multidimen-
sional analysis of data, the system we developed com-
bines basic principles of data warehouse technology
oriented to the needs of community health services.
This paper introduces the steps we took in construct-
ing the data warehouse; the case presented here is
that of a district community health management infor-
mation system in Changshu, Jiangsu Province, China.
For our data warehouse, we chose the MySQL 4.5 re-
lational database, the Bro wser/Serv er, (B/S) mo d e l , an d
hypertext preprocessor as the development tools. Re-
sults: The system allowed online analysis processing
and next-stage work preparation, and provided a plat-
form for data management, data query, online analy-
sis, etc., in community health service center, specialist
outpatient for osteoporosis, and health administration
sectors. Conclusion: The users of remote management
system and data warehouse can include community
health service centers, osteoporosis depart ments of ho-
spitals, and health administration departments; pro-
vide reference for policymaking of health administra-
tors, residents’ health information, and intervention
suggestions for general practitioners in community
health service centers, patients’ follow-up information
for osteoporosis specialists in general hospitals.
Keywords: Community Management; Data Warehouse;
Information Management System; Osteoporosis; Remote
Management of Osteoporosis
Osteoporosis is a multifactorial bone disease in addition
to being a noninfectious chronic disease (NCD) that is
arousing increased attention-like diabetes mellitus and
hypertension. Osteoporosis has definite pathophysiolo-
gical and social psychological effects in addition to ex er-
ting economic consequences [1]. Osteoporo sis can increase
the incidence of fractures. Among osteoporosis patients,
about 30% of females suffer fractures [2]. Osteoporosis
has no obvious symptom, thus it is referred to as a “si-
lent” disease. Patients tend not to properly understand treat-
ment nor do they place a strong emphasis upon it. Most
elderly osteoporo s is patients have a low educational lev e l
and therefore do not fully use the services provided by
health departments. Therefore, staff in community health
centers needs to provide instruction, intervention, and
appropriate management prior to disease attack.
Community intervention is an important aspect in pro-
moting NCD management [3]. Active risk evaluation of
the population at risk from osteoporosis and appropriate
intervention at an early stage of the condition through the
community health service are effective in reducing the
incidence of osteoporosis and improving patients’ quality
of life [4].
Since osteoporosis is a multifactorial, chronic epidemic
disease, informationization provides important technical
support in real-time management and efficient use of hu-
man resources [5]. Integration of all procedures is the
most important factor in community intervention and
management. It involves optimization and assembly of
staff, equipment, and desktop application systems to pro-
mote cooperation among application users, departments
and staff in general hospitals, community health service
centers, medical staff overall, community residents, medical
institutions, and health administration officials. Most hos-
pital information systems are used to manage the busi-
ness side of proceedings, that is, one-time integration of
information, and they are unable to support full data use.
Q. Wang, Y. C. Shen / J. Biomedical Science and Engineering 6 (2013) 1072-1076 1073
Thus, there is a need to build a data warehouse that al-
lows full analysis towards strategy development [6].
In cooperation with rural community health service cen-
ters, we analyzed data sources towards promoting com-
munity health management for osteoporosis by means of
a data warehouse.
2.1. Data Research
We invited staff from Yushan Xinglong Community Health
Service Center, the Departments of Orthopedics, Gyne-
cology and Obstetrics, and Endocrinology of Changshu
Hospital of Tradition al Chinese Medicin e, Health Bureau
of Changshu, and software engineers to compose the
group for our data research. The outpatient clinic of
Changshu Hospital of Traditional Chinese Medicine was
responsible for the organization and communications re-
lated to this study, and the data warehouse was construct-
ed for physicians from Yushan Xinglong Community
Health Service Center and the indicated departments of
Changshu Hospital of Traditional Chinese Medicine and
health management institutions. The data warehouse us-
ers were provided with basic training with regards to th e
study concept and required levels of analysis.
2.2. Data Scope
Data source was divided according to subjects and data
research to integrate information models of various busi-
ness systems for macroscopic merge, abstraction, and da-
ta scope to confirm that all required data were extracted
from business systems and well organized. The data scope
was defined as community residents with osteoporosis or
at a high risk of osteoporosis; it mainly comprised post-
menopausal females and elderly males (older than 60
years). The data of all residents in the data scope were
used in the study except those of residents who moved
out of the community or died.
2.3. Subject Elements of Community
Management for Osteoporosis
The subject elements for community management of os-
teoporosis included dimension (content of business), sub-
jects (includes data of subjects), particle size (dimension
levels to extract data details), and storage limit (of data).
Based on a comprehensiv e analysis of data conducted by
physicians from community health service centers and
the indicated departments of Changshu Hospital of Tra-
ditional Chinese Medicine, several dimensions were con-
firmed; these included baseline information, osteoporo-
sis-related high-risk factors, bone density, and assess-
ments of interventions. Each dimension was divided into
several levels, and particle size was used to confirm and
elucidate the dimension levels.
Osteoporosis-related high-risk factors are complex.
Among males, 21 high-risk factors have been identified,
whereas 26 have been identified among females [7]. We
screened the high-risk factors and provided options for
data entry, analysis, and minin g. Evaluated osteoporosis-
related high-r isk factors comprised age, body mass, fam-
ily history, and nutritional factors; intervention measure-
ments included appropriate diet, exercise, sufficient cal-
cium intake, vitamin D intake, and correction of poor life-
style habits; bone density examination items included quan-
titative computed tomography, dual energy X-ray absorp-
tiometry, and ultrasound bone intensity examination; treat-
ment measurements included calcium agents, vitamin D,
alendronate sodium tablets, calcitonin, Acla sta, a nd est ro-
gen replacement therapy. The dose and duration of each
intervention, examination, and treatment were recorded.
The snowflake model was used to summarize the a b o v e
multidimensional data relationships; the model consisted
of a fact table and a group of dimension tables. This al-
lowed the dimension factors to be further divided; for
example, intervention factors included sports, amount of
sunlight, appropriate diet, and medication. The “interven-
tion” dimension could be snow flaked, that is, the dimen-
sions could be decomposed in terms of the attributes for
sports, amount of sunlight, appropriate diet, and medica-
tion to form four-dimension tables. For the “female” di-
mension, time since menopause had to be recorded as the
particle size.
2.4. Dynamic Loading
Community management needs to be continuously up-
dated so as to provide dynamic data loading when con-
structing a database. Traditional data warehouses store
diachronic, resting, and integrated business data, which
initially load data and then support business searches.
However, data loading with a dynamic data warehouse
can load data while simultaneou sly allowing users to co n-
duct searches. Moreover, dynamic loading does not af-
fect the use of the data warehouse, which allows the im-
mediate analysis of loading data. The intervention measures
and bone density measurements of subjects could be con-
tinuously recorded.
3.1. Fact Table of Data Warehouse
The fact table contains all the osteoporosis health data.
Copyright © 2013 SciRes. OPEN ACCESS
Q. Wang, Y. C. Shen / J. Biomedical Science and Engineering 6 (2013) 1072-1076
The fact table was the largest table we constructed and
its information was updated the fastest in the data ware-
house. All attributes for each record depended on the pri-
mary key of the fact table, and a series of foreign keys
was associated with each dimension table. With regard to
the search function of the data warehouse, it is necessary
to minimize connection operations among different ta-
bles. The fact table was designed as shown in Table 1.
For each attribute in the fact table, the dimension infor-
mation was recorded using a special dimension table to
confirm the values of some dimensions [4]. The design
of the dimension tables was based on the table name
(main key word coding, name), and content in parenthe-
ses in Table 2 represents the field names. The main di-
mension tables are shown in Table 2.
The search function was improved by combining the
small dimension tables. For example, the social dimen-
sion table is presented in Table 3.
Table 1. Design of the osteoporosis health file data fact table.
Field name Meaning
ID File compile
Xingming Name
Chusheng Birth data
Shequ Community
Xingbie Sex
Juejing Menopause time (female)
Jibing History of disease
Jiazu Family history
Gmdff Measurement method of bone density
Gmdt Measurement time of bone density
Gmdz Bone density value
Gmdgk Bone mineral density
Ganyuss Intervention diet
Ganyush Intervention life
Ganyuyd Intervention exercise
Ganyuyw Intervention drugs
Beizhu Remarks
Lrname Name of data entry staff
Lrrq Date of data entry
Guidang Filing or not (1: filing; 0: not)
Table 2. Design of the main dimension tables.
Structure of dimension table Instruction
BASIC (basic information) Dimension table of basic information
(risk factors with osteoporosis) Dimension table of risk factors
for osteoporosis
BMD (bone mineral density) Dimension table of bone miner al
IM (intervention measures) Dimension table of interventi on
Interactive Data Distributed Structure
System management was achieved through interactive
management of physicians from the community health
service center and general hospital and health admini-
stration departments. Therefore, we used an interactive
data mart structure. Although different data marts were
achieved in specific departments, they were integrated
and interlinked to provide a compreh ensive data view for
business scope. The administrator assigned different per-
missions for different grades of users. The user had to
register the database for data entry, correction, export,
and analysis, that is, the data warehouse for business
scope. For example, the staff of different community
health service centers could enter different data relating
to their own commun ities.
User management is also a special dimension table.
The user management was built as presented in Table 4.
Hypertext Preprocessor was used to develop web appli-
cation programs. The Linux operating system was em-
ployed as the server operating system, equipped with the
Apache 2.0 operating platform. The MySQL 4.5 relational
database was built and is accessible on the internet. The
user can input http://www.cszlf.net/sycweb/ in the web
browser, and the log-in page appears as seen in Figure 1.
The data entry interface appears after inputting the
user name and password Figure 2.
The system provides online analysis for real-time and
online analysis of data in the data warehouse, including
individual case analysis, group analysis, and global an aly-
sis. For individual case analysis, the user can search the
target records through a conditional search and click the
“analyze” button on the interface to obtain the individual
case analysis by system and primary diagnosis and treat-
ment suggestions; these do, however, require the confir-
mation of clinical physicians. This process achieves com-
puter-assiste d di ag nosi s and treatment Figure 3.
Table 3. Social dimension table (expression of patients in the
Id Community no.
Sqmc Community name
Table 4. Users tabl e (system admini st rator list).
Id User encoding
Name Logging name
Pass Logging password
XinmingReal name
Level Management level
(0: system administrator; 1: common administrator)
Last Last logging time
Copyright © 2013 SciRes. OPEN ACCESS
Q. Wang, Y. C. Shen / J. Biomedical Science and Engineering 6 (2013) 1072-1076
Copyright © 2013 SciRes.
Figure 1. Log-in screen of Changshu osteoporosis remote management
sys tem.
Figure 2. Data entry interface of the Changshu osteoporosis remote management system.
Figure 3. Online analysis interface of the Changshu osteoporosis remote management sys-
Q. Wang, Y. C. Shen / J. Biomedical Science and Engineering 6 (2013) 1072-1076
There is a common lack of appropriate tool software and
unified ordered organization in community public health
service management, [8] and it is difficult to analyze
daily working data or to further community health man-
agement. Although some information management sys-
tems for osteoporosis have been developed and used,
most of them use databases rather than data warehouses;
thus, they do not support data analysis functions or data
mining [9]. We have been collecting data since 2010, and
we have combined basic principles and methods for a
data warehouse with community intervention in construc-
ting such a warehouse that achieves dynamic loading.
This data warehouse has several positive features: It ef-
fectively organizes d ata sources, and provides deep-level
data mining and online analysis. In addition, users can
include community health service centers, osteoporosis
departments of hospitals, and health administration de-
partments. In this way, it is possible to provide strategic
evidence and support for health administration depart-
ments, provide complex computer-aided diagnosis and
treatment for community health service centers, provide
patients with follow-up data, and provide raw scientific
research data and real-time health statistics for hospital-
based physicians. The system can be used for data man-
agement, data query, online analysis, etc. for specialist out-
patients for osteoporosis in community health service
center and health administration sectors [10]. The web-
based network environment eases data searches, and on-
line analysis of the data warehouse in combination with
data-mining techniques can analyze the data distribution
and dynamic changes of each variable, support research
and strategy, and provide evidence for clinical research
and community NCD [11].
Thanks to Changshu audio-visual classroom Zhao Weifei for technical
[1] Yang, S.Q., Wang, H.P., Guan, L.Z., Qu, L.Y. and Li,
M.Q. (2008) Effect evaluation on health education for the
elderly with high-risk potential of osteoporosis in Zhong-
guancun Community of Beijing. Chinese Journal of Health
Education, 24, 593-595.
[2] Liu, Z.L., Huang, J.G. and Liu, Z.J. (2007) The discus-
sion and analysis for the mechanism and prevention of
osteoporosis in women after menopause. China Medical
Her, 24, 157-158.
[3] Hu, R., Zhou, Z.M., Z hai, D.Y., Yue, X.Y., Xu, G.L. and
Liu, X.F. (2010) Awareness of hypertension among is-
chemic stroke survivors in the community: Survey and
analysis. Postgraduate Medical Journal, 23, 167-170.
[4] Stolba, N. (2007) Towards a sustainable data warehouse
approach for evidence-based heathcare. PhD Thesis, Vi-
enna University of Technology, Vienna.
[5] Wang, H.X., Wang, H.L. and Li, F.H. (2011) Application
of nursing information system. Postgraduate Medical
Journal, 24, 72-74.
[6] Zhang, J.X., Wang, X.L., Chang, S.F., Li, R., Wang, H.J.
and Kan, X.L. (2010) Research and construction for data
warehouse of TCM medical record in cold. Jiangsu Jour-
nal of Traditional Chinese Medicine, 42, 66-68.
[7] Yang, J.Q. and Jiang, X.Y. (2005) Risk factors and their
assessment of osteoporosis. Journal of Fujian Medicine
University, 39, 237-239.
[8] Chen, W.C., Han, Z.Z., Zhang, L. and Yang, J. (2008) Re-
search on developing information system with new con-
cept for community health services. Journal of Chinese
General Practice, 11, 1414-1416.
[9] Zhao, L., Xin, Z.Q. and Yu, W.Z. (2009) Clinic use of in-
formation management system of osteoporosis patient.
Modern Hospital, 9, 142-144.
[10] Yu, J.F., Shen, Y.C., Zhou, B.K., Chen, X., Wang, Q. and
Gu, Y.F. (2012) Remote management system of osteopo-
rosis community intervention and design of data ware-
house. Journal of Chinese General Practice, 15, 26-28.
[11] Pan, X.Y. (2010) Survey data analysis for health needs of
urban community. Chinese Communication Doctors, 12,
Copyright © 2013 SciRes. OPEN ACCESS