DateTimeOffset to Date Time Offset (Dimension) Identities

These SQL functions might come in handy for your ETL and DWH.
I’m currently designing a DWH which will have three ‘TimeDimensions’:
DateDimension (grain = day)
TimeDimension (grain = second)
TimeZoneDimension (grain = offset in minutes)

Simply said, these functions convert a DATETIMEOFFSET value into three types of integer identity values (which can be used in your dimensions).

DateTimeOffset to Date Time Offset Identities - Results

Want it? 🙂
Download it here:
DateTimeOffset to Date Time Offset Identities – SQL Code

Advertisements

ValueFormat function – SSRS Format Mask

This function really comes in handy when you’re working on a report that is able to display numbers/currencies and percentages.

ValueFormat-Code

ValueFormat-Results

Code:

CREATE FUNCTION [dbo].[ValueFormat]
(
   @Value FLOAT --You might want to replace this by DECIMAL(19,4) if it needs to be accurate!
   ,@Character VARCHAR(1)
)
RETURNS VARCHAR(20)
AS
BEGIN

   DECLARE @ValueFormat VARCHAR(20)
   SET @ValueFormat =
   (
   SELECT CASE
      WHEN (@Value) < -100000000000 and @Character = '#' THEN '#,##0.00M'
      WHEN (@Value) < -1000000000 and @Character = '#' THEN '#,##0.00M'
      WHEN (@Value) < -10000000 and @Character = '#' THEN '#,##0.00M'
      WHEN (@Value) < -1000000 and @Character = '#' THEN '#,##K'
      WHEN (@Value) < -10000 and @Character = '#' THEN '#,##K'
      WHEN (@Value) < -1000 and @Character = '#' THEN '#,##K'
      WHEN (@Value) < -1 and @Character = '#' THEN '#,##0.00'
   
      WHEN (@Value) < 1 and @Character = '#' THEN '#,##0.00'
      WHEN (@Value) < 10000 and @Character = '#' THEN '#,##K'
      WHEN (@Value) < 1000000 and @Character = '#' THEN '#,##K'
      WHEN (@Value) < 10000000 and @Character = '#' THEN '#,##0.00M'
      WHEN (@Value) < 1000000000 and @Character = '#' THEN '#,##0.00M'
      WHEN (@Value) < 100000000000 and @Character = '#' THEN '#,##0.00M'
   
      WHEN (@Value) < 0 and @Character = '%' THEN '0.##%'
      WHEN (@Value) < 1 and @Character = '%' THEN '0.##%'
   END
   )
   RETURN @ValueFormat

END

Check SQL Server Agent status on all SQL Servers

There are two easy checks to verify if the SQL Agent service is running:

SQL-Agent-Status-Code

SQL-Agent-Status-Results

#1) By using the build-in DM-view:

SELECT *
FROM master.sys.dm_server_services

(last_startup_time is always NULL so it seems, Microsoft is working on it? more info)

#2) By querying it (script by Colin Stasiuk):

IF EXISTS (
   SELECT 1
   FROM master.dbo.sysprocesses
   WHERE program_name = N'SQLAgent - Generic Refresher'
   )
      BEGIN
         SELECT @@SERVERNAME AS 'InstanceName', 1 AS 'SQLServerAgentRunning'
      END

ELSE
   BEGIN
      SELECT @@SERVERNAME AS 'InstanceName', 0 AS 'SQLServerAgentRunning'
   END

List all active/inactive SQL Agent Jobs (disabled/enabled)

How to retrieve a complete list of all SQL Agent Jobs that includes whether the jobs are active/inactive (enabled/disabled)?

You might want to use this T-SQL statement:

SQL-Agent-Jobs-Status-Code

Results:

SQL-Agent-Jobs-Status-Results

Code:

SELECT
   jobs.Job_id
   ,steps.Database_name AS DatabaseName
   ,jobs.Name AS JobName
   ,JobStatus = CASE
      WHEN jobs.Enabled = 1 THEN 'Active'
      ELSE 'InActive'
   END
   ,jobs.Description
   ,steps.Step_name AS StepName
   ,steps.Command AS SQLCommandUsed
   ,steps.Server AS ServerName
FROM msdb.dbo.sysjobs jobs
   INNER JOIN msdb.dbo.sysjobsteps steps
      ON jobs.job_id = steps.job_id

More info about these system tables can be found on MSDN: click here.

Gartner: Magic Quadrant for Data Warehouse (DWH) Database Management Systems 2013

Gartner released its annual Data Warehouse (DWH) Magic Quadrant report last Monday!

Click here for the full report by Gartner.

GartnerDW2013

Microsoft
Microsoft (www.microsoft.com) markets the SQL Server 2012 Fast Track reference architecture, SQL Server 2008 R2 Parallel Data Warehouse Appliance (Update 3), HP Business Decision Warehouse and Dell Quickstart Data Warehouse Appliance. Microsoft does not report customer or license counts, but there are thousands of SQL Server customers worldwide (including analytical and transactional systems).

Strengths
Broad market usage.

With customers in the manufacturing, telecommunications, insurance, government, retail, financial services, transportation and other industries, Microsoft’s solution demonstrates wide applicability to vertical markets. In addition, customers come from all across the world, from North America to EMEA to Asia/Pacific, including countries such as Belgium, Denmark, New Zealand, South Korea, Ukraine and the U.S. Furthermore, customers report annual revenues ranging from under $100 million to over $10 billion. Microsoft is a major rival to any vendor just in sheer numbers of deployed data warehouses (although not in revenue, which, though rising, remains much lower).
Competitive capability and customer loyalty.
Microsoft customers tend to grow with Microsoft. Microsoft also holds its own when competing with other leading vendors, its usual rivals in competitive situations being IBM and Oracle. Its competitors are most often rejected on the basis of price, which means Microsoft’s pricing is considered favorable. Reference customers indicated that half of Microsoft’s competitive wins were directly related to its price and ease-of-use advantages over competitors. Importantly, although customers identified issues of product maturity (such as with metadata and monitoring tools), they considered Microsoft’s skills availability and pricing compensated for these weaknesses — though they still encourage Microsoft to improve on its shortcomings. In addition, Microsoft can claim the highest percentage of customers performing data warehousing on a current software release, according to our survey data.
Product form factors and partners.
By offering DBMS software, providing reference architectures, prebuilding and preloading implementations of reference architectures, offering an appliance and offering professional services (and partner connections), Microsoft offers almost every configuration of data warehouse deployment. In addition, Microsoft has an impressive mix of partners for colead management and lead development, hardware partners, codevelopment agreements with other suppliers and implementers, and reseller agreements. Moreover, Microsoft’s Parallel Data Warehouse appliance, despite a slow start, has been adopted by approximately 100 organizations in the past 18 months. Further adoption of this appliance is likely as Dell continues to enter the data warehouse space and sells the Dell Parallel Data Warehouse Appliance.
Completeness of solution approach.
With so much of the software needed for BI and data warehousing initiatives included in a single license, Microsoft makes the processes of purchasing and license management very easy. In addition, Microsoft offers data models (through partners) and provides assistance (directly and through partners) at the time of implementation. The most notable change by Microsoft in 2012 regarding data warehousing was its new vision for combining structured and unstructured data, desktop analytics tools (such as PowerPivot), enterprise warehousing, fast implementation strategies and the cloud.
Logical data warehouse practices.
Microsoft started early on unstructured and structured data in combination by using SharePoint, PowerPivot and technology acquired from Fast Search & Transfer — all coordinated by the SQL Server engine. It has since added in-memory capabilities across the stack by using xVelocity in SQL Server 2012, SQL Server Analysis Services (SSAS) and PowerPivot. In addition, Microsoft has been catching up with the announcement of HDInsight and a solution offering in partnership with Hortonworks. Finally, with Microsoft’s StreamInsight complex event processing solution, customers can address the velocity requirements of big data.

Cautions
Perceived lack of enterprise-readiness.

As SQL Server grows to enable its expanded use for data warehousing, it is generating more questions from clients about its readiness to support large data warehouses and HA/DR capabilities. However, with the release of SQL Server 2012, Microsoft has continued to mature this offering with the addition of Always On features to improve clustering, failover and active-active data warehouses. Growth in the number of large SQL Server data warehouses will also reduce this perception.
Complete data management capabilities.
SQL Server offers a complete set of data management capabilities, including data integration with SQL Server Integration Services and OLAP capabilities with SSAS. However, as data warehousing projects have matured and grown in complexity, clients have reported (both through Gartner’s direct interactions and through the survey we conducted for this Magic Quadrant) limitations with the capabilities offered with SQL Server, specifically in the areas of metadata management and data integration. Microsoft is still developing its capabilities in these areas.
Business model driven by the “majority market.”
Distributed process management and execution is one aspect of the logical data warehouse. However, although Microsoft was quick to respond to its customers’ fast-developing need to cope with data in high volume, in great variety and at fluctuating velocity, and to offer integration with major Hadoop distributions, this left it behind some of the Leaders in terms of vision. But, then, being a “fast follower” for some features and functions has always been part of Microsoft’s business model.



Gartner: Magic Quadrant for BI Platforms 2013





SQL Scalar-valued Function – Capitalize the first character

It’s simple, it’s basic, it spans across 16 lines of T-SQL code (including breaklines)….and you might need it someday 🙂


CREATE FUNCTION [dbo].[fn_TextToInitCase]
(
@VarString NVARCHAR(250)
)
RETURNS NVARCHAR(250)
AS
BEGIN

DECLARE
@Result NVARCHAR(250)

SET @Result = UPPER(SUBSTRING(@VarString,1,1))+SUBSTRING(@VarString,2,LEN(@VarString))

RETURN @Result

END