Data Mart, Data modeling & Business Intelligence by Datamart.org

It is common to upload/append new data into existing sql server table. Problem could arise when data is manually entered into any other formats like excel or access and fresh data from these formats has to be added to previously added data into Sql server. The major reason of problem is because previously appended was cleaned before it was appended into Sql Server table. We can either replace sql server table with new and updated data, but replacing may increase the work because replaced data may contain some errors.

Another option is We can also just take the new rows which have been added manually in different file formats. There can also complications in this method because of unique identity constraints if new rows have a duplicate id. The solution to resolve the unique id constrains is to have Excel or Access or other format table where the data is being entered should kept intact. We periodically upload new rows into Sql Server through DTS. The main advantage could be we have to clean only the new rows if required.

Stars, Snow Flakes, Fact Tables Schema for multidimensional Databases. 

Star Schema: 

The most common in which Data Warehouse contains 1 large central Fact Table containing bulk amount of data with no redundancy and set of smaller  dimension tables, 1 for each dimension like Time, Geographic etc. 

Snow Flake 

            It is the substitution of Star Schema where some dimension tables are normalized by splitting tables into additional tables. Major difference between Star and Snow Flakes is that dimension tables of snow flakes may be kept in normalized to reduce redundancies for example location dimension can be further categorized by city dimension. 

Fact Constellation/Grouping: 

Sophisticated application may require multiple fact tables to share dimensions. This kind can be viewed as a collection of stars and hence a Galaxy schema is formed.  For example we facts table for sale and shipping both can use Location dimension.

Data Mining can be called as knowledge discovery consist of following steps;
Data Cleaning
Data cleaning mean removing inconsistent data, verify that data is correct and according certain rules.
for example column grades may have A,B,C.

Data Selection;
Relevant data is extracted from database. i.e If we are working on marketing research we probably require data from customer tables, products and orders.

Data Integration
Data Integration means integrating/combining data from various sources for example workers overtime can be integrating in Database in their respective accounts.

Data Transformation
converts data from a source data format into destination data.

Data Mining
Data mining helps in finding valuable knowledge and high level information which can then be browsed from different angles and the facilitate informed decision making in business, information management and all other fields where it is being applied.

For this example, we want to name the range of cells that include the customer and addresses .

1. First highlight the cells
2. Now click inside the box on your toolbar at the top of your screen left hand side just below the file and edit menue.

3. When you click inside this box, you see the cell name on which the curser is highlight.

4- While it is highlighted, type Customers then hit your Enter key.

NOTE: You must hit the Enter key when you are finished typing the name so it is accepted.
5. There are some rules about range names:
1. Names can not have spaces in them
2. Names can not be the same as a cell reference.
3. Names can not begin with a number.
4. Names can not use certain special characters. 5. I rules not followed you will get an error message and will have to change it.

Creating a Bank like Report in SQL

Frequently we have to create a report in any reporting tools like Crystal Reports, Sq Server Reporting Server or MS Access which should give us account detail between 2 dates, as well as previous balance as of beginning date of the report, for example we an account summary between 2nd-Jan-2008 and 29-Jan-2008 it imperative to have the previous balance as of 2nd-January-2008 at the top of the report and then this balance must be deducted from the detail balance to calculate the Net balance.

This can be done utilizing sub query in Stored procedure as follows ;

ALTER procedure preiosubalance2
(@enterAccountid int, @enterbegdate datetime, @enterenddate datetime)
as
Select accountid, debit, credit, transactiondate, (select sum(Debit)from
dbo.PrimeTransactions as sumd
where TransactionDate <= @enterbegdate and accountid = @enterAccountid) as sumb
from dbo.PrimeTransactions where accountid = @enterAccountid
and transactiondate between @enterbegdate and @enterenddate

In the above code 3 variable are defined for Account ID, Beginning date, Ending Date. Our statement containing a sub query is also called an outer query or outer select, contains details for the Account in question, A sub query is also called an inner query or inner select, will calculate the balance as of the Beginning date. If we run the query we will have result as showing Debit and Credit Details and Balance as of 3/3/2003.

preiosubalance2
acid dr cr tr-date Balance
1 0 14574 3/3/2003 11942
1 11942 0 3/3/2003 11942
1 0 11942 3/4/2003 11942
1 7204 0 3/4/2003 11942
1 0 7204 3/5/2003 11942
1 5425 0 3/5/2003 11942
1 0 5425 3/6/2003 11942
1 4247 0 3/6/2003 11942
1 0 4247 3/7/2003 11942
1 2686 0 3/7/2003 11942
1 0 2686 3/8/2003 11942

Data Transformation; Data Transformation is process of converting data from source to target as per requirements of the design and business rules.

Types of Data Transformations 

Data Scrubbing 

Data Scrubbing is a process to correct deficiencies in the Data and is a part of transformation process as follows; 

Mapping 

Mapping is straight transfer of source field directly into the target database. If the source Database system has clean data, it will expedite the straight data mapping. Also includes in Mapping is data from multiple representations to single representation such as common formats for residential address such phone numbers, Student Grades A,B,C,D etc. 

Concatenation 

Concatenation is the process of adding 2 or more fields together to form a single field., i.e  Category and product to be concatenated into 1 columns. 

Separation 

Separation of a single field into two different fields, this commonly applied to source systems information like address, Customer name are stored in 1 field.

 “When you build a data warehouse you must make a decision about the granularity [The level of detail] of the data you want to present. In most cases you want to record your activity bases on each transaction. In other cases you may choose to use a daily level of granularity In case of time dimension if you have day as the finest level, users will not be able to drill down to transactions by using time dimension any further than a day level. If you choose hour level users will be able to analyze trends at an hourly level. “If a Time Dimension row is defined as representing a single day, then the associated fact row either denotes an event that happened on that given day, or it has active totals for that given day”

Before going into further details it will be worth describing the difference between Data warehouse and data mart. Information requirements of management at level should be the basis for preparing the Data mart. By creating data marts makes it easier to manage data warehouse projects, as after all when combing all these data marts they become a data warehouse of an organization.

In the Data warehouse it is required to have all the organizational information from all functional areas at tactical level and building data warehouse requires a vast amount of time and resources. By creating data marts information is collected from each functional areas. Data mart represent the individual functional areas of the business such as Marketing, Human resource, Sales, Finance and Accounting. In general complete data warehouse is based on the functional data marts.

Basically Data warehouse is to store data in a manner to facilitate Managers makes meaning full decision making, by integrating data from multiple transactional systems. Data ware housing is aimed at timely delivery of right information to right people and is a continuous process.

The transactional data is stored in relational database system in order to sore relevant data up to most granular level, and is designed to store as fastest as possible.

This is a review about books that were originally written in 1999 and 2001. My reveiew is still present on Amazon. This is about my great appreciation for Noel Jerke’s books on ASP and Sql server named as ” E-commerce Developer’ guide to Building Community and promotional Tools. & “Visual Basic Developer’s Guide to E-Commerce with ASP and SQL Server” are after both books more than five years still feels updated from many recent book on the same topics. Although technologies have been changed but that is from Technology point of view not from functionality point of view.

Although title says developer’s guide but it makes a reader from novice to developer. Before that e-commerce was complicated,difficult even reading other books makes you confuse more about the subject but after reading this whole concept becomes clear. In my opinion Only prerequisite for reading this book is some databases experience, it will accelerate if reader have experience in Access, Sql server. The 99 percent Use of stored procedure is one of the greatest thing by Noel. I have built my web site base on concept and examples given in this book. Though there might be some minor errors but I think they make you learn and eventually they make you master the vb script, stored procedure and concept.
I congratulate Noel and request him to upgrade this book to Asp.net and vb.net as well as other of his book named e-commerce developer’s guide to building community tools.

Thanks Noel

In 1996 to 2000 I worked in financial department of a company and I started storing its data in Lotus and excel sheets for quick calculation in preparing the monthly / yearly reporting. Most of the data was stored in papers, and computers were used for letter writing, presenting the manual reports by typing these into spreadsheet programs.

There were customized software vendors who provide computerized accounting services. Any changes in ready made software required sizable amount of the money to be spent again and then Company executive were afraid that if in case of any problem in software or computer all the data might be lost and hence that didn’t solved the problem.

In addition to back ground in accounting and Finance, I took database courses like Fox pro as well as thoroughly studies the Microsoft manual for Access as well as some database books . Fox pro and MS Access triggered new thought process of organizing Data into RDBS Format and creating the reports on ad hoc basis and made my life lot easier.

Most important step we took was to start storing the data RDBMS format, as well as existing in flat files like Lotus and excel was imported into Ms access in first stage. This data was then utilized to prepare ad hoc reports by using Sql Language. Also we starting following the other software like Dac Easy, Peach Tree in creating variety of reports for inventory, General ledgers concept, Control Accounts and following the accounting standards and customized reporting.
Step by step eventually all areas were transformed into RDBMS format for storage purposes and the same Database was later on upgraded to Sql Server 7/2000.

Microsoft provided seamless integration between the MS Access 2000 and Sql server 7. The continues improvement process successfuly integrated all the functional areas like Finance, Production, Marketing and Human Resources and flow of information was streamlined. Employees of the company were keen to learn and part of this continues process as expertise in understanding the system as directly related to pay raise. It is now understandable that Information technology department runs not on awareness of technical terms, but it runs based on how user friendly and simply managers get their information for making day to day business decision making.