SQL Server 2016 CTP 3.0 is available, and some of you already played with it. There are great set of new features for BI in 2016 in all areas; SSIS, MDS, SSRS, and SSAS. However there are some features which are part of Database Engine but still relates to BI and Data Warehousing. Temporal Tables Read more about Temporal Tables: A New Method for Slowly Changing Dimension[…]
SQL Server 2012 has a set of useful functions to work in T-SQL environment. Lead and Lag are one of the most useful functions introduced in 2012. Lag function helps to access values from previous records, and Lead function helps to access values from next records in the data set. structure of working with these Read more about Lag and Lead Functions in SQL Server 2012[…]
Today I’ve wrote a simple script to get number of records for all tables in a database and fetch those tables that contains at least one record. This picture shows an example of result set: Here is the script: declare @tempTable table(tablename varchar(max), rowscount int,processed bit) insert into @tempTable select TABLE_NAME,0,0 from INFORMATION_SCHEMA.TABLES where TABLE_CATALOG=‘DEMO’ Read more about T-SQL Script to get row count of all tables in the database[…]
Master Data Services provides data governance and reference data management as part of SQL Server product. There is a security mechanism in MDS that provides robust role based security and authorization for domain accounts and groups. Security on data also can be applied on Model level, entity, or even in row level. Security in MDS Read more about Deep Dive into Security Schema of Master Data Services Database[…]
Time Dimension is an important dimension in most of the Data Warehouses, especially when requirement for keeping hours, minutes, and seconds is vital for the business. Some blog and books and articles consider time dimension as a dimension that contains date columns, but in this post I only focus on the Time part of it, Read more about Script for Creating and Generating members for Time Dimension[…]
Date Dimension can be found in every Data Warehouse, because Date dimension is one of the most important factors of each operational system that users like to view reports based on date dimension. So the importance and requirement to a date dimension persuade me to write a t-sql script to generate records for date dimension. Read more about Script for Creating and Generating members for Date Dimensions: General Purpose[…]
SSDT has an option to test connectivity of connection managers at the time of opening the SSIS package. You can change this behaviour with WORK OFFLINE option. When you have many connection managers in SSIS package, you may face a halt at the time of opening each package with a “testing connectivity” message. This will Read more about How to get rid of TESTING CONNECTIVITY at the time of Opening SSIS packages in SSDT[…]
Hi folks, Today I face a question regarding usage of OLE DB VS. ADO.NET Connection managers in SSIS, there are some blog posts about difference between connection providers and speed and performance of them. So I thought that this will be helpful if I list different Control Flow Tasks and Data Flow Components in SSIS Read more about Difference Between Connection Managers used in SSIS Control Flow Tasks and Data Flow Components[…]
Hi folks, I’ve changed the t-sql code for Capitalize function, the previous capitalize function worked only if there is one space between words, but this version works even if there be more than one space between words. with this version of Capitalize; you can run the function like this: select dbo.Capitalize(‘st charles‘) and this Read more about Capitalize Function (Version 2.0) – T-SQL[…]
T-SQL code (working in SQL Server) to replace duplicate spaces (or triple or more) with a single space is : declare @text varchar(max) set @text=’ St Charles’ while charindex(‘ ‘,@text)>0 set @text=replace(@text,’ ‘,’ ‘) select @text and the result is : St Charles