Why Cosmos DB may not be apt for building Data Warehouse?

Well, the question is slightly wrong until the context is specified because it is possible to build Modern Data Warehouse by including Cosmos DB in the architecture. This is too much relevant today because the data is no more straight forward content with human readable entities and relations (structured), but unstructured and/or streaming too. Also the pace of the data flow, or business requirement is becoming near real-time.

See a reference architecture below:

c1Image Source: MS Docs

Here, in this blog, the context is about Traditional Data Warehouse possibility, where you will be modelling the data, specifying relationships, etc. Let us look at the definition of Data Warehouse mentioned in Oracle Docs:

“A data warehouse is a relational database that is designed for query and analysis rather than for transaction processing.”

Now let us ask the right question – Why Cosmos DB may not be apt for using as a data store in a Data Warehouse? – It is not apt, because, Cosmos DB is a NoSQL database where it is literally not easy to draw relationships between entities/tables/data. Check what MSDN blog said about this:

“Cosmos DB is not a relational database. You cannot just take your relational database and expect it to run in Cosmos DB. You could move tables of data into Cosmos, but not the relational aspects of your existing data structures.”

As of today, this is the conclusion. But we cannot say tomorrow what will happen to these concepts because Cosmos DB is becoming powerful and I am already in love with it.

You can read common scenarios (use cases) where you can use, or the companies use Cosmos DB here.

Do you have different thoughts on this? Please comment.

Getting started with Azure Databricks

Introduction

What is Azure Databricks?

Azure Databricks is the same Apache Databricks, but a managed version by Azure. This managed service allows data scientists, developers, and analysts to create, analyse and visualize data science projects in cloud.

Databricks is a user friendly, analytics platform built on top of Apache Spark. Databricks acts as an UI layer, a WYSIWYG dashboard where you can create clusters, manage notebooks, write code and analyse data without knowing the internals of the system. Apache Spark is a unified analytics engine for large scale data processing and currently it supports popular languages such as Python, Scala, SQL and R.

About the article

If you know Apache Databricks already, then a tutorial is not necessary to get started because Azure Databricks also uses the same management portal used by Databricks.

Though there are different strategies possible to create and manage Databricks projects, I have followed below flow in this article:

image

Screenshots and steps provided in this article are valid as on 20 Sept 2018. Advancement in technology happening at a faster pace so as the Azure portal upgrades. So, please be aware of any portal flow changes when you try out the same. I will try to keep this tutorial up to date.

Login to Azure Portal

You must be having at least a trial account to get started. Visit Azure home page to get one – https://azure.microsoft.com/

Step 1: Create your first Databricks workspace

First step in creating a Databricks project is by creating a Workspace.

Typical steps will be to click “+ Create a resource” à “Analytics” à Azure Databricks

image

In the workspace creation wizard, you will have to provide below details:

A. Workspace name: Give a unique name (retry until you get a green tick mark at the right. You get a red X mark because someone already took your favourite names).

B. Subscription: Choose an appropriate subscription plan, or leave the default value if you do not know what this is about

C. Resource Group: Choose an existing resource group, or give a new one. (Provide a new name if you do not know what this box is about)

D. Location: This is the data center. Select your nearest location in the dropdown, or keep the default

E. Pricing Tier: Now this is about cost so be careful. I would prefer to go with a Free trial if I am doing this for learning purpose. You can read more about the pricing tiers here.

image

Click “Create” button and wait till the workspace get created. This will take couple of minutes and you will get the notification once it is completed.

image

Once he workspace is created, you can go to “All resources” and click your newly created workspace name in the list.

image

The resource dashboard will look like this:

image

Now it is time for some action. Click “Launch Workspace” button, and you will be directed to a new browser page. You will be signed into the portal automatically.

Your Azure Databricks journey starts here.

image

From here, there are different strategies possible to execute projects. Since a full-fledged project which includes a meaningful data analysis is out of scope of this article, we will try out a simple example like querying a dataset or plotting a bar chart.

Let us load a dataset and visualize using a notebook.

For the purpose, I have downloaded a dataset from internet, which is about the literacy rate in India. You may also download a freely available one, or create a dataset of your own. We are not going to do any complex analysis in this example so this simple dataset is enough. May note that the values in the dataset are not real values. My CSV file looks like this, with first row as header row.

image

Create Cluster

For storing the data and doing processing, we need some powerful machines. Let us call it clusters and create one in this section.

On the dashboard, click on “New Cluster

I am giving the cluster a name “MyFirstCluster”. If you are good in Azure portal already then you know most of the input parameters mentioned in the page. Otherwise if you are a beginner, I suggest you to leave all the other settings ‘as it is’ and click “Create Cluster” button to proceed further.

image

It will take some time to complete the cluster creation. For me it took about 5-10 minutes. You can see the status of cluster creation in next screen.

image

Once the cluster is created, the status will change from “Pending” to “Running

image

Once the cluster is crated then we are read to upload data or creating notebooks. Let us upload the data first.

Upload data

Upload the already prepared/downloaded dataset to the newly created cluster.

Go back to the dashboard and click “Upload Data

image

In the next screen, give the dataset a name and upload the dataset. In my case I am using a CSV file with some 35 rows. Your dataset can be a bigger one but note that depending on the size of the dataset the upload and processing can take more time.

image

Once upload is completed, you can create the Notebook.

Create Notebook

A Notebook in the context is an interactive web based editor which allows data scientists, analysts and developers to write and collaborate scripts and notes to analyse and visualize.

You can either create the Notebook by clicking “Create Table” in the Dashboard screen, or as the continuation of the last step. When you click “Create Table in Notebook” button in the above screen, Databricks service will create sample notepad for you with sufficient sample code, with python as the default language.

image

Make sure that you have the cluster attached to this notepad. If you see “Detached” status at left-top side, then make sure to choose a cluster by clicking on the “detached” text. Without a cluster, you cannot run the scripts.

image

Now it is time to test the script. You can see the sample python scripts in various script boxes in the page. You can click on the play button you see on right-top side of any script snippet box:

image

You should be able to see the script getting executed and result will be displayed below in the form of a table. If there are errors, you will be provided with proper error messages which you can use to debug the script.

image

Now it is your time for experimenting and more learning.

As a bonus, let us see how to visualize the same data using a bar chart. Click on the bar chart icon. If you do not see any charts auto generated, then click “Plot Options” and play around with the parameters.

image

image

Click “Apply”, and now you can see the bar chart updated in the Notebook.

image

Happy Learning!

References:

  1. https://docs.microsoft.com/en-us/azure/azure-databricks/what-is-azure-databricks
  2. https://databricks.com/
  3. http://spark.apache.org/

SQL Server 2016 Row Level Security (RLS) Implementation

Row Level Security Capability was introduced with 2016 version SQL Server. Same is available in Azure SQL Database also as of today. This blog will detail a novice example on how to implement.

This is the planned implementation flow:

  1. About the example
  2. Prepare Sample data
  3. Create Predicate function
  4. Apply Security Policy
  5. Test Security

1. Plot

For the purpose of example, we will take the case of an imaginary Super market. Let us assume there are Supervisors assigned to each department in the shop and we want each supervisor to see only items he is responsible for.

RLS is applied on tables but in this example we will apply to a VIEW, which makes more sense as it is close to the real world scenarios.

2. Prepare sample data

Find the schema and sample data I used for the example:

Table: dbo.Employee


CREATE TABLE [dbo].[Employee](
[EmpID] [int] NULL,
[Department] [varchar](50) NULL,
[Name] [nvarchar](150) NULL,
[Username] [varchar](50) NULL
) ON [PRIMARY]

1

Table: dbo.StockByDepartment


CREATE TABLE [dbo].[StockByDepartment](
[Department] [varchar](50) NULL,
[Item] [nvarchar](100) NULL,
[UnitPrice] [money] NULL
) ON [PRIMARY]

Screenshot[2]

View: dbo.Stock


CREATE VIEW [dbo].[Stock]
WITH SCHEMABINDING
AS
SELECT e.Department, e.EmpID, e.Name, e.Username, d.Item, d.UnitPrice FROM dbo.StockByDepartment d
INNER JOIN dbo.Employee e
ON d.Department=e.Department

Screenshot[3]

3. Create Predicate function

Run this script to create predicate function over dbo.Stock view.


CREATE FUNCTION fn_SecurityPredicate(@username sysname)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN SELECT 1 AS [fn_SecurityPredicate_result]
FROM
dbo.Stock
WHERE
@username=USER_NAME();

4. Apply Security Policy

Run this script to apply security policy over dbo.Stock view


CREATE SECURITY POLICY DepartmentFilter
ADD FILTER PREDICATE dbo.fn_SecurityPredicate(Username)
ON dbo.Stock

5. Test Security

Now it is time for us to try out the applied security with various users.

Create sample users with the name you have provided in “Login” field of dbo.Employee table, and login to SSMS using it and try SELECTing the records in dbo.Stock.

For demo purpose, below code will create some sample users and grant permission to dbo.Stock view:


CREATE User paul WITHOUT LOGIN

GRANT SELECT on dbo.Stock TO paul

Now, for the sake of testing, you can use the below code:


EXECUTE('SELECT * FROM dbo.Stock') as USER='paul'

You should see filtered data like the one below:

Screenshot[4]

Latency vs Throughput

Latency is about how fast a request can travel from one point to another.
Example: For sending 100Mb of data, the API will take one hour.

Throughput is how much data can travel from one point to another in a specific time frame.
Example: in one hour, this API can transmit 100Mb of data

 

Free eBook – Cloud Application Architecture Guide

Download from here.

Explore these best practices for any cloud.

No matter which cloud you choose, certain design principles apply to ensure your app is scalable, resilient and available. Take a structured approach to architecting cloud apps using the best practices, design review checklists and reference architectures in the Cloud Application Architecture Guide eBook.

T-SQL script to create SQL Job for daily database backup

This script will create an SQL Server Agent job, which will take daily database backup to a folder with date appended in the filename.


DECLARE @job varchar(100) = 'Backup_testdb_daily' -- Name of Job
DECLARE @db varchar(100) = 'testdb' -- DB to backup
DECLARE @bakfile varchar(100) = 'd:\_temp\' + @db -- Backup file path

DECLARE @date varchar(8) = '20180720' -- Job Start date
DECLARE @time varchar(8) = '135400' -- Job run time. Eg: Run At 23rd hour

--------------------------------------------------------------------

DECLARE @cmd varchar(200) = CONCAT ('DECLARE @bakfile varchar(200) = ''' , @bakfile , ''' + ''_'' + convert(varchar(100),GetDate(),112) + ''.bak'';') +

CONCAT('BACKUP DATABASE ', @db, ' TO DISK = @bakfile');

USE msdb

EXEC dbo.sp_add_job
@job_name = @job;

EXEC sp_add_jobstep
@job_name = @job,
@step_name = 'Backup database',
@subsystem = 'TSQL',
@command = @cmd

EXEC sp_add_jobschedule
@job_name = @job,
@name = 'DB Backup Schedule',
@freq_type = 4, -- daily
@freq_interval = 1,
@active_start_date = @date,
@active_start_time = @time

EXEC dbo.sp_add_jobserver
@job_name = @job,
@server_name = @@SERVERNAME

Change Server Mode in SSAS

You can change the Analysis Services mode from Multidimensional Mode to Tabular Mode or vice versa easily by following below steps. I did this on SQL Server Analysis Services 2016 version.

Step 1: Edit msmdsrv.ini file

Go to folder X:\Program Files\Microsoft SQL Server\MSAS13.MSSQLSERVER\OLAP\Config

Change X: with your correct installation drive.

Open file msmdsrv.ini in notepad

It is recommended to take a backup first. There is a chance this folder require additional permission to edit so I would suggest you to open the file “as Administrator”

Find the tag <DeploymentMode>0</DeploymentMode>

If your current mode is Multidimensional then the DeploymentMode value will be 0 or if Tabular, then it will be 2. Change it to 0 or 2 as per your requirement.

Step 2: Restart SSAS

Open SQL Server Configuration Manager from Start menu and
Right click on “SQL Server Analysis Services” and click “Restart” in the context menu.

Finished! Try connecting to SSAS instance in SSMS to test.