Ok, it’s been a really long time since I posted, well over a month. Work commitments have taken a bit more of my time this month. If you missed it this may have been the biggest month for SQL Server since RTM release.
April saw the release of Service Pack 1 for SQL Server 2005 and Microsoft making another tool acquisition in the BI space.
SP1 was the most interactive service pack I have seen. The CTP access allowed SQL users to work with the beta product and provide feed back to MS on any possible issues. Creating what should be a solid service pack.
If you have not downloaded SP1 yet check out: Download SQL Server 2005 SP1
Saturday, April 29, 2006
Friday, March 24, 2006
A list of the bugs that have been fixed in SQL Server 2005 Service Pack 1 - Community Technology Preview (CTP) March 2006
I came across an interesting KB post on Microsoft support today. A complete list of bugs fixed in SQL Server 2005 Service Pack 1 CTP. There is a list of 19 fixes at the time of this post. This CTP service pack fixes a fair amount of Analysis services issues. The list will be updated as Microsoft document other fixes.
Check out: A list of the bugs that have been fixed in SQL Server 2005 Service Pack 1 CTP March 2006
Check out: A list of the bugs that have been fixed in SQL Server 2005 Service Pack 1 CTP March 2006
If you have not downloaded SQL Server 2005 Service Pack 1 CTP check out:
Sunday, March 19, 2006
Microsoft SQL Server: SQL Server 2005 SP1 CTP
Good News,
The service pack 1 for SQL Server 2005 has now gone public. If you have been waiting for Database mirroring then this is the service pack you should be testing.
Also Books online has been updated for this CTP. The services pack has been in private beta for a while now and it’s great to see Microsoft following through with the same level of beta testing as was done for SQL 2005.
Remember the CTP is not a supported release and is for testing purposes only. So the same rules apply to the use of any beta product. Bearing that in mind I would get into testing the CTP soon as possible
Check out: Microsoft SQL Server: SQL Server 2005 SP1 CTP
The service pack 1 for SQL Server 2005 has now gone public. If you have been waiting for Database mirroring then this is the service pack you should be testing.
Also Books online has been updated for this CTP. The services pack has been in private beta for a while now and it’s great to see Microsoft following through with the same level of beta testing as was done for SQL 2005.
Remember the CTP is not a supported release and is for testing purposes only. So the same rules apply to the use of any beta product. Bearing that in mind I would get into testing the CTP soon as possible
Check out: Microsoft SQL Server: SQL Server 2005 SP1 CTP
Wednesday, March 15, 2006
SQL Server SSIS Sample Component: UnpackDecimal
Microsoft have just release another SSIS Sample Component for Packed Decimals.
Packed Decimals are generally converted using the OLE DB provider.
SQL Server SSIS Sample Component: UnpackDecimal
UnpackDecimal takes an input column formatted in packed decimal (comp-3), and generates the corresponding Decimal value.
Download details: SQL Server SSIS Sample Component: UnpackDecimal
Packed Decimals are generally converted using the OLE DB provider.
SQL Server SSIS Sample Component: UnpackDecimal
UnpackDecimal takes an input column formatted in packed decimal (comp-3), and generates the corresponding Decimal value.
Download details: SQL Server SSIS Sample Component: UnpackDecimal
Monday, March 13, 2006
Beginners Guide to Business Intelligence – What is a Data Warehouse?
This is the second instalment of my blog feature on Beginners Guide to Business Intelligence. This time I am looking at: What is a Data Warehouse exactly?
Business Intelligence Context
First Business Intelligence in my opinion is not just architecture. It is a strategy of information within an organization. All organizations report on the performance of their business processes, services and sales. How they manage and organize information is maintained by a Business Intelligence Architecture, which is made up of several components. A data warehouse is just one such component. The data warehouse is sometimes seen as the corporate repository.
Before we get into the detail of data warehouse definitions lets have a flash back for a bit of data warehouse history.
Birth of Data warehousing
In a sense, companies have been reporting on information since the dawn of computing systems. Data warehousing is really a collection of technologies that have evolved within information systems. Listed below are some of the legacy terms that you may come across when talking about data warehouses.
Executive Information System (EIS)
One of the first attempts at giving executive managers a high level view of business activities, this kind of system was widely adopted in the late 70s and early 80s. Usually these systems would be summary tables built on top of transactional tables. EIS focused solely on the financial view of the organisation with limited information other than budget vs. actual. The tables would be maintained within the source system and loaded once a month and reports would be generated on top of them, almost always printed on line-flow paper.
Management Information System (MIS)
An extension of EIS, the MIS gave business users a wider view of information in the form of Balance Scorecards and other summarised views of information. The term Key Performance Indicators (KPI) was widely adopted for measures that were just not financial based. For the first time summary tables would be maintained independently of the transactional source system. This kind of system was widely adopted in the late 80’s early 90’s. In fact entire departments were named after MIS systems, hence the term MIS Department.
Decision Support Systems (DSS)
The next evolutionary step was the creation of the DSS. A term widely used when discussing OLAP solutions. In the 90’s you were more than likely building a DSS system to support OLAP reporting. This was the era of creating reporting systems for areas of the business no longer just focusing on Financial and Sales reporting. It has to be noted that if is was not for products like Essbase, Cognos and front-end tools like Lotus 1-2-3 and Microsoft Excel; DSS systems may not have been so successful. It was the first time that information was delivered to operational roles outside of transactional systems.
Data Warehouse
The data warehouse really is a central repository combining all of these earlier systems. The early data warehouses were third normal form 3NF databases, taking incredible amounts of time to develop. Due to the large nature of early data warehouse, data integration was heavily used in the creation of data warehouses, requiring special ETL (Extract, Transform, and Load) tools and skills - even worse to develop and create reports against. At this time two distinct trains of thought appeared when it came to defining what a data warehouse should be.
The first general definition was that the existing third normal form system, was the only solution to the eternal business questions that a drive a company. Bill Inmon was the main proponent of this concept stating that data warehouse architects could never predict all possible business questions just using summary tables alone. A data warehouse by its very definition should store data at the lowest level possible providing an Ad-hoc query environment. This created the need to store large amounts of data in a structure, which could never be fully tuned for all possible queries.
Many organisations had invested heavily in a data warehouse without fully realizing all the potential benefits. The original goals of data warehouse systems were to create an environment with the easy access, and usability too large amounts of data. Early data warehouses were too complex in nature and as a result fell well short of this goal.
Something had to change.
That happened to be Ralph Kimball and the concept of de-normalisation. Ralph proposed an ingenious way of data retrieval using existing RBMS systems. The main idea was to create low level transactional (facts) tables with attribute (Dimensions) tables relating to each transaction using an integer surrogate key system.
This allowed the database optimizers to summarize large amounts of data more efficiently. Due to the reduction in the number of tables required for a similar query in a traditional data warehouse. This definition describes the Star schema concept from a high-level technical view. Besides the query performance improvement there was a huge business benefit, for the first time business users had a view of data from an information context.
Another benefit of Star schemas was the business context or subject area. Business users were presented a view of transactional data from a business context. Instead of having to join multiple tables with complex joins to produce a query that only DBAs understood. Users where able to write queries that answered business questions for example, how many products did the company sell for this year compared to last year?
Third Normal Form vs. Star schemas
Kimball vs. Inmon is the most common discussion between Data Warehouse Architects, next to which database engine is the best for data warehousing, It maybe one of the few arguments that will never truly die down.
Proponents of 3NF push the robust nature of data warehouses as the corporate repository/information factory that will move with the business and insulate against change with in source systems.
On the other hand Star schemas provide a high performance user friendly reporting environment, which is the ultimate goal of any data warehouse. Even within the star schema approach there are differing views on whether the star should be snowflake (Relational dimensions with de-normalized fact table) or just solely the de-normalized.
The Next Step: Data Marts
Data warehouses became victims of their own success. Organisations started to realize the benefits but could not wait necessarily for a large system to be put in place, hence the data mart.
Although data warehouses were and still are a successful approach to the corporate data repository, the length of time, cost and complexity to develop and deliver data warehouse projects reduce some of the benefits. This was due to the fact that most organizations would change during the course of long projects.
A data mart tends not to suffer from such issues. As the data mart is a focused repository on one area of the business. Organisations are able to see a quick turn around and delivery of a high value repository that a data mart has to offer.
The most common form of a data warehouse will more than likely be a collection of data marts. This is sometimes called a federated data warehouse. Often this is the most successful form of data warehousing.
No matter what kind of system you are building the goals always the same: the delivery of information. Ultimately this kind of system undertaking should always support the business decision making process.
References
Want to know more about Data Warehouses? Check out the following references:
Bill Inmon http://www.inmongif.com/
Ralph Kimball http://www.ralphkimball.com/
Great Resources and Articles about Data Warehousing:
http://www.intelligententerprise.com/
http://www.DMReview.com/
Beginners Guide to Business Intelligence: What is Business Intelligence About?
What is Business Intelligence About?
Business Intelligence Context
First Business Intelligence in my opinion is not just architecture. It is a strategy of information within an organization. All organizations report on the performance of their business processes, services and sales. How they manage and organize information is maintained by a Business Intelligence Architecture, which is made up of several components. A data warehouse is just one such component. The data warehouse is sometimes seen as the corporate repository.
Before we get into the detail of data warehouse definitions lets have a flash back for a bit of data warehouse history.
Birth of Data warehousing
In a sense, companies have been reporting on information since the dawn of computing systems. Data warehousing is really a collection of technologies that have evolved within information systems. Listed below are some of the legacy terms that you may come across when talking about data warehouses.
Executive Information System (EIS)
One of the first attempts at giving executive managers a high level view of business activities, this kind of system was widely adopted in the late 70s and early 80s. Usually these systems would be summary tables built on top of transactional tables. EIS focused solely on the financial view of the organisation with limited information other than budget vs. actual. The tables would be maintained within the source system and loaded once a month and reports would be generated on top of them, almost always printed on line-flow paper.
Management Information System (MIS)
An extension of EIS, the MIS gave business users a wider view of information in the form of Balance Scorecards and other summarised views of information. The term Key Performance Indicators (KPI) was widely adopted for measures that were just not financial based. For the first time summary tables would be maintained independently of the transactional source system. This kind of system was widely adopted in the late 80’s early 90’s. In fact entire departments were named after MIS systems, hence the term MIS Department.
Decision Support Systems (DSS)
The next evolutionary step was the creation of the DSS. A term widely used when discussing OLAP solutions. In the 90’s you were more than likely building a DSS system to support OLAP reporting. This was the era of creating reporting systems for areas of the business no longer just focusing on Financial and Sales reporting. It has to be noted that if is was not for products like Essbase, Cognos and front-end tools like Lotus 1-2-3 and Microsoft Excel; DSS systems may not have been so successful. It was the first time that information was delivered to operational roles outside of transactional systems.
Data Warehouse
The data warehouse really is a central repository combining all of these earlier systems. The early data warehouses were third normal form 3NF databases, taking incredible amounts of time to develop. Due to the large nature of early data warehouse, data integration was heavily used in the creation of data warehouses, requiring special ETL (Extract, Transform, and Load) tools and skills - even worse to develop and create reports against. At this time two distinct trains of thought appeared when it came to defining what a data warehouse should be.
The first general definition was that the existing third normal form system, was the only solution to the eternal business questions that a drive a company. Bill Inmon was the main proponent of this concept stating that data warehouse architects could never predict all possible business questions just using summary tables alone. A data warehouse by its very definition should store data at the lowest level possible providing an Ad-hoc query environment. This created the need to store large amounts of data in a structure, which could never be fully tuned for all possible queries.
Many organisations had invested heavily in a data warehouse without fully realizing all the potential benefits. The original goals of data warehouse systems were to create an environment with the easy access, and usability too large amounts of data. Early data warehouses were too complex in nature and as a result fell well short of this goal.
Something had to change.
That happened to be Ralph Kimball and the concept of de-normalisation. Ralph proposed an ingenious way of data retrieval using existing RBMS systems. The main idea was to create low level transactional (facts) tables with attribute (Dimensions) tables relating to each transaction using an integer surrogate key system.
This allowed the database optimizers to summarize large amounts of data more efficiently. Due to the reduction in the number of tables required for a similar query in a traditional data warehouse. This definition describes the Star schema concept from a high-level technical view. Besides the query performance improvement there was a huge business benefit, for the first time business users had a view of data from an information context.
Another benefit of Star schemas was the business context or subject area. Business users were presented a view of transactional data from a business context. Instead of having to join multiple tables with complex joins to produce a query that only DBAs understood. Users where able to write queries that answered business questions for example, how many products did the company sell for this year compared to last year?
Third Normal Form vs. Star schemas
Kimball vs. Inmon is the most common discussion between Data Warehouse Architects, next to which database engine is the best for data warehousing, It maybe one of the few arguments that will never truly die down.
Proponents of 3NF push the robust nature of data warehouses as the corporate repository/information factory that will move with the business and insulate against change with in source systems.
On the other hand Star schemas provide a high performance user friendly reporting environment, which is the ultimate goal of any data warehouse. Even within the star schema approach there are differing views on whether the star should be snowflake (Relational dimensions with de-normalized fact table) or just solely the de-normalized.
The Next Step: Data Marts
Data warehouses became victims of their own success. Organisations started to realize the benefits but could not wait necessarily for a large system to be put in place, hence the data mart.
Although data warehouses were and still are a successful approach to the corporate data repository, the length of time, cost and complexity to develop and deliver data warehouse projects reduce some of the benefits. This was due to the fact that most organizations would change during the course of long projects.
A data mart tends not to suffer from such issues. As the data mart is a focused repository on one area of the business. Organisations are able to see a quick turn around and delivery of a high value repository that a data mart has to offer.
The most common form of a data warehouse will more than likely be a collection of data marts. This is sometimes called a federated data warehouse. Often this is the most successful form of data warehousing.
No matter what kind of system you are building the goals always the same: the delivery of information. Ultimately this kind of system undertaking should always support the business decision making process.
References
Want to know more about Data Warehouses? Check out the following references:
Bill Inmon http://www.inmongif.com/
Ralph Kimball http://www.ralphkimball.com/
Great Resources and Articles about Data Warehousing:
http://www.intelligententerprise.com/
http://www.DMReview.com/
Beginners Guide to Business Intelligence: What is Business Intelligence About?
What is Business Intelligence About?
Monday, February 06, 2006
SSIS Sample Components
Well is seems the flow of add-ins for SSIS is starting. Microsoft has just release a lot of usefuls add-ins for SSIS. I am a big fan of the Regex component as it is often the hardest to code. Check out the links below.
SQL Server SSIS Sample Component: CalendarTransform
CalendarTransform is an SSIS dataflow transform component that generates standard calendar attributes.
SQL Server SSIS Sample Component: UnDoubleOut
UnDoubleOut is an SSIS dataflow component that removes qualifiers from quoted text, either in place, or via the creation of a new output column.
SQL Server SSIS Sample Component: Regex
Regex is an SSIS dataflow component that applies a configured regular expression against an incoming column, matching, extracting, or splitting, as configured by the user.
SQL Server SSIS Sample Component: UnpackDecimal
UnpackDecimal takes an input column formatted in packed decimal (comp-3), and generates the corresponding Decimal value.
SQL Server SSIS Sample Component: RTrimPlus
RTrimPlus takes a string or unicode column, and removes trailing spaces, whether ASCII, or Japanese.
SQL Server SSIS Sample Component: SeeBuffer
SeeBuffer is an SSIS dataflow component that sits in a data flow and is provided a look at each buffer that is presented to it.
SQL Server SSIS Sample Component: NullDetector
NullDetector is an SSIS dataflow component that sits astride a data flow, and, depending on whether the value of a user-indicated column is null or not, routes rows to one or the other of its outputs.
SQL Server SSIS Sample Component: CodePageConvert
CodePageConvert is an SSIS dataflow component that translates from and to any code page or unicode character representations.
SQL Server SSIS Sample Component: ConfigureUnDouble
ConfigureUnDouble takes a text column, and, removes bracketing quotes if present, plus places double quotes inside the text with sinqle quotes.
SQL Server SSIS Sample Component: UnDouble
UnDouble takes a text column, and, removes bracketing quotes if present, plus replaces double quotes inside the text with sinqle quotes.
SQL Server SSIS Sample Component: CalendarTransform
CalendarTransform is an SSIS dataflow transform component that generates standard calendar attributes.
SQL Server SSIS Sample Component: UnDoubleOut
UnDoubleOut is an SSIS dataflow component that removes qualifiers from quoted text, either in place, or via the creation of a new output column.
SQL Server SSIS Sample Component: Regex
Regex is an SSIS dataflow component that applies a configured regular expression against an incoming column, matching, extracting, or splitting, as configured by the user.
SQL Server SSIS Sample Component: UnpackDecimal
UnpackDecimal takes an input column formatted in packed decimal (comp-3), and generates the corresponding Decimal value.
SQL Server SSIS Sample Component: RTrimPlus
RTrimPlus takes a string or unicode column, and removes trailing spaces, whether ASCII, or Japanese.
SQL Server SSIS Sample Component: SeeBuffer
SeeBuffer is an SSIS dataflow component that sits in a data flow and is provided a look at each buffer that is presented to it.
SQL Server SSIS Sample Component: NullDetector
NullDetector is an SSIS dataflow component that sits astride a data flow, and, depending on whether the value of a user-indicated column is null or not, routes rows to one or the other of its outputs.
SQL Server SSIS Sample Component: CodePageConvert
CodePageConvert is an SSIS dataflow component that translates from and to any code page or unicode character representations.
SQL Server SSIS Sample Component: ConfigureUnDouble
ConfigureUnDouble takes a text column, and, removes bracketing quotes if present, plus places double quotes inside the text with sinqle quotes.
SQL Server SSIS Sample Component: UnDouble
UnDouble takes a text column, and, removes bracketing quotes if present, plus replaces double quotes inside the text with sinqle quotes.
Saturday, January 28, 2006
This Months Book Recomendation
Every time someone asks me about getting into BI or learning about data warehousing I point them to one book: The Data Warehouse Tool Kit first Edition by Ralph Kimball. When it comes to a starting point for Data Warehousing this is the book.
It’s the only book I know of that starts at the beginning and asks the questions of why you would want to embark on a Data warehouse project. Ralph uses plain language to describe star schemas and data warehouse design. Each chapter takes a real world industry example such as telecoms, retail, banking and Insurance and demonstrates star schema design. Each chapter takes on different design problems that you may face when building a star schemas.
The first Edition contains a CD with sample stars populated based on each chapter. The later editions do not come with the CD.
It’s a great book for learning about design and understanding the concepts of data warehouses and star schemas. Even if you are not a developer this book is still for you. It makes the connection between business problems and data warehouse design. The great thing about this book is its technology independent.
It’s the only book I know of that starts at the beginning and asks the questions of why you would want to embark on a Data warehouse project. Ralph uses plain language to describe star schemas and data warehouse design. Each chapter takes a real world industry example such as telecoms, retail, banking and Insurance and demonstrates star schema design. Each chapter takes on different design problems that you may face when building a star schemas.
The first Edition contains a CD with sample stars populated based on each chapter. The later editions do not come with the CD.
It’s a great book for learning about design and understanding the concepts of data warehouses and star schemas. Even if you are not a developer this book is still for you. It makes the connection between business problems and data warehouse design. The great thing about this book is its technology independent.
Wednesday, January 25, 2006
KB Alerts Feed added to my Blog
One site I keep an eye on is http://www.kbalertz.com/. It’s a great resource for finding Microsoft known bugs and knowledgebase articles.
I have added feeds for SQL Server 2000 and 2005 to my side bar.
I have added feeds for SQL Server 2000 and 2005 to my side bar.
Friday, January 20, 2006
Avoiding Microsoft SQL Server 2005 Deployment Pitfalls
January has not been a good month for blogging. It seems the reality of coming back to work has taken over my time a bit. Plus I am currently training for a Mountain bike race in March. I came across this interesting installation guide on installing SQL Server 2005. It’s worth a read if you have not installed SQL 2005 before.
Check out: Avoiding Microsoft SQL Server 2005 Deployment Pitfalls
It raises an interesting point, what is the minimum spec to locally develop SQL 2005 applications on. Currently I am running a HP nc6000 with 2 Gig of Ram. It’s the minimum I would recommend to anyone starting development on a laptop.
Any one looking at serious development should always have a development server. A basic development server I would start with a 2 way dual core 64x with 4 Gig minimum. Of course you will still need to size your dev server based on project requirements.
Check out: Avoiding Microsoft SQL Server 2005 Deployment Pitfalls
It raises an interesting point, what is the minimum spec to locally develop SQL 2005 applications on. Currently I am running a HP nc6000 with 2 Gig of Ram. It’s the minimum I would recommend to anyone starting development on a laptop.
Any one looking at serious development should always have a development server. A basic development server I would start with a 2 way dual core 64x with 4 Gig minimum. Of course you will still need to size your dev server based on project requirements.
Wednesday, January 04, 2006
SQL Server 2005 Services Manager
So its 2006, No more CTPs or Launches to look forward too; Instead it's back to the reality of doing projects with SQL Server 2005.
Like most developers I miss SQL Server Services Manager to start and stop SQL. Mainly because I have a laptop and I use it for everything else as well as development.
To get round this I have created two DOS batch files on my desktop for stoping and starting required SQL services. There are three main services that I have in my batch files:
NET START MSSQL
For a Named Instance use the following syntax.
NET START MSSQL$InstanceName
Strangely enough to stop Services you use the NET STOP Comand. Listed below are the two batch scripts I use to stop and start SQL Server , Analysis Services and Reporting Services on my Laptop. Feel free to save them in notepad with the .bat extention.
Start Serivces down batch script.
CDREM -----------------------------------------------------------------
REM SQL Server 2005, Analysis and Reporting Services START Script
REM -----------------------------------------------------------------
NET START MSSQL$SQL2005
NET START MSOLAP$SQL2005
NET START "IIS Admin"
NET START w3svc
NET START ReportServer$SQL2005
Stop Serivces down batch script.
CD\
REM -----------------------------------------------------------------
REM SQL Server 2005, Analysis and Reporting Services STOP script
REM -----------------------------------------------------------------
NET STOP MSSQL$SQL2005
NET STOP MSOLAP$SQL2005
NET STOP w3svc
NET STOP "IIS Admin"
NET STOP ReportServer$SQL2005
Like most developers I miss SQL Server Services Manager to start and stop SQL. Mainly because I have a laptop and I use it for everything else as well as development.
To get round this I have created two DOS batch files on my desktop for stoping and starting required SQL services. There are three main services that I have in my batch files:
- MSSQL - The services name for SQL Server 2005,
- MSOLAP -The services name for Analysis Services 2005,
- ReportServer - The services name for Reporting Services.
NET START MSSQL
For a Named Instance use the following syntax.
NET START MSSQL$InstanceName
Strangely enough to stop Services you use the NET STOP Comand. Listed below are the two batch scripts I use to stop and start SQL Server , Analysis Services and Reporting Services on my Laptop. Feel free to save them in notepad with the .bat extention.
Start Serivces down batch script.
CDREM -----------------------------------------------------------------
REM SQL Server 2005, Analysis and Reporting Services START Script
REM -----------------------------------------------------------------
NET START MSSQL$SQL2005
NET START MSOLAP$SQL2005
NET START "IIS Admin"
NET START w3svc
NET START ReportServer$SQL2005
Stop Serivces down batch script.
CD\
REM -----------------------------------------------------------------
REM SQL Server 2005, Analysis and Reporting Services STOP script
REM -----------------------------------------------------------------
NET STOP MSSQL$SQL2005
NET STOP MSOLAP$SQL2005
NET STOP w3svc
NET STOP "IIS Admin"
NET STOP ReportServer$SQL2005
Subscribe to:
Posts (Atom)
