[MEDITECH-L] Data Repository Structure mapping
Laura Wilson
their relationship with each other? Something like this below would be
awesome!
I realize this example is very simplistic and to truly do this, it would
be huge, but how nice it would be!
Maybe even if there was a lookup of sorts where you could enter a field
name and it would tell you all tables that this field is found in? Or
enter a table name and it would list the tables that you can link to
from the indexes in both tables?
I am also running into lots of tables that are empty or only partially
populated. Most of the time, the response I am getting from Meditech is
"that table is being phased out." Are the rest of you running into this
issue also?
Laura Wilson
Asst. Director, Info Systems
Success is getting what you want. Happiness is wanting what you get.
-Dale Carnegie
Garry McAninch
Yes, it would be nice to have a routine that provides table relationships.
The only ones that I know of are designed in software applications like
ERWin. In SQL Server 2005, there is a tool called Database Diagrams, but it
requires you to build the linkages yourself. Those that have gone ahead and
built these guard them with their lives as it takes quiet a long time to put
together.
I wasn't able to see your attachement, so I'm not sure what you were showing
in your list. I use this little utility when working with clients DR sites.
It's a Stored Procedure that provides a result set of Table Names or Column
Names that are found within the database (i.e. livedb). You simply execute
the Stored Procedure along with all, or part, of the name you are searching
for. As an example:
Executing the code: EXECUTE DA_sp_Lookup Unit in a SQL query window
provides a list of User Tables that has the word "Unit" included along with
a second result set showing Column Names with the word Unit somewhere in the
name. It also provides the column number/order in the table and the column
type and size.
To use this, simply copy and past the section of code below and place it
into a SQL Server Query window and execute it to create the procedure.
Once created, you then utilize it with the following paramters: EXEC[ute]
DA_sp_Lookup <text string> .This is designed for use on SQL Server 2005,
but I believe that it should function on a SQL 2000 server also.
As far as tables that are empty or only partially populated and MEDITECH
saying that they are being phased out.. What tables are you finding this
in? There are always changes in tables due to ring releases, but to say
they're being phased out, that's news to me. It sounds more like that the
extracts are not properly activated and/or the Initial loads for those
tables have gone south. Do you have any errors in your error logs with
these tables?
Anyway, give this utility a try. If there are any problems, or if I can be
of any help, let me know.
Good Luck !!
Replied by:
Garry McAninch,
Principal
Dimensions Analysis
Phone: 905-704-1356
Mobile: 905-941-1356
e-mail: gmca...@dimensionsanalysis.com
Data Warehouse Engineering * Digital Dashboards * Multi-Dimensional
Reporting * Information Management Consulting
This communication is intended solely for the addressee(s) and contains
information that is privileged, confidential and subject to copyright. Any
unauthorized use, copying, review or disclosure is prohibited. If received
in error, please notify us immediately by return e-mail.
-- start copy here
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
USE livedb
GO
CREATE PROCEDURE [dbo].[DA_sp_Lookup](@String varchar(100))
AS
-- Procedure Name: DA_sp_lookup
--
-- Created By: Garry McAninch, Dimensions Analysis
www.dimensionsanalysis.com
-- Creation Date: December 11, 2007
-- Revision Date: December 11, 2007
--
-- Description: Stored procedure to search for table/column names
--
-- For the use by Dimensions Analysis clients
--
-- NOTE: For use in all databases, store in master
database
--
-- Usage: sp_lookup <text string>
--
-- ----------------------------------------------------------------
-- Table Names
-- ----------------------------------------------------------------
SELECT TOP 100 PERCENT
OBJ.[name]
AS 'ObjectName',
CASE OBJ.xtype
WHEN 'U' THEN 'User Table'
WHEN 'V' THEN 'User View'
WHEN 'FN' THEN 'User-Defined Scalar-Valued
Function'
WHEN 'TF' THEN 'User-Defined Table-Valued
Function'
WHEN 'P' THEN 'Stored Procedure'
ELSE OBJ.xtype
END
AS 'OjbectType',
OBJ.crdate
AS 'CreateDate'
FROM sysobjects OBJ
(NOLOCK)
WHERE OBJ.uid = 1
AND
OBJ.xtype IN ('FN','P','TF','U','V')
AND
UPPER(OBJ.name) LIKE UPPER('%'+@String+'%')
ORDER BY OBJ.[name]
-- ----------------------------------------------------------------
-- Column Names
-- ----------------------------------------------------------------
SELECT TOP 100 PERCENT
OBJ.[name]
AS 'ObjectName',
OBJ.id,
CASE OBJ.xtype
WHEN 'U' THEN 'User Table'
WHEN 'V' THEN 'User View'
WHEN 'FN' THEN 'User-Defined Scalar-Valued
Function'
WHEN 'TF' THEN 'User-Defined Table-Valued
Function'
WHEN 'P' THEN 'Stored Procedure'
ELSE OBJ.xtype
END
AS 'OjbectType',
OBJ.crdate
AS 'CreateDate',
COL.colorder AS
'ColOrder',
COALESCE(COL.name,
'No Parameter or Name') AS
'ColParamName',
CASE COL.xusertype
WHEN NULL THEN 'None'
WHEN 36 THEN 'Integer'
WHEN 48 THEN 'TinyInt'
WHEN 52 THEN 'SmallInt'
WHEN 56 THEN 'Integer'
WHEN 58 THEN 'SmallDateTime'
WHEN 60 THEN 'Money'
WHEN 61 THEN 'DateTime'
WHEN 62 THEN 'Float'
WHEN 99 THEN 'NText'
WHEN 106 THEN 'Decimal'
WHEN 108 THEN 'Numeric'
WHEN 127 THEN 'BigInt'
WHEN 165 THEN 'VarBinary'
WHEN 167 THEN 'Varchar'
WHEN 175 THEN 'Character'
WHEN 231 THEN 'NVarchar'
WHEN 239 THEN 'NCharacter'
WHEN 256 THEN 'SysName(NVarchar)'
ELSE CONVERT(varchar,xusertype)
END
AS 'ColType',
COL.length
AS 'ColLength',
COL.xprec
AS 'Precision',
COL.xscale
AS 'DecPlace'
FROM sysobjects OBJ
(NOLOCK)
LEFT JOIN syscolumns COL
(NOLOCK)
ON
OBJ.id = COL.id
WHERE UPPER(COL.name) LIKE UPPER('%'+@String+'%')
AND
OBJ.xtype <> 'S'
ORDER BY OBJ.[name]
-- end of copy
Laura Wilson
Asst. Director, Info Systems
====================================
Please do NOT send messages that ask "Please post to the list" These are useless messages that just waste the email server's resources. Instead, email the original requester and ask that they send you or post the results of their question.
To UNSUBSCRIBE, go to http://MTUSers.net
for information.
The meditech-l archives (and other tips) can be found at
http://mtusers.net/zarchives
Job opportunites in the Meditech community can be found at
http://mtusers.net/zjobs
Townsend, L. Allan
Here is something I've had for awhile and have no idea where it came from. It might help anyone trying to follow the relationships, etc. in the DR tables.
Allan
Allan Townsend
Application Specialist, Decision Support
HITS Nova Scotia
If you have received this e-mail in error or you are not the intended recipient, please:
* Do not open any attachments;
* Notify the sender by e-mail or telephone that you have received the message; and
* After notifying the sender, delete the e-mail from your system.
Thank you.
Purvins, James A.
Structure Mapping you are looking for?
Below link is for Magic 5.62.
http://www.meditech.com/prdr/Tables/5.6HTMLsEP/System.htm
James Purvins
Inland Northwest Health Services
(509) 232-8384
This e-mail and any attachments are confidential and may also be
privileged. If you are not the named recipient, or have otherwise
received this communication in error, please delete it from your inbox,
notify the sender immediately by replying to the message, and do not
disclose its contents to any other person, use the information for any
purpose or store or copy the information in any medium. Thank you for
your cooperation
Townsend, L. Allan
Here is another good link for finding out more on the relationships between tables in the DR. Click on the modules and drill down on the tables.
http://www.meditech.com/prdr/Tables/55HTMLsCS/system.htm
Allan
Allan Townsend
Application Specialist, Decision Support
HITS Nova Scotia
208 Charlotte Street
Sydney, N.S.
B1P-1C4
Phone : 902-595-3004
Cell : 902-565-6227
Fax : 902-595-3000
E-Mail : allan.t...@nshealth.ca
If you have received this e-mail in error or you are not the intended recipient, please:
* Do not open any attachments;
* Notify the sender by e-mail or telephone that you have received the message; and
* After notifying the sender, delete the e-mail from your system.
Thank you.
-----Original Message-----
From: meditech-...@mtusers.com [mailto:meditech-...@mtusers.com] On Behalf Of Laura Wilson
Sent: Monday, January 05, 2009 6:06 PM
To: medit...@mtusers.com; CSLis...@SISUnet.org
Subject: [MEDITECH-L] Data Repository Structure mapping
Laura Wilson
Asst. Director, Info Systems