James works at Microsoft as a big data and data warehousing solution architect where he has been for most of the last ten years. Before that he was an independent consultant working as a Data Warehouse/Business Intelligence architect and developer. He is a prior SQL Server MVP with over 35 years of IT experience. Check out his book Deciphering Data Architectures: Choosing Between a Modern Data Warehouse, Data Fabric, Data Lakehouse, and Data Mesh.
A table that does not have a clustered index is referred to as a Heap. While a lot has been written about index fragmentation and how to defrag indexes, there is not much that talks about how to defrag a … Continue reading →
If you are using Visio 2010 and creating tables via the Database Model Diagram template, you may be surprised to learn there is not a way to build CREATE TABLE scripts from your resulting diagram. The last release that supported … Continue reading →
The SQL Server 2012 Developer Training Kit has been updated with a bunch of new content (download it here via the web installer). New and updated content will be released periodically and can be downloaded on-demand using the Web Installer. This … Continue reading →
Microsoft has posted the licensing for SQL Server 2012. It has simplified licensing: SQL Server 2012 will offer two licensing options – one that is based on computing power (“core based”), and one that is based on users or devices (“Server + … Continue reading →
Behind the scenes in SSIS, the data flow engine uses a buffer-oriented architecture to efficiently load and manipulate datasets in memory. The benefit of this in-memory processing is that you do not need to physically copy and stage data at … Continue reading →
Dimensions are often recycled for multiple purposes within the same database. For instance, a “Date” dimension can be used for “Date of Sale”, as well as “Date of Delivery”, or “Date of Hire”. This is often referred to as a … Continue reading →
A conformed dimension is a dimension that has the same meaning to every fact with which it relates. Conformed dimensions allow facts and measures to be categorized and described in the same way across multiple facts and/or data marts, ensuring consistent … Continue reading →
A reference dimension occurs when the key column for the dimension is joined indirectly to the fact table through a key in another dimension table. This results in a snowflake schema design. The following figure shows one fact table named InternetSales, … Continue reading →
SQL Server 2012 RC0 (Release Candidate 0), formerly known as SQL Server Denali, is now available for download. SQL Server 2012 RC0 is the first release which is feature complete. This was unexpected as no one gave any hints that it … Continue reading →
Degenerate dimensions, also called fact dimensions, are standard dimensions that are constructed from attribute columns in fact tables instead of from attribute columns in dimension tables. This is because useful dimensional data is sometimes stored in a fact table to … Continue reading →
In SSAS, data structures do not always conform to the snowflake or star schema model where one fact is associated with a single dimension member. For example, consider the example of financial transactions in accounts that can have one or … Continue reading →
Parallel execution in SSIS improves performance on computers that have multiple physical or logical processors. To support parallel execution of different tasks in a package, SSIS uses two properties: MaxConcurrentExecutables and EngineThreads. If you are like me, you probably did not … Continue reading →
If you have been creating SSIS packages in Business Intelligence Development Studio (BIDS) for any length of time, you have likely noticed that SSIS packages run slower in BIDS as opposed to running them in a SQL Server agent job … Continue reading →
As a follow-up to my post SQL Server “Denali”: New Certifications, the seven exams for SQL Server 2012 are: Querying Microsoft SQL Server (70-461) [BI and DBA] (info) (course) Administering Microsoft SQL Server 2012 Databases (70-462) [DBA] (info) Developing Microsoft … Continue reading →
A few weeks ago I asked a colleague, who is a DBA, if he was going to the SQL PASS Summit. He had no idea what PASS was. It then occurred to me that there are probably thousands of DBAs, BI … Continue reading →
Are you a salaried employee looking to switch to a W-2 or 1099 contractor? (For an explanation of W-2 and 1099, click Consultants: 1099 or W-2?) If so, what hourly pay rate would you need to at least match your … Continue reading →
All the videos for this popular conference are online or will be soon (48 of them). Take a look at the recorded sessions at http://sqlbits.com/content/Event9. All are free to watch! The one’s on my list to watch (because I’m interested in SQL Server … Continue reading →
Another must read white-paper: Analysis Services 2008 R2 Performance Guide. This white paper describes how business intelligence developers can apply query and processing performance-tuning techniques to their Microsoft SQL Server 2008 R2 Analysis Services OLAP solutions. It applies to SQL Server … Continue reading →
As you may know, the TechEd in the U.S. is not the only one. There have been many all over the world, and it 2011 there have been one’s in New Zealand, Australia, and next month in Russia. The Australia and New … Continue reading →
Technology Adoption Program (TAP) and Rapid Deployment Program (RDP) are ways for Microsoft to get early feedback on new products or product updates as well as give important customers a heads-up on what Microsoft is up to. Participation is by … Continue reading →