New SelectViews Show Posted
(and a new format!) We’re experimenting with releasing the show in segments, so you’ll be able to watch different portions of the show at different times during the week (we’ll be releasing a segment every day now). OR, you can tune in on Fridays (starts next week) to watch the entire program all at once automagically. You can watch it the traditional way, or the new way, segment by segment. Big bites or little bites, it’s up to you.
Today’s show: SQL Server Challenge Results – setting up a data warehouse.
Watch the show here
I’d love to hear your thoughts on this new approach – we’re open to ideas and feedback! Email me here. We have some other really cool things on the near-term horizon as well… Please let us know if you have any issues as well, the player changes are Beta and we’re updating and tweaking things as we go.
Featured Article(s)
Reclaiming the table space after dropping a column [without clustered index]
(Vadivel Mohanakrishnan) If we drop a column it gets dropped but the space which it was occupying stays as it is! In this article we would see the way to reclaim the space for a table which has a non-clustered Index.
Down to Earth Information About Performance
Performance is a bit(!) more than just running queries and tuning indexes. To get to the real issues that may be impacting your application, you often need to understand exactly where the bottleneck really is. This Wait-Time impact each SQL statement incurs is critical to understanding where to look to tune your application. With Confiio Ignite, you can get this wait-time information and much more. Find out how to minimize delays, what you need to consider changing and much more. Check out Confio Ignite and see where your bottlenecks really are in your system. Get more information here.
Business Intelligence and Data Warehousing Feedback
David wrote in with his experiences: "
1. Are you archiving information to a secondary server for query/reporting applications?
Yes. The data used for query and reporting is a copy of the production data, stored separately. At first, business users asked us to ‘pluck and plop’ their data acquisition application databases into a so-called data warehouse. The advantage was that the users were comfortable and familiar with the tables and columns. It also helped that major queries were not going against the production databases. The disadvantage was that the data was in no way optimized for queries and analysis.
We keep the original data for Audit and Archive purposes. In addition, we are now beginning to create data stores that are optimized for analysis by using the Ralph Kimball design principles that include fact tables, dimension tables and a star schema. Part of the dimension tables is creating a Client dimension and a Provider dimension that will eventually serve all of the data warehouse as shared dimensions. Then we will be able to know if the person who participates in one program is the same person who participates in another program.
2. If yes, when you archive, do you REMOVE it from the first database?
When the acquisition database gets so large that performance suffers, the users finally become interested in removing some of the oldest data. So far, we always keep the most recent 2 years of data in the production acquisition system.
3. If yes, what is your retention time-frame? Or does it vary based on the type of information?
The main factor in retention time is how large the database has grown and how much the performance suffers. We have just gotten to the place where business users want us to remove old data from the production system. I believe we will get more requests for this as they experience the improved response time and also experience that they do not regret having removed the old data. The old data is still available for reports and analysis in the data warehouse.
We are also doing this in our data warehouse itself. Our data warehouse has ‘Audit and Archive’ data that is complete. We also have identical Operational Data Stores (ODS) that are limited to the most recent 26 months of data – a bit over two years. This is adequate for most reports and ad-hoc queries. These reports and ad-hoc queries run much faster using the 26 month ODS data.
4. What have you done on hardware? Did you get a lesser machine for the BI stuff, or was it actually a beefier machine?
The data warehouse has a beefier machine, but it also has data from a majority of our operational systems. So, any individual operational system may have more power available to it, on its server. But the data warehouse, with much more data, and hit upon by more users from more business areas, has the beefiest hardware."
Featured White Paper(s)
Finding and Fixing Bad SQL in Microsoft SQL Server 2000
This article will teach you the basic techniques used to find and fix poorly performing SQL statements (usually SELECT statem… (read more)
SQL Server Performance Analysis
Analyzing SQL Server performance can be a daunting task especially when a large number of users and application programs are … (read more)
The Database Utility for SQL Server: A Highly Available, Flexible, Simple Approach to SQL Server Consolidation
The success of SQL Server in recent years has produced a phenomenon of SQL Server sprawl — the uncoordinated deployment of t… (read more)
The Failure of Relational Database, The Rise of Object Technology and the Need for the Hybrid Database
Hybrid databases provide the speed and rapid development capabilities of object technology PLUS robust SQL access…. (read more)