Creating Tabular Project using SQL Data Base and Deploying to Analysis service (SSAS)

Creating Tabular Project: In this article we will see what process is to CREATE Tabular Model by using the data source as On-prem SQL Database and finally publish it to On-prem Analysis Services (SSAS) from Visual Studio. Follow each steps easily by referring the images mentioned with all the steps and deploy your model to AAS. For more details visit Microsoft Website: SQL Server Analysis Services overview | Microsoft Learn

Perquisites:

1. SQL Server Data Base: Analysis Services (SSAS) Installation Setup Guide With SSMS (kingfishertechtips.in)
2. Analysis Services (SSAS):SQL Server 2022 Download, Install, Connect Using SSMS Tool (kingfishertechtips.in)
3. Visual Studio with SSDT Tools: SSDT Tools (Reports, Tabular & Cubes Model) Installation (kingfishertechtips.in)

Creating Tabular Project:

Step 1: Open Visual Studio and click on “Create a new project“. If you have not installed Visual Studio, then you can follow the below document to install. Link: SSDT Tools (Reports, Tabular & Cubes Model) Installation (kingfishertechtips.in)

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

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.

Deploy Tabular Project:

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.

Connecting using SSMS:

Step 20: In the homepage you can see “Connect” option at the top, just click on that and select SQL Database.

Step 21: Now give the Tabular Model name as you can see in my case I have given “PCTAB”

Step 22: Now I have successfully connected to SSAS. Just expand the Database option and you can see that my Tabular Model has successfully deployed to SSAS.

Conclusion:

As we have seen in all the above steps on how to create Tabular Model and Deploy it to On-prem Analysis Server (SSAS). Here we have seen only for SSAS, but if you want to do other Server then you have to follow the same steps at last just mention the server’s name while deploy it on particular server, if you don’t want to deploy it on SSAS. 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. SSRS/PBIRS – Install And Configure The Report Server On-Prem (kingfishertechtips.in)
3. Could Not Load Folder Contents In Portal URLs – SSRSPBIRS (kingfishertechtips.in)
4. SSRS/PBIRS – Install And Configure The Report Server On-Prem (kingfishertechtips.in)
5. SQL Server 2022 Download, Install, Connect Using SSMS Tool (kingfishertechtips.in)
6. Capture Fiddler Trace for Debugging the Network Traffic (kingfishertechtips.in)
7. On-Premises Gateway: Configure/Map SQL DB To Power Bi (kingfishertechtips.in)
8. Fixed Column/Row Header Make Visible In Any Report (kingfishertechtips.in)
9. Self-Signed Certificate: Configure SSRS/PBIRS With SSL (kingfishertechtips.in)
10. Create Local User Account/Local Admin User In Windows 10/11 (kingfishertechtips.in)
11. Login With AAD/Domain/Microsoft Account To Azure VM’s/RDP (kingfishertechtips.in)

---