tag:blogger.com,1999:blog-202420062012-02-16T01:42:56.915-08:00Important Things about Data Modeling, Data Warehousing, and Techniques.Scotthttp://www.blogger.com/profile/07884774285476356003noreply@blogger.comBlogger241100tag:blogger.com,1999:blog-20242006.post-2310637737992208782008-11-25T13:04:00.001-08:002008-11-25T13:07:19.269-08:00

I keep getting the need to quickly Naturalize hierachies for analysis. So here is a sample script that accomplishes this:set nocount ondeclare @Temp1 table(RecordID int Primary key NOT NULL ,ParentRecordID int,Description nvarchar(1000))/* Start loading of test data */insert into @Temp1 values(1,null,'CEO')insert into @Temp1 values(2,1,'VP Marketing')insert into @Temp1 values(3,1,'VP Operations')

Scotthttp://www.blogger.com/profile/07884774285476356003noreply@blogger.com0tag:blogger.com,1999:blog-20242006.post-40410013698625716942008-07-17T13:48:00.001-07:002008-07-17T13:59:58.659-07:00

More and more companies on the internet are providing services to other people, companies, or service providers. These companies are in thier own right "Service Providers".If you are a modeler or developer that is working for a service provider or wish to provide a new service, you may find the TMForum to be a great place to start. They provide an industry standard framework for service

Scotthttp://www.blogger.com/profile/07884774285476356003noreply@blogger.com0tag:blogger.com,1999:blog-20242006.post-29059668962711445392008-07-17T13:42:00.000-07:002008-07-17T13:45:45.552-07:00

This is very useful article that does a comparitive mapping between UML and IDEF1x for those who are ERWin users and have a need to use a UML tool such as Rational Rose or Sparx.http://www.dama-michigan.org/2%20Jerry%20Stembridge%20Feature%20Article.htm

Scotthttp://www.blogger.com/profile/07884774285476356003noreply@blogger.com0tag:blogger.com,1999:blog-20242006.post-40119049098287418442007-12-27T12:33:00.000-08:002008-01-10T09:06:31.925-08:00

ZIP File: TablePartitioningTool.zipThis experimental toolset is a result of my desire to have a simple repeatable solution for a complex problem: Table Loading and Partitioning Management for time based event data.1. Loading can be a sore spot in any ETL pipeline when it is too slow. This results in an ever-increasing backlog of import data.2. Partitioning within SQL Server 2005 is an extremely

Scotthttp://www.blogger.com/profile/07884774285476356003noreply@blogger.com0tag:blogger.com,1999:blog-20242006.post-19830520756652114662007-12-10T08:31:00.000-08:002008-02-07T21:07:51.454-08:00

ETL processes typically will run across the need to delete duplicate records. The fastest and simplist way I know of is to create a Primary Key (or unique index) on the table with the instruction to IGNORE_DUP_KEY=ON . Then when you bulk copy or insert data into the table, SQL quickly ingores the duplicates.Example: CREATE TABLE #Temp (ID INT NOT NULL)ALTER TABLE #TempADD PRIMARY KEY (ID ASC)WITH

Scotthttp://www.blogger.com/profile/07884774285476356003noreply@blogger.com1tag:blogger.com,1999:blog-20242006.post-57592595829758259502007-11-21T07:33:00.000-08:002007-11-21T07:48:53.910-08:00

Reducing I/O for a processing Pipeline is critical when processing high volume of data. By reducing your I/O you reduce the load on the network, Hard Drives, and CPU memory.The following is a research paper from Vijayshankar Raman called:“How to Wring a Table Dry: Entropy of Relations and Querying of Compressed Relations”. http://portal.acm.org/citation.cfm?id=1164201Here is Vijayshankar Raman's

Scotthttp://www.blogger.com/profile/07884774285476356003noreply@blogger.com0tag:blogger.com,1999:blog-20242006.post-65836630061983953172007-11-20T22:20:00.000-08:002007-11-22T11:32:08.631-08:00

To clarify some thoughts and clear up some confusion, here is some definitions...Definition: Meta DataInformation about information. Basically its documentation about information used or contained by various systems. Example: Data Dictionaries & Data Model Diagrams.This is on excellent thing to do at the beginning of a project, no matter how small a project it is. See my thoughts on data modeling

Scotthttp://www.blogger.com/profile/07884774285476356003noreply@blogger.com0tag:blogger.com,1999:blog-20242006.post-18486238571693270902007-11-05T15:04:00.000-08:002007-11-07T13:02:02.343-08:00

The above link provides the following:1. DBCodeGeneration.zip Description: You will find that 80% of all stored procedures in a transaction database are all very predictable and can be auto generated. Using a code generator will save you time and create symmetry within your database which increases predictability and stability for the overall system. This will allow you to focus on the other

Scotthttp://www.blogger.com/profile/07884774285476356003noreply@blogger.com2tag:blogger.com,1999:blog-20242006.post-52069575763567663182007-08-14T15:19:00.000-07:002007-08-21T11:52:15.911-07:00

The above link provides the following:1. DBAuditTrackingTool.zip Description: This tool audits your database tables without changing the table orginal table schema. Tracks what, when, and who changed a row within a table. Each table has a corrisponding history table and trigger to audit a table.Feature List:1. Track all who, what, and when changes have been made to any table.2. Auto Generates the

Scotthttp://www.blogger.com/profile/07884774285476356003noreply@blogger.com0tag:blogger.com,1999:blog-20242006.post-38460133249902336242007-08-01T13:50:00.000-07:002007-11-21T07:49:43.423-08:00

Do you wish to compress your data without having to decompress it to read it? Most people will think of RAR or ZIP compression when they need to save space on the hard drive. This may save storage and I/O load, but the side effect of this approach will require you to decompress the file every time you need to access it. The following is a means to compress your data without requiring

Scotthttp://www.blogger.com/profile/07884774285476356003noreply@blogger.com0tag:blogger.com,1999:blog-20242006.post-9420610593648153072006-12-18T18:45:00.000-08:002007-11-05T19:09:24.115-08:00

This is a how to do sessionizing using SQL with just a set of events. We use alot of self-joins to accomplish this task.Steps:Determine your session timeout length.Order events by date. Identify the Exit Point Event and the duration between events. The Exit Point is the event that has a duration greater than the session timeout length. Finally you can identify the sessionID and assign a sequence

Scotthttp://www.blogger.com/profile/07884774285476356003noreply@blogger.com4tag:blogger.com,1999:blog-20242006.post-1162420280346999202006-11-01T14:14:00.000-08:002006-11-02T08:48:45.923-08:00

Now I know I stated in previous posts that I'm not really excited about Meta models. But there is a place for them. They can be used sparingly here-and-there where the business area is blurred or when you need to store system configuration! Yep. A system can have many services that need to be controlled by a central database. A meta model fits the bill.Having a database schema that is flexibable

Scotthttp://www.blogger.com/profile/07884774285476356003noreply@blogger.com1tag:blogger.com,1999:blog-20242006.post-1143397248059393752006-03-26T10:18:00.000-08:002006-03-26T10:50:30.433-08:00

Not everyone can be proficient at creating and comprehending data models. Modeling is a very abstract concept with its own unique hieroglyphic language. So to make it easy on everyone, why not allow people to experience the model as you design it.How? 1. As your defining the logical data model, stop each week and create a physical model based on the logical model designed so far2. Create the data

Scotthttp://www.blogger.com/profile/07884774285476356003noreply@blogger.com2tag:blogger.com,1999:blog-20242006.post-1143176024075371052006-03-23T20:29:00.000-08:002006-03-24T14:41:33.873-08:00

The following design pattern I’ve known for several years and have implemented it to great success. The subtles are what will save you: 1. Having an explicit ExpirationDate will save you having to look up what an initial contract’s end date is when you imply that the Start date of the replacing contract is the end of initial contract (When in doubt, be explicit not implicit); 2. You can

Scotthttp://www.blogger.com/profile/07884774285476356003noreply@blogger.com1tag:blogger.com,1999:blog-20242006.post-1143002010016312372006-03-21T20:03:00.000-08:002006-03-21T22:02:19.476-08:00

Here are some strategic questions to ask key business personnel to help accurately model how the business uniquely defines its information.Entity Questions:1. What is the list of Items (or Entities) that you are tracking or would like to track?2. How do you uniquely identify each {Entity Name Here} ?3. Do you manually assign identification to each {Entity Name Here}?4. Can the unique identifier

Scotthttp://www.blogger.com/profile/07884774285476356003noreply@blogger.com1tag:blogger.com,1999:blog-20242006.post-1142476320989248212006-03-15T18:19:00.000-08:002006-03-16T12:43:16.900-08:00

How do you track all changes for a table within a database? Easy. Build a corrisponding history table that mirrors the table in question with the addition of a datetime column to the primary key and an action column to indentify the action that got this item into the history table (Insert, Update, Delete). The history table is maintained by a trigger on the master table.Example:Person -----------

Scotthttp://www.blogger.com/profile/07884774285476356003noreply@blogger.com1tag:blogger.com,1999:blog-20242006.post-1142104828268527152006-03-11T11:00:00.000-08:002006-03-11T11:38:40.010-08:00

Data Modeling In GeneralI found it worthy to note that the purpose of creating a normalized logical data model is to accurately document the business entities and relationships between them in a detailed model. The value of this logical model is two fold: One is for the business owners to have detailed comprehension of their own business information; Second is to transfer this business knowledge

Scotthttp://www.blogger.com/profile/07884774285476356003noreply@blogger.com1tag:blogger.com,1999:blog-20242006.post-1140577605272664292006-02-21T18:51:00.000-08:002008-01-13T21:23:04.905-08:00

Partitioning splits data across multiple tables, databases, and/or database servers. There are two types; vertical and horizontal partitioning. Horizontal partitioning divides data across mutiple tables based on rows that fall within the appropriate partition rules. Each partition has the same columns, but has its own partition rule (Example: Between 01/01/2005-02/01/2005). Veritical partitioning

Scotthttp://www.blogger.com/profile/07884774285476356003noreply@blogger.com0tag:blogger.com,1999:blog-20242006.post-1139802909364565792006-02-12T19:48:00.000-08:002006-11-15T13:29:18.486-08:00

Reporting on hierarchal tables (Child/Parent relationship) tables can be a huge time bottleneck when using an iterative cursor process. Here is a speedy way to look up hierarchal information.Original Hierarchal TableTraditionally hierarchal tables self reference its own primary key and giving it a parent role name. Example:Person PersonID (PK) , Name (Attributes) , ParentPersonID (Self Ref.

Scotthttp://www.blogger.com/profile/07884774285476356003noreply@blogger.com0tag:blogger.com,1999:blog-20242006.post-1139016213689827352006-02-03T17:07:00.000-08:002006-03-11T15:24:50.230-08:00

I’m a firm believer in creating symmetry through design patterns where it is applicable. Using good modeling techniques and naming conventions on data and process definitions will enable easier code generation. 80% of an application can be code generated based on templates that enforce design patterns. The remaining 20% may need to vary a little from the standards when it is too “costly” to do

Scotthttp://www.blogger.com/profile/07884774285476356003noreply@blogger.com8tag:blogger.com,1999:blog-20242006.post-1135800105035966642005-12-28T11:28:00.000-08:002005-12-28T12:01:45.043-08:00

If you want to successfully mine useful data from your web logs, you should carefully instrument your websites. Garbage in, Garbage out. Parameters in the URL can be easily mined, but they should be standardized across all your websites or properties. This will simplify the Business and Customer Intelligence mining process and increase accuracy. Somethings to Track:1. Page Views2. User

Scotthttp://www.blogger.com/profile/07884774285476356003noreply@blogger.com0tag:blogger.com,1999:blog-20242006.post-1135797413653516512005-12-28T10:53:00.000-08:002006-02-04T14:14:17.773-08:00

The following is a list of common concepts that are commonly found within a data warehouse or transaction database.Some things to think about:1. Table Names a. Domain Tablesb. Domension Tablesc. Rule Tablesd. Fact Tablese. Index Tablesf. Log or Change History Tablesg. General Table2. Table Column Names a. ID, SKb. Code, Name, Descriptionc. Binary Indicators/Flagsd. Ranges: Start and Ende.

Scotthttp://www.blogger.com/profile/07884774285476356003noreply@blogger.com0tag:blogger.com,1999:blog-20242006.post-1135723971478606642005-12-27T14:43:00.000-08:002005-12-27T14:53:35.913-08:00

Arguments I use for people that over use meta-data Models. (Mostly by over zealous C++, C#, Java Developers, UI developers, and beginning data modelers)You May Not Know Your BusinessThe purpose of Data Modeling, in short, is to accurately define your business and relationships between business concepts in great detail. Meta Modeling your whole business sends a red flag which states that you may

Scotthttp://www.blogger.com/profile/07884774285476356003noreply@blogger.com0tag:blogger.com,1999:blog-20242006.post-1135723253984781572005-12-27T14:04:00.000-08:002005-12-27T14:41:46.816-08:00

From my personal experience in database development over the 15 years of my career, I have seen many businesses suffer from information glut without a means to quickly and cheaply mine their data. A single business can lose Terabytes of data every day without ever relinquish its secrets and benefiting its owner with its hidden treasures.Businesses may or may not even know what they are looking

Scotthttp://www.blogger.com/profile/07884774285476356003noreply@blogger.com0