Ever wanted to create a diagram of your MS SQL Database tables in Power BI? Here is what I did to make that happen. The approach is to build a query that can be run against the catalog system views inside each MS SQL database. The approach is to build a list of “From” and Read more about Diagram your Database Tables using Power BI[…]
From 2016 we able to do machine learning inside the SQL Server 2016 and 2017. In these post series, I am going to show some tips and trips for that. such as set up R services in SQL Server 2016, how to be sure we able to do that, and also how we can see Read more about R in SQL Server – write R Scripts- Part 1[…]
Every so often I find myself needing to import data, but only want data relevant to values already existing in my data model. This is common where I build smaller models based on a subset of fact or transaction records. Rather than bring in entire dimension tables, I may only need the dimension records that Read more about Dynamic SQL Using Power Query[…]
Almost a year ago I published first version of T-SQL script to generate and populate a general purpose date dimension here. Today I want to publish the second version of that date dimension which has some corrections (fiscal calendar calculation fixed), and additional attributes (such as current year, current month, and current day) Multiple countries Read more about Script to Generate and Populate Date Dimension Version 2: Adding Multiple Financial Years[…]
One of the most common errors that you might face in join statements is this error: Msg 4104, Level 16, State 1, Line … The multi-part identifier …… could not be bound. The main reason for this error is that the source table cannot be found, for example if you have statement such as Table1.OrderDate, Read more about Fixing the error: The multi-part identifier … could not be bound in Join statements[…]
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[…]
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[…]
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[…]