Why Use a SQL Server Table Partition Data warehouses and data repositories often have tables that store millions of records and each day adding thousands more. As the data ages, the older records generally require fewer updates. If all of these records are kept in a single physical table. Queries and maintenance on the table … [Read more…]
Next week I will be in Seattle for the 2014 SQL Pass Summit event. If you are involved in projects that leverage the Microsoft SQL Server stack this is a great conference to attend. If this is your first time going and would like some tips on making the most of the conference, send me … [Read more…]
Using TABLESAMPLE in SQL Server SQL Server has several ways for you to limit the number of records returned from a query. For example, the AdventureWorks [sales].[salesorderdetail] table has 121,317 rows loaded in sequential order starting with SalesOrderDetailID 1. By Using a top 1000 clause I can limit the results to the first 1000 rows … [Read more…]
Use SQL Server Checksum in Data Warehousing When building data warehouses you often need a fast way to know if the data in the warehouse is different from the incoming source data. One method to do this is to create a SQL Server CHECKSUM column in the data warehouse table and another on the incoming … [Read more…]
SQL Server 2014 Cardinality Estimator The SQL Server 2014 cardinality estimator has been improved to increase the quality of SQL Server query plans in both OLTP and data warehousing solutions. The cardinality of a field refers to the number of unique values that are populated in the table. Gender would be a low cardinality field. Storing … [Read more…]
Schema on Read vs Schema on Write in Business Intelligence When starting build out a new BI strategy. There are more options now than ever before. Gone are the days of just creating a massive star or snowflake schema to store THE data warehouse. In today’s business, you need to store data in structured and … [Read more…]
Business Intelligence Strategy A business intelligence strategy is used to determine where an organization is and where they are going with their BI implementation. To start building an effective business intelligence strategy requires taking an objective look at your organizations BI capabilities including data, technology and people. DATA When you begin your discovery into the … [Read more…]
SQL Server Keyboard Shortcuts for Management Studio SQL Server Management Studio is filled with lots of optional settings that lets you maximize your development experience. There are a few helpful settings and shortcut keys that will help you get the most out of the tool while working on database activities. Some of these have changed … [Read more…]
How Business Intelligence Can Help Effective business intelligence starts with questions that will help your organization make better decisions. For example, an Electrical Utility provider may be asking how to better understand its customer usage patterns for the next 1-6 months. An insurance provider may be asking how to look for fraudulent medical claims. Or … [Read more…]
ETL Process Reporting databases need to consolidate data from across many applications within an organization. In order to successfully pull data from source applications and make it into usable data for reporting and analytics. There are two paths that most Business Intelligence strategies can leverage, ETL or ELT. Extract Transform and Load (ETL) ETL is … [Read more…]