Getting Started With Data Analysis Using SSAS Tabular Modeling In Excel - Part One

-
As we all know, SQL Server Analysis Service (SSAS) is an analytical data engine used in decision support and business analytics. It provides enterprise-grade semantic data models for business reports and client applications, such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
We will try to implement the same workflow to show how we can do data analysis in SSAS. I have divided this in two parts,
Let’s get started with this example.
Prerequisite
  • SQL Server (SSMS and SSAS Tabular Model Configured) - here we have used SQL Server 2017 Developer Edition.
  • SSDT (SQL Server Data Tools) - we have used SSDT for visual studio 2017
  • MS EXCEL
NoteIt is very important to know the compatibility of SQL Server version that we will use. Please see this Microsoft Article for compatibility details.
Data
We will use “Adventure Works” Database to develop sample tabular model. We can download this database backup file from here.
In the below mentioned tables, we are going to create the tabular model.
Tables
Description
Customer
This table contains information related to customer. i.e. name, address etc.
Person
This is more specific information related to Person
Product
This table contains Product details.
DemoSalesOrderHeaderSeed
This table contains Sales Order Header information. i.e. date, customer info etc.
DemoSalesOrderDetailSeed
This table contains Sales  Order Detail.
Tabular Modeling
Now, we will create a Visual Studio Solution for the Tabular model.
  • Go to the New Project. 
  • Select “Analysis Services Tabular Project” and named it “SSASTabularExample”.

It will open the Tabular model designer. Give “localhost” in “Workspace Server” and compatibility level 1400 as we are using SQL Server 2017. Click okay.
  • Then, it will open a popup for load data from various sources. Here select “SQL Server Database”.
  • Then, will need to provide server information and credentials.

We can see that it will provide the list of All SQL Server object tables, views, stored procedure etc. Here select required tables that I have already mentioned above and click on load button.


We can see data processing is successfully completed as in the below screenshot.


Right Click on Data Source in Tabular Model Explorer and view data that are in memory.

Relationship
Now we define one to many relationships between data. So, we can enable bi-directional cross filters in tabular model and no need to write custom DAX formula for filter data.
Click Diagram View in tabular model.


Right Click a table heading or column, and then click Create Relationship.


Please see this detail article about relationships by Microsoft.
Measures
A measure is a calculation on entire column using DAX formula for reporting purposes; i.e. product count, customer count, sum of product price etc.
Click Model View in tabular model and We will add below Measures in Model,
  1. Distinct Count of Customer:=DISTINCTCOUNT([CustomerID])  
  2. Total of OrderQty:=SUM([OrderQty])  
  3. Distinct Count of Product:=DISTINCTCOUNT([ProductID])  
To add measures click on the column for which we want to create the measure, and then click the Column menu as below,

Please see this detail article about measures by Microsoft.
Hierarchies
Hierarchies are metadata that define relationships between two or more columns in a table and Hierarchies can appear separate from other columns in a reporting.
Here we create date hierarchy to show usage of hierarchies in tabular model so we have two more column month, year based date in “DemoSalesOrderHeaderSeed” table.

Click on Diagram View in a table window, right-click on a column, and then click Create Hierarchy.

Please see this detail article about hierarchy by Microsoft.
Roles
As we know, Roles define member permissions for a model. Members of the role can perform actions on the model as defined by the role permission. Here we define two roles, tabular user who has read permission on model and tabular admin who has admin permission on model.
Right Click on roles in Tabular Model Explorer and add roles on model.

Please see this detail article about roles by Microsoft.
Now we have successfully built Tabular Model and are ready to deploy it to server. There are few simple step for deployment as mentioned below.

Go to solution explorer, right click on model. Click on deploy. Let's connect analysis server and we see deployed tabular model as in the below screenshot,


Conclusion
In article, we have learned about how we can create SSAS tabular model and setup measure, roles, calculated columns, and hierarchies in tabular modeling.
In the next part, we will see how business users can do data analysis on SSAS tabular model in excel.
Hope you will love this article

Comments

Post a Comment

Popular Posts

Contact Application Using ASP.NET Core Web API, Angular 6.0, And Visual Studio Code - Part One

-
Image
We will develop a contact application using ASP.NET Core Web API, Angular 6.0, and Angular Material UI as shown in the below screenshots.  We will use Visual Studio code (VS code) for the development editor.  I have divided contact application development into two articles, Contact Application using ASP.NET Core Web API, Angular 6.0, and Visual Studio Code Part – 1 In the article, we will set up ASP.NET Core Web API project, and develop the Web API controller for contact CRUD operations. Contact Application using ASP.NET Core Web API, Angular 6.0, and Visual Studio Code Part – 2 In the article, we will setup Angular 6 within ASP.NET Core Web API Project, and develop the contact form & list component using Angular Material UI. So, starting with this article, first, we will see how we can create ASP.Net Core Web API Project. We have the following prerequisites, Visual Studio Code (VS code) editor Please see this  article  to see how we can setup Visual Stud
Read more

Contact Application Using ASP.NET Core Web API, Angular 6.0, And Visual Studio Code - Part Two

-
Image
In this article, we are going to learn about how we can setup Angular 6.0 in ASP.Net Core Web API project and develop the contact list & form component using Angular Material UI. In the Part 1 article, we set up ASP.NET Core Web API and developed the API for contact CRUD operation. Please go through  Contact Application using ASP.NET Core Web API, Angular 6.0, and Visual Studio Code Part 1  once before proceeding with this article. To setup Angular 6.0 in the project, first, we have to install NodeJS in the development environment. We can download NodeJS installer from  NodeJS website . and then check the version on NodeJS as below: Now, let us globally install Angular CLI package by enter “npm install –g angular/cli@6.0.0”, as shown in below screenshot Scaffold Angular To scaffold Angular, enter “ng new Contact-App --skip-install” command in VS code terminal as below. Here, --skip-install option is used to skip installation of the npm packages. And Contact-
Read more

Send an Email Reminder Notification Based on an Expiration Date using Power Automate

-
Image
Introduction    In this article, we are going to create an email reminder notification based on an expiration date using Power Automate. We will create a flow that’s run daily that reads & filters data from a SharePoint list that has list items that expire in the next 5 days. Then it will send an expiration notification email to a particular user.   SharePoint List - "ProductSales"     Columns  Type  Title  Text  ExpiryDate  DateTime   Let’s log in to Office.com with office 365 credentials. Go to the “Power Automate” tile and click on it.   It will be redirected to Power Automate Page. Click on Create button from left navigation.   Click on the scheduled flow tile. It will open the popup.       Create Recurrence Flow Enter the flow name and select a specific time when we want to schedule this workflow. Then, click on the create button. It will open the Edit Flow page where we can see a Recurring Action. Now we are f
Read more

MySQL Data Access API Development Using Express.JS, Node.JS

-
Image
In this article, we are looking into the introduction of Node.JS and Express.JS and then, I am going to create a simple MySQL data access API using Express.js, NodeJS. As we all know, Node.JS is the most trending open source technology that’s directly or indirectly partitioning into advanced web development, and let’s have lookup what is Node.JS? Node.JS Definition Node.JS is a platform that provides environment (outside the web browser) to execute JavaScript and which is built on Chrome's V8 JavaScript engine developed by Ryan Dahl. Why Node.JS? NodeJs processing model depends on a single thread with event looping. When clients send HTTP requests to NodeJs server, the Event loop is woken up by OS. It will pass the request and response objects as JavaScript closures to worker functions with callbacks. For long-running jobs like I/O operations running on non-blocking worker threads, when the job is complete, responses are sent to the main thread via a callback
Read more

Chat Application using Angular 8, Asp.net Core 2.2.0, Signal R 1.1.0

-
Image
In this article, we are going to create a simple chat application using Angular 8, Asp.net Core 2.2.0, Signal R 1.1.0 as shown below: We install above all prerequisite following in our development machine: Prerequisite .Net Core SDK 2.2.0  – download from  here . Nodejs 10.15.3  – download from  here . Angular CLI 8.0  – Install angular command package by executing this command: “ npm install -g @angular/cli@8.0.0 ” Setup Asp.net Core with Signal R Project: open a command prompt and enter create asp.net core web project as below: Install the following NuGet package in our project: Microsoft.AspNetCore.SignalR Microsoft.AspNetCore.SpaServices.Extensions Call SignalR in the “Startup.ConfigureServices” method to add SignalR services: services.AddSignalR();   Create MessageHub Class: SignalR makes real-time client-to-server and server-to-client communications possible and it will call methods to connect clients from a server using Sign
Read more

ReactNative FlatList

-
Image
Introduction: React Native List show has different way to represent, react native listview either FlatList or SectionList. Flatlist is term used for static data just like array of list to view. Process: index.js import React, { Component } from 'react'; import { AppRegistry, FlatList, StyleSheet, Text, View } from 'react-native'; export default class FlatListBasics extends Component {   render() {     return (       <View style={styles.container}>         <FlatList           data={[             {key: 'Devin'},             {key: 'Jackson'},             {key: 'James'},             {key: 'Joel'},             {key: 'John'},             {key: 'Jillian'},             {key: 'Jimmy'},             {key: 'Julie'},           ]}           renderItem={({item}) => <Text style={styles.item}>{item.key}</Text>}         />       </View&
Read more

Getting Start With Data Analysis Using SSAS Tabular Modeling In Excel - Part Two

-
Image
In this article, we are going to see how business users can utilize the tabular model for data analysis and visualization in Excel. This article depends on Part I. So, it would be great if you first go through this: Getting started with data analysis using SSAS Tabular Modeling in Excel - Part I We will see how we can create SSAS tabular model and setup measure, roles, calculated columns, hierarchies in tabular modeling. Now, we created a Tabular Model and deployed it on the analysis server. Let’s open Excel. Go to Data Tab, click on Get Data Option from a ribbon. Go to From Database, Analysis services. It will open Data connection wizard, enter server & credential details. Click on next. Select Tabular Model Database. Click on next. Now, Save Data Connection and Click on finish. Go to Insert Tab, Click on Pivot Table option from the ribbon. Select External Data Source. Click on choose connection. We can see our saved data connection
Read more

Contact application - Upgrade Asp.net Core 2.0 to 2.1

-
Image
In this article, we will look into steps for contact application - upgrade Asp.net Core 2.0 to 2.1, please see my previous articles about how we developed contact application: ContactApplication using ASP.NET Core Web API, Angular 6.0, and Visual Studio CodePart One: In the article, we will set up ASP.NET Core Web API project, and develop the Web API for contact CRUD operations. ContactApplication using ASP.NET Core Web API, Angular 6.0, and Visual Studio CodePart Two: In the article, we will setup Angular 6 within ASP.NET Core Web API Project, and develop the contact form & list component using Angular Material UI that will consume Web API which we have created in Part One. ContactApplication Azure Deployment: In this article, we are going to deploy/host a contact application with Visual Studio code to Azure web apps. Please note:   Previously we have used Asp.net Core 2.0.3 for contact application, now upgrade to Asp.net Core 2.1.5. This is download link for Dot
Read more

Send Email With SharePoint Lookup Columns Data Using Power Automate

-
Image
  Introduction   In this article, we are going to send an email with SharePoint Lookup Column data using Power Automate. We will create a flow that’s run weekly that fetches data from a SharePoint list which contains lookup columns. We will use the “Apply each” action to get lookup records.   SharePoint Parent List - "ProductSales"   Columns Type Title Text ExpiryDate DateTime SalePersonName LookUp (SalePerson List)   SharePoinChild List - "SalePerson"   Columns Type Title Text   In the above lists we have SalePersonName column which is actually the Title column from SalePerson List. When we “Parse Json” data in the above type of data we get two title fields in the dynamic column section. And sometimes workflow fails due to a null record in lookup column. Let’s see the data in the list as below,     Log in to Office.com with Office 365 credentials. Go to the “Power Automate” tile and click on it. It will be redirected to the Power Automate Page. Click on the Create
Read more

Typescript Basics

-
Nowadays, there are so many JavaScript frameworks (Angular, React) written and used to develop frontend UI by Typescript. It very important to every developer has very basic knowledge about Typescript. So, in this article we will look into Typescript Basics for getting started with it. Pre-requisite:   Basic knowledge of JavaScript, OOPs concept. What is Typescript? The Typescript language is a typed super set of JavaScript, means any program written using Typescript Language will be compiled into plain JavaScript. What does Typescript solve over JavaScript? And Why Typescript Language is so much popular among Developers? Types & Equality Scope OOPs Implementation Module management Installing Typescript: There are two basic ways to install Typescript: Via NPM (Nodejs Package Manager) Typescript’s Visual Studio plugins. Please this  link  for more information For via Npm, enter this command in command prompt: npm install -g typescript   save fi
Read more