February 8, 2008 at 7:57 am
Fairly soon those of us supporting the legacy product will need to learn to work with the new database, which is very highly normalized. Instead of doing reports or data corrections where one or two tables were typically involved, there will be very complex joins and foreign key constraints to deal with. Someone must have come up with ways to make this easier. Ideally the solutions will Tsql based rather than having to get off into C#
February 8, 2008 at 8:17 am
You can create views to simplify the querying.
There are other ways of dealing with this, such as indexed views, moving data to another database that is less normalized for performance, building a data warehouse, etc.
February 8, 2008 at 8:37 am
you could generate a script for view creation based on sp_pkeys or sp_fkeys
But you'll have to study the new datamodel to understand it symanticaly :doze:
for your queries to make sence.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 8, 2008 at 10:15 am
Here is a nicely laid out view that makes looking at the foreign keys a lot easier (IMHO).
You can pull all keys that touch a table as either a parent or a child.
Another thing you can do to help yourself out is make a series of database diagrams for sections of the schema.
Once you know how an area is laid out then do like Steve said and start creating yourself views.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW [dbo].vw_FOREIGN_KEYS AS
SELECT
ForeignKeys.name AS Key_Name,
Parent.name AS Parent_Table,
Parent_Columns.name AS Parent_Column,
Child.name AS Child_Table,
Child_Columns.name AS Child_Column,
OBJECTPROPERTY (sysforeignkeys.constid , 'CnstIsDisabled') AS Is_Disabled
FROM sysforeignkeys
INNER JOIN sysobjects ForeignKeys
ON sysforeignkeys.constid = ForeignKeys.id
INNER JOIN sysobjects Parent
ON sysforeignkeys.rkeyid = Parent.id
INNER JOIN sysobjects Child
ON sysforeignkeys.fkeyid = Child.id
INNER JOIN syscolumns Child_Columns
ON sysforeignkeys.fkey = Child_Columns.colid AND Child.id = Child_Columns.id
INNER JOIN syscolumns Parent_Columns
ON sysforeignkeys.rkey = Parent_Columns.colid AND Parent.id = Parent_Columns.id
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
February 8, 2008 at 10:21 am
Randy Petty (2/8/2008)
Fairly soon those of us supporting the legacy product will need to learn to work with the new database, which is very highly normalized. Instead of doing reports or data corrections where one or two tables were typically involved, there will be very complex joins and foreign key constraints to deal with. Someone must have come up with ways to make this easier. Ideally the solutions will Tsql based rather than having to get off into C#
On the reporting side: if you are planning on using Reporting services, then you should leverage Report Models which is a way to automate the linking between the tables. If you have ever used Business Objects - it's like setting up a universe (just much more basic and much more automated): it sets up a simplified layer with all of the relationships pre-defined, etc...
so - once the model is set up, you create reports simply by dragging any number of fields from any number of tables, and the report will "figure out" the relationships. Not 100% bulletproof for some scenarios (as in - those where the report wants some other type of table relationships than the one you defined), but should cover MOST circumstances.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply