Unlocking the Power of AAS: A Comprehensive Guide to Servers, Gateways, Projects, and SSMS

Azure Analysis Services: In this activity you will create resources in Azure, installing and configuring gateway to connect to your previously installed Analysis Services, and creating a service principal to access Azure Analysis Services using username/password from SQL Server Management Studio (SSMS). Additionally, you will be performing backup and restore of the sample model used in previous activity.

Prerequisites

• Get approvals for $200 credit to Azure portal (Remember your subscription name!)
• Download and install Visual Studio 2019, choose “Data storage and processing” package (can be found from Microsoft Store)
• Install Analysis Services Project extension in VS2019 (can be found within VS2019 Extensions menu > Manage Extensions, search for analysis)
  

Creating Azure Analysis Services

1. Important things to keep in mind while performing this activity: always use your @domain.com to sign-in to any services.
2. Logon to Azure portal.
3. Create a resource > Analytics > Analysis Services.
4. Enter a Server name (must be all lowercase and no special characters).
5. Choose your Subscription name.
6. Choose a Resource group, if you do not have one, click Create New in the link directly below the drop down.
7. Choose the Location (aka Region, remember this!).
8. Choose S0 for Pricing tier. S level gives enterprise functionalities that customers will be using.
9. Click on Backup Storage Settings to create a new Storage Account.
10. Click on + Storage account and give it a name.
11. Click OK and wait for Validation.
12. Select your new Storage account and create a new Container.
13. Click on + Container and give it a name.
14. Click Create.
15. Select your new Container and click Select button.
16. Back in the Analysis Services page, click Create and wait for deployment.
17. 

Connecting using gateway

1. Open Install and configure an on-premises data gateway, download and install gateway.
2. Choose the recommended options and sign in using @domain.com alias.
3. When registering the gateway, ensure to select your default region (this will be used later on for Power BI, as Power BI data gateway will not work unless it is the default).
4. Follow the steps in Azure portal to create an On-premises data gateway resource and connect Analysis Services to your gateway (it is OK if your Azure Analysis Services is in a different region than your On-premises date gateway since we are only testing, for customers it would be preferred to be in the same region due to network latency/performance issues).

Creating DB & Upload To Azure Analysis Services

1. Open Visual Studio 2019, go to File > New > Project.
2. Search for analysis and choose Analysis Services Tabular Project, click Next.
3. Give it a Project name, click Create.
4. Choose Integrated Workspace, and SQL Server 2019 / Azure Analysis Services (1500) for Compatibility level, click OK.
5. Under Tabular Model Explorer view, right click on the project name, choose Import from Data Source.
   
6. Choose Database > SQL Server database, click Connect.
7. Enter the SQL Server instance under Server that you wish to connect (Hostname\Instance). If you only have one SQL Server instance running on your computer, type in localhost to connect.
8. Logon to SSMS using Database Engine.
9. Right click on the Database instance > Properties.
   
10. Go to Security, ensure Server authentication is set to SQL Server and Windows Authentication mode. If you changed this option, you would need to restart your SQL Server services for it to take effect, then come back to this step.
11. Under database instance, go to Security > Logins, right click on Logins and choose New Login.
12. Give it a user name, choose SQL Server authentication, give it a password (twice), and uncheck Enforce password policy.
13. Under User Mappings, assign db_datareader and db_datawriter to the databases you have restored in previous activity, then click OK.
14. Back in Visual Studio, choose Database, enter your SQL Server user credentials.
15. You will receive a warning regarding Encryption, ignore and click OK.
16. Choose any AdventureWorksDW database, click OK.
17. Find the table FactResellerSales, check the box, then click Select Related Tables button.
18. Other tables related to this one will be selected automatically, click Transform Data button.
19. In the Power Query Editor window, rename the Tables to have friendly names (i.e., remove Fact or Dim from the beginning, add space between words).
20. Select the previously named FactResellerSales table, in the data preview, find and select the columns SalesAmount, TaxAmt, and Freight (using Ctrl key).
21. Go to Columns menu > Remove Columns.
22. Click View menu > Advanced Editor, this is the query that the Power Query Editor created for your previous action.
23. Select the previously named DimEmployee table, find and select FirstName and LastName columns.
24. Go to Columns menu > Add Column > Custom Column.
25. Enter Full Name for New column name.
26. In the formula text box, enter the following and click OK: = [FirstName] & " " & [LastName]
27. Click View menu > Advanced Editor, this is the query that the Power Query Editor created for your previous action.
28. Click Home menu > Close & Apply.
29. When the Data Processing window appears and finishes processing, click Close button.
30. Change to Diagram view. Look at the relationship between the tables.
    
31. Change to Solution Explorer view, right click on your project > Properties.
    
32. Under Deployment Server, change Server to your Azure Analysis Services URI. To find it, go to Azure Portal Home, click top left menu, choose Resource group. Click on the Analysis Services from the list of resources. Look for “Management Server Name” and copy it. Back in Visual Studio, paste it in Server field, click OK.
33. Under Solution Explorer view, right click on your project > Deploy.
34. Choose your @microsoft.com alias if prompted.
35. Click Close when Deployment is successful.

Connecting to Azure Analysis Services from SSMS

1. In Azure portal, go to your Analysis Services you have created above. There are many ways to find this. One way is to go to Home, click top left menu, choose Resource group. Click on the Analysis Services from the list of resources.
2. Look for Management Server Name and copy it.
3. In SSMS, connect using Analysis Services as Server type.
4. Under Server name, paste the Management Server Name and choose Azure Active Directory – Universal with MFA for Authentication.
5. Enter your @domain.com alias under User name and click Connect.
6. Authenticate using your Authenticator.
7. Expand Databases and you should see the previous project that you have deployed.
8. Find “Adventure Works Internet Sales Tabular Sample Database” that you downloaded previously, make a copy of the ABF file. Rename the file to AdventureWorksLab.abf to prepare for upload.
9. Back to Azure portal, go to your Storage container (through your Storage Account > Containers).
10. Click + Upload and choose AdventureWorksLab.abf that you have renamed earlier, click Upload button.
11. Back in SSMS, right click on Databases > Restore.
12. Click Browse and expand the Azure Analysis Services.
13. Select the ABF file you have just uploaded, click OK. Click OK again to finish.
14. You may need to Refresh Databases for Adventure Works Internet Sales database to appear.

Creating service principal and connecting using SSMS

1. In Azure portal, go to Azure Active Directory.
2. Select App registrations and select + New registration.
3. Give it a name, leave everything at default (single tenant), and click Register button.
4. Click on the app that you have just created.
5. Copy your application (client) ID and Directory (tenant) ID for usage later.
6. Go to Certificates & secrets.
7. Click on + New client secret.
8. Give it a description that is meaningful, you can change the Expires period to longer if you wish. Click Add button.
9. Copy and keep the new secret value somewhere safe! You will not be shown this value again later!
10. Go back to Azure home portal, go to your Azure subscription.
11. Select Access control (IAM).
12. Select + Add and choose Add role assignment.
13. Click on Members tab and select + Select members.
14. Search for your app that you created earlier, having the exact name will be helpful since you will get many results here. Select it and click on Select button.
15. Click on Role tab and search for Contributor role. Select it and click on Review + assign button. Click on Review + assign button again to finish.
16. In SSMS, connect using Analysis Services as Server type.
17. Under Server name, paste the Management Server Name and choose Azure Active Directory – Universal with MFA for Authentication.
18. Once logged in, right click on the connection and go to Properties.
    
19. Go to Security, click Add button.
20. Under Manual Entry, enter the following format: app:<Application (client) ID>@<Directory (tenant) ID>
21. Click OK to save and disconnect from the connection.
22. Connect again using Analysis Services.
23. Under Server name, paste the “Management Server Name” and choose Azure Active Directory – Password for Authentication.
24. User name will be the following format: app:<Application (client) ID>@<Directory (tenant) ID>
25. Password is the secret you have created earlier.
26. Click OK to connect.

SQL Bi Links:

1. Could Not Load Folder Contents In Portal URLs – SSRSPBIRS (kingfishertechtips.in)
2. Capture Fiddler Trace for Debugging the Network Traffic (kingfishertechtips.in)
3. On-Premises Gateway: Configure/Map SQL DB To Power Bi (kingfishertechtips.in)
4. Fixed Column/Row Header Make Visible In Any Report (kingfishertechtips.in)
5. Self-Signed Certificate: Configure SSRS/PBIRS With SSL (kingfishertechtips.in)
6. Run As Different User Option Not Showing, Enable In Windows (kingfishertechtips.in)
7. Create Local User Account/Local Admin User In Windows 10/11 (kingfishertechtips.in)

---