Normal Distribution and giving the business user an ability to use 95% Confidence Interval

Assuming that your data follows Normal Distribution, if you are presented with a business case to present the numbers with 95% Confidence Interval, you could take the following approach.

Sample size < 5 – Ignore Perhaps a message that we do not have enough data is useful here. This is because in the statistical world, you ignore any sample size less than 5 as the data is highly volatile and cannot be presented with confidence.

You could bucket sample sizes from 6 to 29 into 3 or more categories depending on your business’ need for precision. Below is an example where we categorized into 3 buckets. The values highlighted in yellow are taken from a Statistical table approximating the value for different buckets for a 95% Confidence Interval and could be reused for your use case.

Lower Threshold Value

=SWITCH(

Fields!SampleSize.Value <= 5, “Not enough Data”

       , Fields!SampleSize.Value > 5 AND Fields!SampleSize.Value <= 10  , ROUND(Fields!SampleMean.Value - (2.36 * Fields!StdDev.Value/Fields!Sqrt_SampleSize.Value),2)

       , Fields!SampleSize.Value > 10 AND Fields!SampleSize.Value <= 20  , ROUND(Fields!SampleMean.Value - (2.16 * Fields!StdDev.Value/Fields!Sqrt_SampleSize.Value),2)

       , Fields!SampleSize.Value > 20 AND Fields!SampleSize.Value <= 30  , ROUND(Fields!SampleMean.Value - (2.06 * Fields!StdDev.Value/Fields!Sqrt_SampleSize.Value),2)

       , Fields!SampleSize.Value > 30, ROUND(Fields!SampleMean.Value - (1.96 * Fields!StdDev.Value/Fields!Sqrt_SampleSize.Value),2)

)

Upper Threshold Value

=SWITCH(

Fields!SampleSize.Value <= 5, “Not enough Data”

, Fields!SampleSize.Value > 5 AND Fields!SampleSize.Value < 10  , ROUND(Fields!SampleMean.Value + (2.36 * Fields!StdDev.Value/Fields!Sqrt_SampleSize.Value),2)

       , Fields!SampleSize.Value > 10 AND Fields!SampleSize.Value < 20  , ROUND(Fields!SampleMean.Value + (2.16 * Fields!StdDev.Value/Fields!Sqrt_SampleSize.Value),2)

       , Fields!SampleSize.Value > 21 AND Fields!SampleSize.Value < 30  , ROUND(Fields!SampleMean.Value + (2.06 * Fields!StdDev.Value/Fields!Sqrt_SampleSize.Value),2)

       , Fields!SampleSize.Value > 30, ROUND(Fields!SampleMean.Value + (1.96 * Fields!StdDev.Value/Fields!Sqrt_SampleSize.Value),2)

)

So the 95% Confidence Interval for your data would be the range between “Lower Threshold Value and Upper Threshold Value”.

Query to get the list of partitioned tables

If COUNT(*) = 1, then there are no partitions (only one partition, that is the table)

If COUNT(*) > 1, then there are multiple partitions


SELECT s.name AS SchemaName, t.name AS TableName



FROM SYS.TABLES t


INNER JOIN SYS.SCHEMAS s On t.schema_id = s.schema_id


INNER JOIN SYS.PARTITIONS p on p.object_id = t.object_id


WHERE p.index_id IN (0, 1)


GROUP BY s.name, t.name


HAVING COUNT(*) > 1


ORDER BY s.name, t.name;

Script to CREATE and DELETE multiple SSAS cube partitions

Here is how you can create a script to create and delete multiple SSAS cube partitions.

1. Right click on cube partition --> Script Partition As--> CREATE TO
2. Right click on cube partition --> Script Partition As--> DELETE TO
3. Embed the Delete Partition and Create Partition XMLA in the T-SQL Script below
4. Run the T-SQL script and copy the result and paste it in the scriptlet below to run these commands as a batch.
5. 
   

   Copy paste the script that is generated from the script above.. Here .....

   
   

Detailed instructions follow

T-SQL Script that dynamically generates the partitions

DECLARE @StartDate DATE = getdate() - 360;


DECLARE @EndDate DATE = getdate();

DECLARE @StartYearMonth INT = CAST(CONVERT(CHAR(6),@StartDate,112) AS INT)

DECLARE @EndYearMonth INT = CAST(CONVERT(CHAR(6),@EndDate,112) AS INT)

DECLARE @YYYYMM VARCHAR(6)

DECLARE @CreatePartition NVARCHAR(max)

DECLARE @DeletePartition NVARCHAR(max)

DECLARE @FinalPartitionDef NVARCHAR(MAX)

SELECT @DeletePartition = '





'

SELECT @CreatePartition = ''



Adventure Works DW 2008R2

Adventure Works

Internet Orders







Internet_Orders_YYYYMM

Internet_Orders_YYYYMM



Adventure Works DW

SELECT [dbo].[FactInternetSales].[ProductKey],[dbo].[FactInternetSales].[OrderDateKey],[dbo].[FactInternetSales].[DueDateKey],[dbo].[FactInternetSales].[ShipDateKey], [dbo].[FactInternetSales].[CustomerKey], [dbo].[FactInternetSales].[PromotionKey],[dbo].[FactInternetSales].[CurrencyKey],[dbo].[FactInternetSales].[SalesTerritoryKey],[dbo].[FactInternetSales].[SalesOrderNumber],[dbo].[FactInternetSales].[SalesOrderLineNumber],[dbo].[FactInternetSales].[RevisionNumber],[dbo].[FactInternetSales].[OrderQuantity],[dbo].[FactInternetSales].[UnitPrice],[dbo].[FactInternetSales].[ExtendedAmount],[dbo].[FactInternetSales].[UnitPriceDiscountPct],[dbo].[FactInternetSales].[DiscountAmount],[dbo].[FactInternetSales].[ProductStandardCost],[dbo].[FactInternetSales].[TotalProductCost],[dbo].[FactInternetSales].[SalesAmount],[dbo].[FactInternetSales].[TaxAmt],[dbo].[FactInternetSales].[Freight],[dbo].[FactInternetSales].[CarrierTrackingNumber],[dbo].[FactInternetSales].[CustomerPONumber] FROM [dbo].[FactInternetSales]
WHERE OrderDateKey <= YYYYMM



Molap

Regular



-PT1S

-PT1S

-PT1S

-PT1S












SELECT LEN(REPLACE(REPLACE(@CreatePartition,CHAR(13),''),CHAR(10), ''))

SELECT LEN(REPLACE(REPLACE(@DeletePartition,CHAR(13),''),CHAR(10), ''))



WHILE @StartYearMonth <= @EndYearMonth

BEGIN

SELECT @FinalPartitionDef = REPLACE(@DeletePartition,'YYYYMM',CONVERT(CHAR(6), @StartYearMonth, 112))

SELECT @FinalPartitionDef = @FinalPartitionDef + REPLACE(@CreatePartition,'YYYYMM',CONVERT(CHAR(6), @StartYearMonth, 112))

PRINT @FinalPartitionDef

SELECT @StartDate = DATEADD(month, 1, @StartDate)

SELECT @StartYearMonth = CAST(CONVERT(CHAR(6), @StartDate, 112) AS INT)



END

--------Now copy the script generated by running this script and paste in the command below----------------








Copy paste the script that is generated from the script above.. Here .....










Sample script looks something like this…

















Adventure Works DW 2008R2

Adventure Works

Internet Orders







Internet_Orders_201106

Internet_Orders_201106



Adventure Works DW



SELECT [dbo].[FactInternetSales].[ProductKey],[dbo].[FactInternetSales].[OrderDateKey],[dbo].[FactInternetSales].[DueDateKey],[dbo].[FactInternetSales].[ShipDateKey], [dbo].[FactInternetSales].[CustomerKey], [dbo].[FactInternetSales].[PromotionKey],[dbo].[FactInternetSales].[CurrencyKey],[dbo].[FactInternetSales].[SalesTerritoryKey],[dbo].[FactInternetSales].[SalesOrderNumber],[dbo].[FactInternetSales].[SalesOrderLineNumber],[dbo].[FactInternetSales].[RevisionNumber],[dbo].[FactInternetSales].[OrderQuantity],[dbo].[FactInternetSales].[UnitPrice],[dbo].[FactInternetSales].[ExtendedAmount],[dbo].[FactInternetSales].[UnitPriceDiscountPct],[dbo].[FactInternetSales].[DiscountAmount],[dbo].[FactInternetSales].[ProductStandardCost],[dbo].[FactInternetSales].[TotalProductCost],[dbo].[FactInternetSales].[SalesAmount],[dbo].[FactInternetSales].[TaxAmt],[dbo].[FactInternetSales].[Freight],[dbo].[FactInternetSales].[CarrierTrackingNumber],[dbo].[FactInternetSales].[CustomerPONumber]

FROM [dbo].[FactInternetSales]

WHERE OrderDateKey <= 201106





Molap

Regular



-PT1S

-PT1S

-PT1S

-PT1S





















Adventure Works DW 2008R2

Adventure Works

Internet Orders







Internet_Orders_201107

Internet_Orders_201107



Adventure Works DW



SELECT [dbo].[FactInternetSales].[ProductKey],[dbo].[FactInternetSales].[OrderDateKey],[dbo].[FactInternetSales].[DueDateKey],[dbo].[FactInternetSales].[ShipDateKey], [dbo].[FactInternetSales].[CustomerKey], [dbo].[FactInternetSales].[PromotionKey],[dbo].[FactInternetSales].[CurrencyKey],[dbo].[FactInternetSales].[SalesTerritoryKey],[dbo].[FactInternetSales].[SalesOrderNumber],[dbo].[FactInternetSales].[SalesOrderLineNumber],[dbo].[FactInternetSales].[RevisionNumber],[dbo].[FactInternetSales].[OrderQuantity],[dbo].[FactInternetSales].[UnitPrice],[dbo].[FactInternetSales].[ExtendedAmount],[dbo].[FactInternetSales].[UnitPriceDiscountPct],[dbo].[FactInternetSales].[DiscountAmount],[dbo].[FactInternetSales].[ProductStandardCost],[dbo].[FactInternetSales].[TotalProductCost],[dbo].[FactInternetSales].[SalesAmount],[dbo].[FactInternetSales].[TaxAmt],[dbo].[FactInternetSales].[Freight],[dbo].[FactInternetSales].[CarrierTrackingNumber],[dbo].[FactInternetSales].[CustomerPONumber]

FROM [dbo].[FactInternetSales]

WHERE OrderDateKey <= 201107





Molap

Regular



-PT1S

-PT1S

-PT1S

-PT1S

Customize SSMS for different environments

Sometimes it is very easy to forget that you are on Production Server and do an update or drop tables :)

It helps to color SSMS studio with bold colors to avoid making that mistake. Below is a screen-shot of how to customize the look and feel of SSMS for Dev, Test and Prod servers

Difference between standard subscription and data-driven subscriptions

Standard Subscriptions

1. Standard Subscriptions are available in Sql Server Standard version.
2. Report users can subscribe for the subscriptions.
3. The subscription is static in the sense that the list of subscribers is not derived from a query or some Active Directory search. The subscribers request(subscribe) for the report delivery or get an information that the report has run and is ready for viewing.
   

Data-Driven Subscriptions

1. Data-Driven subscriptions are available in Enterprise Edition of Sql Server
2. The DBAs/SSRS administrators are the ones who configure the subscriptions. The reports can be subscribed with different parameters.
3. The subscribers list can be dynamic and can be the result of a query.
   

What is the life time of a temporary table on Sql Server

Temporary Tables

You can create local and global temporary tables. Local temporary tables are visible only in the current session; global temporary tables are visible to all sessions.

Prefix local temporary table names with single number sign (#table_name), and prefix global temporary table names with a double number sign (##table_name).

SQL statements reference the temporary table using the value specified for table_name in the CREATE TABLE statement:

CREATE TABLE #MyTempTable (cola INT PRIMARY KEY)

INSERT INTO #MyTempTable VALUES (1)

If a local temporary table is created in a stored procedure or application that can be executed at the same time by several users, SQL Server has to be able to distinguish the tables created by the different users. SQL Server does this by internally appending a numeric suffix to each local temporary table name. The full name of a temporary table as stored in the sysobjects table in tempdb consists of table name specified in the CREATE TABLE statement and the system-generated numeric suffix. To allow for the suffix, table_name specified for a local temporary name cannot exceed 116 characters.

Temporary tables are automatically dropped when they go out of scope, unless they have already been explicitly dropped using DROP TABLE:

• A local temporary table created in a stored procedure is dropped automatically when the stored procedure completes. The table can be referenced by any nested stored procedures executed by the stored procedure that created the table. The table cannot be referenced by the process which called the stored procedure that created the table.
• All other local temporary tables are dropped automatically at the end of the current session.
• Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them. The association between a task and a table is maintained only for the life of a single Transact-SQL statement. This means that a global temporary table is dropped at the completion of the last Transact-SQL statement that was actively referencing the table when the creating session ended.

A local temporary table created within a stored procedure or trigger is distinct from a temporary table with the same name created before the stored procedure or trigger is called. All references to the name within the stored procedure or trigger are resolved against the temporary table created in the procedure or trigger, not the version that existed before the procedure or trigger was called. Nested stored procedures can also create temporary tables with the same name as a temporary table created by the stored procedure that called it. All references to the table name in the nested stored procedure are resolved to the table created in the nested procedure, for example:

CREATE PROCEDURE Test2

AS

CREATE TABLE #t(x INT PRIMARY KEY)

INSERT INTO #t VALUES (2)

SELECT Test2Col = x FROM #t

GO

CREATE PROCEDURE Test1

AS

CREATE TABLE #t(x INT PRIMARY KEY)

INSERT INTO #t VALUES (1)

SELECT Test1Col = x FROM #t

EXEC Test2

GO

CREATE TABLE #t(x INT PRIMARY KEY)

INSERT INTO #t VALUES (99)

GO

EXEC Test1

GO

The results of running this script are:

(1 row(s) affected)

Test1Col

-----------

1

(1 row(s) affected)

Test2Col

-----------

2




SWITCH function in SSRS - Equivalent of CASE statement

SQL Reporting Services Switch Statement

SSRS has a switch function, which mirrors the functionality of a Case statement, and can really save you some time.

Most common use for me are setting colors for parts of my report based on the value of a field.

For example, if we have a field: Orders (Fields!Orders.Value) which carries an integer representing the number of orders this week, we may want to set the background color of our report text box based on the order performance.

=Switch(Fields!Orders.Value < class="str">"Red", Fields!Orders.Value < class="str">"Green")

In the above example, if orders are < style="color: rgb(255, 0, 0);">Red, if they are 100 to 499, we return Green.

How does this work?

The switch statement takes an expression, followed by the return value if that expression evaluated to true. Additionally, it will always take the first expression that evaluated to true, that is why it's OK that a value of 40 (for example) would satisfy both cases. 40 will always give us red, becuase it's the first expression satisfied.

If you want a 'default return value', just take advantage of the fact that the first true expression is evaluated, and modify your code like this:

=Switch(Fields!Orders.Value < class="str">"Red", Fields!Orders.Value < class="str">"Green", 1=1, "Black")

Now, if our value doesn't satisfy the other expressions, we know the last expression will evaluate to true (1 always equals 1), hence "Black" is our default return value.