Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Business Intelligence and Enterprise Architecture

Derek Wilson delivers tactical and strategic Business Intelligence and Enterprise Architecture solutions. His primary focus in on Microsoft SQL Server technologies and aligning business problems to technology solutions. He architects BI solutions leveraging SQL Server, SharePoint and any other technologies that help his clients achieve better data driven decisions. By leveraging the information learned while collecting requirements for BI projects, he helps align business processes to technology helping further organizations Enterprise Architecture. He is an author, trainer, blogger and has been using SQL Server since version 6.5.

SSRS Farm to Scale-Out Performance

SSRS Farm Overview

Building a SSRS farm will require Enterprise or Business Intelligence editions of SQL Server for versions 2012 and 2014.  SQL Server allows for the deployment of 2 or more servers running SQL Server Reporting Services to increase the performance of your reporting environment.

SSRS stores its data… Read more

0 comments, 70 reads

Posted in Business Intelligence and Enterprise Architecture on 26 January 2015

T-SQL Random Numbers using RAND() for Test Data

T-SQL Random Numbers using RAND()

SQL Server includes the T-SQL RAND() function to create a random value between 0 and 1 of float datatype.  To create a random number execute Select Rand(), in my example it returned .0131039082850364.  If I wanted to always return the same number I can… Read more

1 comments, 111 reads

Posted in Business Intelligence and Enterprise Architecture on 12 January 2015

T-SQL COALESCE Example

T-SQL COALESCE

SQL Server T-SQL coalesce simplifies the use of a case statement to find the first non-null value of your expression.  For example, I want to return the products in the AdventureWorks sample database and show the SellEndDate if it exists or the SellStartDate if the SellEndDate is null. Read more

0 comments, 7,104 reads

Posted in Business Intelligence and Enterprise Architecture on 26 December 2014

ROW_NUMBER SQL Server

Row_Number SQL Server

SQL Server includes several Ranking Functions that can be called in T-SQL.  One of these is the Row_Number() function.  You can use this function to return a sequential number in your result set that begins at 1.

There are 2 arguments that can be passed into the… Read more

1 comments, 6,686 reads

Posted in Business Intelligence and Enterprise Architecture on 16 December 2014

Azure SQL Server Database

SQL Server in Azure

You can create and leverage cloud based SQL Server instances using Microsoft Azure.  However, if you have never used the Azure portal it can be a bit challenging to get started.  This post will show you how the steps required to create an Azure SQL Server… Read more

1 comments, 5,986 reads

Posted in Business Intelligence and Enterprise Architecture on 1 December 2014

SQL Server Management Studio Reports

SQL Server Management Studio Reports

Whether you are an Application, SQL, BI Developer or a DBA.  Knowing what is occurring within your database is critical to the performance of your applications.  One tool you can leverage to see what is happening are the built in reports in SSMS.  SQL Server… Read more

1 comments, 6,194 reads

Posted in Business Intelligence and Enterprise Architecture on 17 November 2014

SQL Server Table Partition Example

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…

Read more

0 comments, 260 reads

Posted in Business Intelligence and Enterprise Architecture on 3 November 2014

SQL Pass Summit 2014

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… Read more

0 comments, 176 reads

Posted in Business Intelligence and Enterprise Architecture on 30 October 2014

SQL Server TABLESAMPLE

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… Read more

1 comments, 5,721 reads

Posted in Business Intelligence and Enterprise Architecture on 20 October 2014

SQL Server Checksum

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…

Read more

0 comments, 547 reads

Posted in Business Intelligence and Enterprise Architecture on 14 October 2014

Common Table Expressions (CTE)

Common Table Expresseion (CTE)

A common table expression (CTE) is a derived table that is defined and only stored for the duration of the query. CTE’s can be used for recursive queries, creating a view on demand and referencing the same table multiple times in the same query.  CTE’s make… Read more

0 comments, 8,087 reads

Posted in Business Intelligence and Enterprise Architecture on 3 October 2014

SQL Server 2014 Cardinality Estimator

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…

Read more

0 comments, 241 reads

Posted in Business Intelligence and Enterprise Architecture on 25 September 2014

Schema on Read vs Schema on Write

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… Read more

0 comments, 215 reads

Posted in Business Intelligence and Enterprise Architecture on 12 September 2014

Business Intelligence Strategy – Beginning Steps

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… Read more

0 comments, 315 reads

Posted in Business Intelligence and Enterprise Architecture on 1 September 2014

SQL Server Keyboard Shortcuts

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.… Read more

1 comments, 8,129 reads

Posted in Business Intelligence and Enterprise Architecture on 18 August 2014

Effective Business Intelligence

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… Read more

0 comments, 266 reads

Posted in Business Intelligence and Enterprise Architecture on 4 August 2014

5 Tips for Finding SQL Server System Data

Regardless of the type of development (application or reporting) that you are performing on SQL Server.  There are 5 items within SQL Server that I consistently use to help me design and troubleshoot solutions.

SP_Who

SP_Who is a system stored procedure that returns information on what is currently running on… Read more

3 comments, 8,974 reads

Posted in Business Intelligence and Enterprise Architecture on 17 July 2014

Azure HDInsight – Hadoop in the Cloud

Azure HDInsight Overview

HDInsight is a cloud based implementation hosted in the Microsoft Azure cloud. It enables you to quickly create, expand and delete a Hadoop cluster installation as your project dictates. For organizations just beginning to see how Big Data could be leveraged to solve business problems. A HDInsight… Read more

0 comments, 214 reads

Posted in Business Intelligence and Enterprise Architecture on 10 July 2014

SQL Server 2014 New Features

SQL Server 2014

Below is a listing of the SQL Server 2014 new features and where you can find more information on them.

Analysis Services

Power View is now able to access Multidimensional models in SQL 2014.

Complete information is available at MSDN

Database Engine

Included in the new release… Read more

Microsoft Azure Data Services

Azure Data Services

Microsoft’s Azure data services provides you with a method to enhance your enterprise architecture and business intelligence options.  No longer are you limited to building more servers or adding SAN storage in your on-premise datacenter.   By leveraging the data services in Azure you can become elastic in… Read more

0 comments, 279 reads

Posted in Business Intelligence and Enterprise Architecture on 21 June 2014

Older posts