#FloodSL Red Alert

I've created a simple Power BI report based on the source published by Department of Government Information, Sri Lanka. It is an urgent responsibility to focus on these areas and evacuate as its more likely to rain in next few days. You can navigate the areas through the map. View the Report

Talk to Your Power BI Visuals With Microsoft Cortana

You may have been modelling data and visualize through Power BI since many years. Have you ever thought speak to the data through Power BI visuals instead click and view. Microsoft Cortana empower us to speak to query the data and get the results without navigate the reports in traditional way.

Here are the 5 steps you need to integrate your Power BI reports with Cortana. (5 mins read)
Step 1One of the very first step is to check whether your Windows version need to be 1511 or higher. You can check it by

Click Windows Icon and type “Your PC”

Check the Version whether greater than 1511.

Step 2

Make sure your power BI account and windows account interconnected. 
Click windows go to settings and Click Accounts 

Click Access Work or School Tab. It shows all the accounts added with your windows account. If you have not added your power BI account here, its time to add and link with your windows PC 

Step 3

Make sure your PC is connected with O365
Go to Cortana icon in your task bar and click …

SQL Server 2016 Temporal Tables Usages

As most of you already aware, the Temporal table capabilities are a lot we can find out and use for our developments both database and data warehousing/analytics. Follow diagram shows major usages of Temporal.

From the below link you can find-our wiki article I've written regarding Slowly Changing Dimension usage of Temporal Tables

[Execute SQL Task] Error: An Error Occurred While Assigning VARCHAR(MAX) Value to Variable

You may be encounter problem with when you try to return VARCHAR(MAX) data using Execute SQL Task in SSIS. Below error message will be return most of the time when you wrote T-SQL which return VARCHAR(MAX) column in Execute SQL Task and run the SSIS package. 

The full error message as below,

[Execute SQL Task] Error: An error occurred while assigning a value to variable "SourceQuery": "The type of the value (DBNull) being assigned to variable "User::SourceQuery" differs from the current variable type (String). Variables may not change type during execution. Variable types are strict, except for variables of type Object."
SourceQuery is the variableName try to assigned the VARCHAR(MAX) value.

SSIS does not understand VARCHAR(MAX) data type. So, you can't simply store varchar(max) values in ssis variables. As an option, you have to limit the length into something like VARCHAR(8000)

What Is a Slowly Changing Dimension?

In Data Warehousing dimension tables are typically static. But sometimes you may find dimension entities which can be change over the time. Location of the customer, Project of the Employee are few examples for these type of dimension members. When we implement the warehouse ETL process we need to cater this issue. otherwise the business user end either will lose the data or they might see see inaccurate data in the report. In the data warehouse jargon this common issue will called as Slowly Changing Dimension problem or SCD.

There are altogether 7 SCD types. The folloing Five types are the ones widely used in the industry. Below, you can see a illustration of how each type works 😊

In this example shows the customer table, customer location changing from the source. When it comes to data warehouse DimCustomer customer dimension table how it looks like in each SCD type.

Type 0 
In type 0 preserve the original value, when the attribute changes in source operational system are not going …

Microsoft SQL Server 2016 Technical Overview

I'm just started to reading this .... 🙌 Its a great way to learn about all the Microsoft SQL Server 2016 features and capabilities. Here is the Link for get the free ebook of Technical Overview

Microsoft SQL Server 2016 Technical Overview

Quick Quiz #1 - How To Get T-SQL Result Set Format Without Executing The Query

Is there any option like get the table format or result-set format of underline query without truly executing the query in SQL Server? The answer is Yes. You can write T-SQL query and see the result format without return the data.
How To ?

SET FMTONLY ON option allows us to get the format of the table or result set without actually running the query. Its return metadata only to the client. Therefor if you include actual execution plan to the query it won't fire. below is the code.
USE [AdventureWorksDW2014] GO SET FMTONLY ON; SELECT * FROM dbo.DimCustomer SET FMTONLY OFF;