On-Prem SQL DB Tabular model to Azure Analysis Services (AAS)

On-prem SQL DB Tabular model: This article is very much useful and also complicated because when we use On-prem SQL Database to create tabular model and then deploy that tabular model to Azure Analysis Service (AAS) then, minor setting or gateway configuration changes will not let you deploy your tabular model to AAS.

I will guide you with all the steps from creating to deploying what things and gateway are needed. Please read this article carefully with all the steps included. Also, if you are still not able to deploy the model then you can comment down your concern below, so that we can help you.

#1) Prerequisite

  1. Azure Analysis Services (AAS): QuickStart – Create an Analysis Services server in Azure portal | Microsoft Learn
  2. On-Prem SQL Server: SQL Server 2022 Download, Install, Connect Using SSMS Tool (kingfishertechtips.in)
  3. On-Prem Gateway: Install an on-premises data gateway | Microsoft Learn
  4. Visual Studio with SSDT Tools: SSDT Tools (Reports, Tabular & Cubes Model) Installation (kingfishertechtips.in)

Note: Make sure that your AAS, On-Prem Gateway & Azure Gateway are in same regions. Azure Gateway-We will discuss below on this. Once you have all the required details which is mentioned above you can follow the below steps accordingly:

#2) Setup Azure Gateway

Step 1: Go to Azure Portal and in the search bar search for “On Premises data gateway” and click on that, as you can see in the below image.

image 101

Step 2: Click on Create on-premises data gateway and proceed further. If you have not installed the On-premises Gateway for your SQL Server than you can’t create the azure on-prem gateway.

image 107

Step 3: Here we will get option to create gateway. Now, here 1 & 2 you can provide any name according to you understanding. But if it comes to 3 & 4 Select the region in which you On-prem Gateway is involved.

Understand in my case if you see I am selecting Region as “West Central US” because my On-prem gateway for SQL DB is in that region, so the value is automatically populated as shown “Gateway_Installed_On_PC”. Once done click on Review and Create.

On-prem SQL DB Tabular model
Note: Don’t get confused with On-prem gateway Azure and On-prem Gateway for your SQL Server, both are different but required to be matched, as you can see in the above image under Installation Name: Gateway_Installed_On_PC is my SQL On-prem Gateway, so this is way they will communicate.

Step 4: After Gateway created. Now, we will map that Azure Gateway to Azure Analysis Services (AAS). Just go to your Azure Analysis Services (AAS) Instance and follow the steps.

In the left PAN you can see On-prem data gateway click on that and you can see in the right the Region and Once you select the region the Gateway with that region will be automatically populated.

Screenshot 2023 07 15 055638

Step 5: Once done click on “Connect Selected Gateway” at the top and you can see that the Gateway is Connected.

image 103

#3) SQL On-prem Gateway

Now we will login to our SQL On-prem Gateway and cross verify the connection is succussed or not? Open On-prem Gateway and sign-in with your credentials and you can see that in my case the newly created “Azure-onprem-gateway” is reflecting here. So, all this above setting and configuration matches then we are good to go for deployment of the Tabular model.

image 104

#4) Creating Tabular Project

image 56

Step 2: Search for “Analysis Tabular Project” and you can see that below this option will be populated and selected the highlighted one mentioned in the below image.

image 59

Step 3: Give a Project name to this, as you can I have mentioned as ” TabularProjectAAS” and then click on “Create”

image 61

Step 4: Now here select the option depending on the requirement, in my case I will go with “Integrated workspace” and select the highest compatibility level and click on OK

image 63

#5) Build Tabular Project:

Step 5: Once done you will land on this the homepage, In the right side “Tabular Model Explore” will be there, now here we need one data source so I will >>right click on the “Data Source” >> click on “New Data Source”. Here I will use SQL Data Base, so click on “Database” >> then “select SQL Server database” and click on “Connect.”

image 76

Step 6: Give your “SQL Server name with PC/VM name” name, you can copy the server name from SSMS and then click on OK.

image 75

Step 7: Here I am using SQL Server Authentication so I will select Database and provide the required credentials of your SQL Server. Once done click on connect.

image 93
Note: I can use Windows as well, because my SQL Server is installed in this VM, so windows will work fine for me, but if you’re accessing this SQL Server from other VM, then you have to use Database authentication (SQL Server Credentials)

Step 8: Now select the “DATABASE ” you want to use to create the Tabular model. In my case you can see that I have selected the database, currently I have only multiple Database, but I will use “movie” Database.

image 78

Step 9: Once done, again right click on the “DATASOURCE” and click on “Import New Table

image 80

Step 10: Now all the Tables will be visible here as you can see, but I will select by clicking “Select Related Tables” and then click on “Transform Data.

image 77

Step 11: Here I will make some changes in the table just to confirm it should not be same. As you can see that I have changed “CustomerID” into “CustomerID_New”, also I have made some other changes here by changing the column header name.

image 81

Step 12: I will make one more changed here by deleting the “ParentProductCategoryID” as you can see it is selected and then go to “Column” click on “Remove Column”.

image 79
Note: I am making just minor changes in this Project just to show the how this SSDT tools works.

Step 13: Once all changed are made just go to “Home” click on “Close & Apply.

image 82

Step 14:  All the changes will take effect and at the right side you can see that my tables are procced and it is visible, now  click on “Close

Creating Tabular Project

Step 15: If you want to link one table to other, then click on the below “Diagram” icon then you will see the flow of the tables.  Now we are good to deploy the model to SSAS.

image 84

#6) On-prem SQL DB Tabular model Deploy

Step 16: To deploy first we have to go to “Solution Explorer” by default it will be there, but if it not showing then you can go to “View Option” at the top and click on “Solution Explorer” then it will appear at the right side.

image 88

Step 17: Right click on the “Project Name” and then click on “Properties” at the right side one Dialog box will appear. In this box under “SERVER” specify the Analysis server name, as you can see that I have specified. Now once done click on OK.

On-prem SQL DB Tabular model
Note: If you have Replica of AAS, then giving “Server name” will not work some time, So, you should Copy “Management server name” and use it here!1 2

Step 18: Again, Right click on the “Project Name” and then click on “Deploy“, then it will prompt automatic authentication and then it will start deploying.

image 89

Step 19: Once done the deployment process will start and it will take some time to succussed. Once done you will get this SUCCESS notification as you can see in the below image. Also, if you see in the below output the time and deployment is mentioned.

image 90

#7) Connect to ASS:

Step 20: Now I have logged in to my Azure Portal and you can see the Model which I have just Deployed into AAS.

On-prem SQL DB Tabular model

Conclusion:

As we have seen in all the above steps on how to create Tabular Model and Deploy it to AAS Server. Here we have seen only for AAS, but if you want to do other Server then you have to follow the same steps at last just mention the server’s name to deploy it on particular server if you don’t want to deploy it on AAS. Any concern please comment down below for more Info.


SQL Bi Links:

  1. SQL Server 2022 Download, Install, Connect Using SSMS Tool (kingfishertechtips.in)
  2. Creating Tabular Project & Deploy Model To Analysis Service (kingfishertechtips.in)
  3. Deploy Tabular Project To Azure Analysis Service – SSDT Tool (kingfishertechtips.in)
  4. SSRS/PBIRS – Install And Configure The Report Server On-Prem (kingfishertechtips.in)
  5. Could Not Load Folder Contents In Portal URLs – SSRSPBIRS (kingfishertechtips.in)
  6. SSRS/PBIRS – Install And Configure The Report Server On-Prem (kingfishertechtips.in)
  7. SQL Server 2022 Download, Install, Connect Using SSMS Tool (kingfishertechtips.in)
  8. Capture Fiddler Trace for Debugging the Network Traffic (kingfishertechtips.in)
  9. On-Premises Gateway: Configure/Map SQL DB To Power Bi (kingfishertechtips.in)
  10. Fixed Column/Row Header Make Visible In Any Report (kingfishertechtips.in)
  11. Self-Signed Certificate: Configure SSRS/PBIRS With SSL (kingfishertechtips.in)
  12. Create Local User Account/Local Admin User In Windows 10/11 (kingfishertechtips.in)
  13. Login With AAD/Domain/Microsoft Account To Azure VM’s/RDP (kingfishertechtips.in)

Vickey Rajpoot
Vickey Rajpoot

Hello there! I'm dedicated Microsoft Data & AI Engineer at LTIMindtree, where I thrive technology into actionable insights. Dive into my world by visiting my YouTube channel & Webiste, "Kingfisher Tech Tips".

Articles: 71

3 Comments

  1. Woah! I’m really enjoying the template/theme of this site.
    It’s simple, yet effective. A lot of times it’s difficult to get that “perfect balance” between user friendliness and appearance.
    I must say you’ve done a awesome job with this. Also, the blog loads very quick for me on Chrome.
    Superb Blog!

Leave a Reply

Your email address will not be published. Required fields are marked *