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.

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.

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.

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.

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

#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.

#4) Creating Tabular Project

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.

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

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

#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.”

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.

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.

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.

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

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.“

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.

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”.

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

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“

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.

#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.

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.

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.

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.

#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.

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)

---