SSAS/SQL Beginner Guide: Download, Installation, Restore Database & Tabular Model.

SSAS/SQL: In this activity you will install SQL Server, SQL Server Management Studio, and Analysis Server plugins for use in future activities. Additionally, you will be restoring a demo database server from backups and linking analysis services to the data for a data refresh.

Download & Installation SSAS/SQL

1. Navigate to SQL Server Downloads | Microsoft and click on “Download now” under the Developer section.
2. Select the Basic installation option
3. Accept terms and select where you would like to install it (default location is fine)
4. While this is installing, download the following files to use for the database restores:
   • AdventureWorksDW2019.bak 
   • AdventureWorksDW2016_EXT.bak 
   • adventure-works-tabular-model-1200-full-database-backup.zip
     • You will need to unzip and grab just the .abf file – rename it to AdventureWorks1200.abf when extracted
   • Note: Where to place them will be noted later, for now you can leave them in Downloads
5. Once installation is complete launch SQL Server 2019 Installation Center (Start > Microsoft SQL Server 2019 > SQL Server 2019 Installation Center)
6. Select Installation from the left side menu and choose the first option on the right.
   
7. Select the “Developer_ENU” folder from where you installed the server [Default location is C:\SQL2019] and hit OK.
   • Note: You will likely get a pop-up that tells you that you need to restart. If needed, start from step 5 when back online
     
8. Installation Type: Perform a new installation of SQL Server 2019
9. Product Key: Select “Developer” from the free edition drop-down
10. Feature Selection: Select Database Engine Services and Analysis Services
11. Instance Configuration: Select “Named instance:” and provide a name (Instance ID can be left as default after filling the name field)
12. Database Engine Configuration: Select “Mixed Mode” then fill out a password and hit “Add Current User”
13. Analysis Services Configuration: Select “Tabular Mode” and hit “Add Current User”
    
14. Complete installation for SQL Server
15. Once you reach the complete page, open the install summary via the link, and note the service account for analysis server
    
    
16. Navigate to Download Microsoft SQL Server 2019 Reporting Services from Official Microsoft Download Center to download and install Reporting Service for your reports.

Restore in SSAS/SQL

1. Connect to the Database Engine and Analysis Services (one at a time) searching for the server you created during installation
   • “File > Connect Object Explorer” OR if you have the Object Explorer side bar showing (on by default), just hit Connect in the top left corner of that side bar
     
   • Authenticate using Windows Authentication
2. Copy the backup files from earlier into the SQL backup locations
   • For the two Data Warehouse (DW) .bak files, place them in the following location:
     • C:\Program Files\Microsoft SQL Server\MSSQL15.<YOURSERVERNAME>\MSSQL\Backup\
   • For the Tabular Model .abf file, place it in the following location:
     • C:\Program Files\Microsoft SQL Server\MSAS15.<YOURSERVERNAME>\OLAP\Backup\
3. Right-click on “Databases” under the Database Engine and select “Restore Database…”
4. Select the “Device” radio button and click the “…” button that is now available
   
5. Click on “Add”
6. Navigate to the folder you placed the .bak files into and select one of the files and complete the restore by hitting OK on the two windows
7. Repeat steps 3-6 on the second .bak file
8. Confirm both files are uploaded by expanding the Databases section
   
9. Right-click on “Databases” under the Analysis Services and select “Restore…”
10. Under “Restore Source” hit “Browse” expand the folder that you stored the .abf file in
    
11. Complete the restore.

Analysis Services Data Refresh

1. Expand Databases, then click on Connections and Tables
2. Double-click on the file under the Connections section
3. Click on “Connection String” and then the “…” that has now appeared at the end of the line
   
4. Select SqlClient Data Provider with the “Provider” drop-down
5. Paste your server name into the “Server name:” field (Do not hit the arrow to search as this will scan the whole network for available servers)
6. Make sure “Windows Authentication” is selected in the log on section
7. Select the AdventureWorksDW2019 database from the drop-down
   
8. Click on “Impersonation Info” and then the “…” that has now appeared at the end of the line
   
9. Select the “Use the service account” radio button
10. Hit OK to commit the changesYou will now need to add the service account to the logins for the server/database and provide the account appropriate permissions to enable the refresh via the service account – See Step 15 Under Download and Installation or Potential Issues section for details
11. Expand the Security tab under the Database Engine and right-click on Logins then select “New Login…”
12. Paste the name of service account into the “Login Name:” field
13. Select “User Mapping” on the left-hand menu
14. Check off both AdventureWorks databases
15. Highlight the first database and then check off “db_datareader” and “db_datawriter” in the bottom pane. Repeat for the second database the refresh
    You can now complete
16. In the previously expanded Tables section, right-click the first table (most like Customer) and select “Process Table”
17. Make sure all boxes are checked and change the Mode to “Process Full” then hit OK.

Other Posts!

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)

---