Saturday, July 29, 2017

Graph Database vs. Relational Database?

graphdbGraph Databases are one of the type of NOSQL Databases with CRUD methods that expose a graph model. Graph databases are generally built for use with transactional (OLTP) systems. Graph Databases provide a novel and powerful data modeling technique that makes the data models flexible. They are not a complete replacement for relational models instead they can be used where immediate and significant practical benefit can be achieved.

Graph Databases are schema free and they lack schema oriented Governance mechanisms. When designing Graph Databases, the focus has to be on modeling the structure of the data and their interconnectivity on how different data connect to each other. Graph based data model can become very complex really quickly.

Examples of list of Graph Databases

  • SQL Server 2017
  • Neo4j
  • FlockDB
  • Pregel
  • InfoGrid
  • HyperGraphDB
  • SAP HANA

The key terminologies in a graph database are:

  • Nodes - These are basically Entities
  • Properties - Information about the node or information about the relationship
  • Edges - Relationships between Nodes

A simple example is an Employee Node. An Employee can have a relationship with their Manager, an employee can have relationship with colleagues and relationship with their own subordinates or relationship with employees in other departments. Nodes can have as many relationships as required to describe the data. Relationships can be Unidirectional or Bidirectional and there is no limit on the relationships per node.

When to use a Graph Database vs. Relational Database?graphdb2

a) When multiple relationships need to be described e.g. Social relationships, Networks, Error logging, Sensor data, Logistics data. These are examples where the relationships don't truly exist but needs to be described to understand the data.
b) Using for non-analytical data e.g. Authorisation, User preferences, Non frequent changing variables if stored in a database table.

If you have high performance requirements especially using SQL Server 2017 you need to use functions to parse JSON data and it will be a row by row execution but indexes can be used to improve performance a bit but still recommend to use relational in those scenarios. If you have Validation and Integrity requirements and if you need them in the backend, then relational is the best choice.

SQL Graph Architecture

Technorati Tags: ,

Wednesday, July 12, 2017

Microsoft Azure Stack - Azure On-premises

Microsoft have announced the release of Azure Stack which allows enterprises to run Core Azure Services in their Corporate Data Centers.

Microsoft announced at its Inspire conference today, partners like Dell EMC, HPE and Lenovo will start shipping their integrated systems with Azure Stack in September 2017.

This will help lots of Enterprises who couldn't use Azure services on Cloud due to compliance or regulatory reasons.

More information on Azure Stack

Azure Stack Development Kit

Azure Stack Blog

Azure Stack Ideas for improvements

Nice video on how to bring Azure to your Data Center.

Sunday, July 02, 2017

Interactive Azure Platform Big Picture

Currently I am working on a project which utilises a whole heap of Azure services and it’s really handy to have all links in one place related to the Azure Services. The interactive big picture solves that problem and is available under https://azureplatform.azurewebsites.net/en-us/.

Download PowerPoint version - https://azureplatform.azurewebsites.net/Content/azure-platform-overview.pptx

AzureBigPicture1

Technorati Tags: ,

Monday, May 15, 2017

Power BI Premium

As part of the Data Amp event hosted by Microsoft this month, they made an announcement about Power BI Premium. Currently with SQL Server 2016 Reporting Services, you can upload Power BI Desktop reports to the SSRS Web Portal (Power BI Desktop needs to be installed on consumer devices)

With the January 2017 preview release of SSRS, Microsoft announced the ability to upload Power BI Desktop reports to the SSRS Web Portal along with serving them within the web browser without the need for Power BI Desktop installed on consumer devices.

Power BI Premium introduces the ability to manage Power BI reports on-premises with the included Power BI Report Server. Power BI Report Server includes all the features of SQL Server Reporting Services including the ability to publish and serve Power BI Desktop reports. Power BI Report Server will be generally available late in the second quarter of 2017.

So with the release of Power BI Report Server, SSRS in SQL Server 2017 will support only Paginated and Mobile Reports while Power BI Report Server will support Paginated, Mobile and Power BI Desktop reports. Good news is Power BI Report Server also supports Custom Visuals.

With Power BI Premium, if you want to distribute and embed Power BI Reports in your applications you no longer require to purchase per-user licenses.

Power BI Premium Pricing - https://powerbi.microsoft.com/en-us/pricing/

Power BI Premium Whitepaper - https://aka.ms/pbipremiumwhitepaper

Power BI Premium Calculator - https://powerbi.microsoft.com/en-us/calculator/

Sunday, May 14, 2017

Creating a new team and channel in Microsoft Teams

I have been using Slack as a collaboration platform for projects and have started using Microsoft Teams recently and really impressed with the features. Microsoft Teams is a really handy tool if you are working in projects and have to collaborate with a whole group of people.

Let us see how to create a new project and channel in Microsoft Teams.

a) Log on to Microsoft Teams [https://teams.microsoft.com]

b) From the Microsoft Teams account home page, click on the Create a Team link

TeamCreation1

c) Type the team name and description

TeamCreation3

d) Click on the “…” beside the “My First Team Project” team and click on “Add Channel” to create a new channel.

TeamCreation4

d) Provide a name and description to the channel.

TeamCreation5

e) Now let us create an integration using connectors within Microsoft Teams. In this scenario I would like get the RSS newsfeed for Microsoft Teams to be served in the “MS Teams Blog Feed” channel.

f) Click the “…” beside the “MS Teams Blog Feed” channel and click on “Connectors”. Microsoft Teams provides a whole range of connectors for integration with other SaaS applications.

Connector1

Connector2

g) Click on “RSS” and add the MS Teams Blog URL and frequency to pull the feeds from the blog.

Connector3

h) Click on “Save” and you can notice a connector configured for the channel.

Connector4

i) Now you can see the feed items listed under the MS Teams Blog Feed. Now this can be viewed by your team members who have permissions to the team and channel. Really useful to share important blog feeds with the rest of the team related to your project.

Friday, April 14, 2017

Creating a .NET Core project using Command-Line Interface Tools

I love IDEs but normally you don’t get to learn a lot of things about internals if you use the IDE. How about creating a .NET project using notepad? Sounds interesting….

Yes, let’s create our first .NET Core project using Command-Line Interface tools without any IDE.

Open the Command Prompt and navigate to the folder path where you would like to create the new .NET Core project.

Create a new folder named “NETCore”.

CmdLine1

Create a new folder named “FirstCoreApp” and navigate to the folder

CmdLine2

Type “dotnet new”

CmdLine3

You can see the C# project got created successfully with 2 files. Program.cs and Project.json Note: Project.json will be seen only if you have .NET Core Tools preview 2 and below. From .NET Core Tools preview 3 onwards you will see FirstCoreApp.csproj file.

CmdLine4

Program.cs file contents will be similar to the contents when creating a console application using the VS IDE

You can build the application at command line using the command “dotnet restore”

CmdLine6

You can then run the application using the command “dotnet run”

CmdLine7

Now we have successfully created a .NET Core application just using Command Line Tools without any IDE.

Technorati Tags: ,

Sunday, April 09, 2017

Create KPIs using SSRS 2016

Pretty much every organisation would love to store KPIs (Key Performance Indicator) in a centralised portal and manage access. SSRS 2016 allows user to create KPIs and track progress against those KPIs through the new Web Portal. Visualisations can also be set for those KPIs.

In SSRS 2016 when you create KPIs you can either set values manually or as a Dataset field. Let us see how to do both the options.

Set Manual KPIs

a) Navigate to the SSRS 2016 Web Portal and click New > KPI

KPI1

b) Type KPI name as Sales 2017 KPI, Description of the KPI, data format for the KPI, select value as Set Manually, Type 200 as value, select goal as Set Manually, Type 1500 as Enter goal, select status as Set Manually, type –1 as status and select the visualisation you like and click “Create”

KPI2

c) The new KPI will be displayed as shown below.

KPI3

Set KPIs using Dataset

a) Create a new table and load data using the script below on an existing database.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[KPIs](
    [KPI_Name] [varchar](100) NOT NULL,
    [Target] [int] NOT NULL,
    [Progress] [int] NOT NULL,
    [Status] [int] NOT NULL
) ON [PRIMARY]

GO

INSERT [dbo].[KPIs] ([KPI_Name], [Target], [Progress], [Status]) VALUES (N'Sales KPI', 1500, 200, -1)
GO

b) Create a new dataset using SQL Server 2016 Report Builder. Note: Make sure to create the connection first in the SSRS Web Portal.

KPI4

KPI5

Select “connKPI” and click Open

KPI7

b) Click on “Create” button

KPI8

c) Expand “Tables” tree and select “KPI” table and click the save button on the menu bar and provide the name “KPI” as dataset name.

KPI10

d) Now your dataset has been successfully created. Now navigate to your SSRS portal. URL format: https:// (use it only if you have configured SSRS for HTTPS) or http://<servername>/Reports and you can see the the dataset named “KPI”

KPI11

e) Now follow the same steps as what you followed for creating a manual KPI instead of set manually select “dataset field” as shown below.

KPI12

Monday, April 03, 2017

Monitoring Microsoft Flow runs

Once a flow has been created how do we know that it successfully ran or failed or it is still running. You can monitor the flow runs by clicking the icon as shown below.

AddtoTwitterFlow16

You can see all runs including success, failure, running and cancelled along with checks (no new data) and failed checks.

AddtoTwitterFlow17

Technorati Tags: ,

Sharing Flow with Teams using Team Flows

When we create a flow using Microsoft Flow, the person who created the flow only will be able to maintain it. Team Flows will allow the person who created the flow to assign additional owners to maintain the flow. Team Flows is currently in “Preview” only.

a) Click on “Invite another owner” icon beside the “Blog post notification to Twitter” as shown below.

AddtoTwitterFlow14

b) Add another owner who is an user as part of your office 365 subscription.

AddtoTwitterFlow15

Note: Adding another owner will allow them to edit, update and delete the flow. They can also add additional owners to this flow along with viewing flow history. Also everyone listed as owners will be able to access all embedded connections and can use them in that flow only.

Sunday, April 02, 2017

Creating my first flow using Microsoft Flow

Scenario:

Whenever I write a blog post, I would like a tweet posted on my twitter for my followers. Initially I used to do that manually and then I was using Zapier and now I wanted to move that to Microsoft Flow. Let us see how to do that.

a) Navigate to https://flow.microsoft.com/en-us/ and sign-in.

b) Click on “Create flow from blank”

AddtoTwitterFlow1

c) Choose “RSS” as new blog posts will be available on RSS newsfeed

AddtoTwitterFlow2

d) Choose “When a feed item is published” trigger.

AddtoTwitterFlow3

e) Type the blog’s RSS URL. I have typed my blog’s RSS URL - http://feeds.feedburner.com/CodeSpot and click on “Next Step”

AddtoTwitterFlow4

f) Now I need to post a tweet on twitter once I publish my blog post. Choose the “Twitter” action and “Post a tweet” trigger.

AddtoTwitterFlow5

g) Now you need to sign in to create the connection between Microsoft Flow and your Twitter account.

AddtoTwitterFlow6

h) Provide your twitter credentials and once done you can configure your tweet text with some dynamic content.

AddtoTwitterFlow7

AddtoTwitterFlow8

g) Provide a name to you flow. In this scenario I have given the name “Blog post notification to twitter”

AddtoTwitterFlow9

h) Now my first flow has been created and started running.

AddtoTwitterFlow10

i) You can see the flows under “My flows”

AddtoTwitterFlow11

Update on 03/04/2017

I have updated the above flow to include bitlink to create shortened URL of my blog post URL. After adding it my flow looks like the below.

AddtoTwitterFlow13

Now, I have posted this blog post and below is my twitter output from Flow. Great to see that I have done this without writing a single line of code.

image

Updated twitter output with bitlink

AddtoTwitterFlow18

What is Microsoft Flow and how it differs from Logic Apps?

Microsoft Flow is a new cloud based workflow application that will allow business users to automate routine tasks through applications. Other cloud services like IFTTT (If This Then That) and Zapier have similar functionality and also work across many services. Flow also comes with a list of pre-defined templates. I have been using Zapier until now and moving my zaps to Microsoft Flow.

Flow

Flow vs. Logic Apps

  • Both Flow and Logic Apps are configuration based Integration services which allows users to automate their tasks including integration with SaaS APIs. Flow is built over Logic Apps.
  • Flow is targeted against business users while Logic Apps is targeted against IT Pros/Developers.
  • Flow is used for small scale integration while Logic Apps are used for mission critical applications.
  • Develop a Flow in production directly while with Logic Apps you can use Visual Studio, Source Control, testing, support and Automation.
  • Flow has standard security practices like data sovereignty, encryption at rest for sensitive data, etc. while Logic Apps has the security assurance of Azure.

In general if it is a simple integration and doesn't require Enterprise capabilities then go with Flow or use Logic Apps for simple/complex integrations requiring Enterprise capabilities (DevOps, Security, Source Control) and if functionality is not available in Logic Apps then use Azure Functions to write your own transformation code.

Pricing - https://flow.microsoft.com/en-us/pricing/

Flow Templates - https://flow.microsoft.com/en-us/templates/

Feedback & Ideas - https://powerusers.microsoft.com/t5/Flow-Ideas/idb-p/FlowIdeas

Release Notes - https://flow.microsoft.com/en-us/documentation/release-notes/

Logic Apps and why do we need them?

Logic Apps is a integration Platform as a Service (iPaaS) to automate and orchestrate integration workflows and provides connectivity to other Software as a Service (SaaS) applications. It provides the ability to build powerful enterprise grade integration solutions. The Visual designer allows any technical user or developer to model and automate business processes.

Why do we need Logic Apps?

Integration with Enterprise, custom or Commercial Off-The-Shelf (COTS) APIs are always considered as complex and time consuming. As business moves towards digitization integrating with Legacy systems and SaaS applications becomes extremely important. With Logic Apps, API Integration is made easy.

Logic Apps has large number of connectors for SaaS APIs and on-premise APIs. The connectors are added regularly to Logic Apps. Full list of the connectors can be found here.

LogicAppsConnector

Advantages of Logic Apps

  • Logic Apps are metadata driven. Metadata is exposed in a format using Swagger.
  • Logic Apps has a simple User Interface and developers can get started using Logic Apps within minutes. Logic Apps are built for faster and agile development. Pre-defined templates allow developers to start developing workflows quickly.
  • Scaling APIs based on the demand has been really hard historically. With Logic Apps service being on Azure and is "serverless" you can meet the demands of your client needs easily. Logic Apps will handle scale, availability and performance automatically to meet demand.
  • Exception Handling is really key for any Integration solutions and Logic Apps provides tools and patterns to make sure that the integrations are robust and resilient against failures using features like retries, scopes, and results.

Pricing

Always keep an eye on the pricing with Azure solutions. The details around Logic Apps pricing is here.

Ideas & Feedback

You can also provide your feedback or ideas on Logic Apps here.

Release updates

Microsoft updates Logic Apps with new features and enhancements on a regular basis and release updates can be found here.

Monday, March 27, 2017

SQL Server vNext – Get Data experience in SSAS Tabular Models

The next version of SQL Server, SQL Server vNext introduces the same experience of “Get Data” like what we see in the Power BI Desktop and Excel 2016. This is mainly applicable for tabular models when the compatibility level is set to “SQL Server vNext (1400)”. Great to see Microsoft trying to unify the UI across different toolsets.

getdatadlg

https://blogs.msdn.microsoft.com/analysisservices/2016/12/16/introducing-a-modern-get-data-experience-for-sql-server-vnext-on-windows-ctp-1-1-for-analysis-services/