Data Warehousing – Definition Plus
The data warehousing market consists of tools, technologies, and methodologies that allow for the construction, usage, management, and maintenance of the hardware and software used for a data warehouse, as well as the actual data itself.
In order to clear up some of the confusion that is rampant in the market, definitionplus provides you with fact:
Data Warehouse:
The term Data Warehouse was coined by Bill Inmon in 1990, which he defined in the following way: “A warehouse is a subject-oriented, integrated, time-variant and non-volatile collection of data in support of management’s decision making process”. He defined the terms in the sentence as follows:
Subject Oriented:
Data that gives information about a particular subject instead of about a company’s ongoing operations.
Integrated:
Data that is gathered into the data warehouse from a variety of sources and merged into a coherent whole.
Time-variant:
All data in the data warehouse is identified with a particular time period.
Non-volatile:
Data is stable in a data warehouse. More data is added but data is never removed. This enables management to gain a consistent picture of the business.
(Source: “What is a Data Warehouse?” W.H. Inmon, Prism, Volume 1, Number 1, 1995).
This definition remains reasonably accurate almost ten years later. However, a single-subject data warehouse is typically referred to as a data mart, while data warehouses are generally enterprise in scope. Also, data warehouses can be volatile. Due to the large amount of storage required for a data warehouse, (multi-terabyte data warehouses are not uncommon), only a certain number of periods of history are kept in the warehouse.
Ralph Kimball provided a much simpler definition of a data warehouse. As stated in his book, “The Data Warehouse Toolkit”, on page 310, a data warehouse is “a copy of transaction data specifically structured for query and analysis”. This definition provides less insight and depth than Mr. Inmon’s, but is no less accurate.
List of Business Intelligence (BI) tools
Business Intelligence (BI) tools are widely used for reporting, dashboarding and analysis. The following BI tools were thoroughly examined on 70 criteria considered important for high productivity and Business Intelligence systems that actually add value to your organization. In random order.
| No. | Tool | Version | Vendor |
| 1. | Oracle Enterprise BI Server | 7.8 | Oracle |
| 2. | Business Objects Enterprise | XI r2 | Business Objects (now SAP) |
| 3. | SAP NetWeaver BI | 7.0 | SAP |
| 4. | SAS Enterprise BI Server | 9.1.3 | SAS Institute |
| 5. | IBM Cognos TM/1 & Executive Viewer | 9.1 | Applix (now IBM) |
| 6. | BizzScore Suite | 7.2 | EFM Software |
| 7. | WebFocus | 7 | Information Builders |
| 8. | Excel, Performance Point, Analysis Server | 2007/2005 | Microsoft |
| 9. | QlikView | 8.5 | QlikTech |
| 10. | Microstrategy | 8 | Microstrategy |
| 11. | Hyperion System | 9 | Hyperion (now Oracle) |
| 12. | Actuate | 9.1 | Actuate |
| 13. | IBM Cognos Series 8 | 8.3 | Cognos (now IBM) |
Instances in SQL Server
The port used by default instance is always 1433 and named instances use dynamic allocation.
The total number of instances including the default instance of SQL Server supported by all editions differ. The following gives a clear idea of the supported instances for each editions.
Enterprise – 50
Developer – 50
Standard – 50
Workgroup – 16
Express – 16
Note: Windows Failover Cluster supports only 25 instances.
Add a new column to every table in a database
This seems quite a painful job when so many start opening a table and add the new column name, datatype and save. But what about you have to do this for 200 tables in a database and say you have 10 databases. Hmmm…. Such a crap work doing manually but SQL Server infamiliar stored procedure comes to rescue at this point.
The below query can be used to add a new column to every single table in a database.
Use [db]
go
exec sp_MsForEachTable ‘alter table ? add Load_date datetime null’
Thats it. You are done. Change the database name for the other databases.
So it is not just adding a column to all table, but modifying anything similar for each table in a database. You can change the SQL statement within the single quotes.
Best of luck
Become a Sponsor
This Data Awareness Programme, brings the enlightenment to every individuals in
identifying the data and analyse them. Click here to become a sponsor.
SQL Server Advanced Options
Part -II – Configurations – Management Studio and sp_configure
In my previous article you should have been familiar with the query sp_configure to see the default values of all the list
of 16 options. Let us see the Advanced options here.
By default the show advanced options will have 0 in its value. To see and set the advanced options this has to be changed
to 1 and it can be changed back to 0 once done. The query for changing the option is
sp_configure ‘show advanced options’, 1
go
reconfigure
This query will change the config_value option to 1 and reconfigure will send the command to reload all of the values. Once
the value is changed to 1 and by running sp_configure again will display an extended list of 68 in SQL Server 2008 and 70
in SQL Server 2008 R2.
I am going to take you through some of the most commonly used options.
AWE
SQL Server 2008 Tools & Components
1. Database Engine:
This is the core component which stores, manipulates & manage data. Other features include relational, XML, full text search and replication.
2. Integration Services:
Integration services is responsible for ETL Layer. ETL stands for Extraction, Transformation & Loading. This component is used for extracting the data from the source system, transform accordingly and load it into the database in the 1st component.
3. Analysis Services:
Once the data is available in 1st component via the ETL layer in the second component, not all the shcema is in ready to us and hence those are processed and managed by creating Online Analytical Processing (OLAP) analysis and data mining model for Business Intelligence purpose.
4. Reporting Services:
Whatever transformation is done in 2nd component & 3rd component we need an end user component to see the result and this is achieved as interactive, tables, graphical representations, charts through Reporting services.
The following are the available tools in SQL Server
1. SQL Server Management Studio (SSMS)
This is the management tool to manage all the core components which we saw above.
2. Business Intelligence Development Studio (BIDS)
This tool allows a developer to develop cubes (Analysis Services), integration service packages, reports.
3. SQL Server Configuration Manager
Managing the connectivity components such as Server Network Utility, Client Network Utility, Service Manager is done using this tool. All the connectivity components was separate features in 2005 version and backwards.
4. SQL Server Profiler
All the events occured in SQL Server can be traced and saved for future use using this tool.
5. Database Engine Tuning Advisor
Shortly called DTA is used too analyse the load on the engine. The recommendations provided by DTA can be used to improve the query performance for the same query.
6. Command Prompt Utilities
This is another powerful utility that can be accessed via DOS prompt. You can go to cmd.exe and execute the following commands that have its unique magic work done without the knowledge of T-SQL.
A. bcp – bulk copy of data
B. dta – Database Engine Tuning Advisor
C. dtexec – configure and execute SSIS packages
D. dtutil – Manage SSIS package
E. sqlcmd – command line to execute T-SQL statements
F. rs – Manage Reporting services server
G. rsconfig – manage & configure report server connection
H. rskeymgmt – manage encryption keys on report server
I. sqlagent90 – to start SQL Server Agent
J. SQLdiag – Diagnostic collection by Microsoft
K. sqllogship – Operational tasks like copy, restore, backup operations
L. sqlservr – start and stop the database engine for an instance
M. Ssms – to open SSMS
N. sqlps – to run PowerShell commands
O. tablediff – capable of comparing data from 2 tables in replication topology.
SQL Server Memory limitations
There are totally 6 editions available in SQL Server and 4 Windows Server with 32 & 64 bit for each in 2008 and 3 for 2003 R2 SP2.