Creating a Azure SQL Server Reporting Portal for Public Usage

I love using Windows Azure.

This technology saves you a lot of time when it comes to deploying Microsoft server technology. No longer do we have get a physical server, install all the appropriate hardware, install the all the software, configure the software, and not to mention the security, change control, governance, etc. and all that is required to get just the server up and running.  With Windows Azure, you simply need to just open up the Microsoft Azure portal and create the appropriate virtual machine and pick the required template. Tasks that used to take day can now be done in an hour.

Making your SQL Azure server available for Internet users essentially consists of the following two tasks:

1. Opening HTTP/HTTPS endpoint for your VM in Azure management portal (or during the installation of the VM)
2. Establish a firewall port on the VM (After the VM has been installed)

The process is as follows...

First we have to create a Virtual Machine for the SQL Server. You can use any SQL Server version as all have the SQL Reporting Services components installed.

 

Next, when creating your VM, machine sure you add the option to connect via HTTP. This is done by adding the HTTP endpoint. You can specify a Private and Public port (80 is the default for HTTP).

 

Continue until the provision completes (i.e. Creating Virtual Machine,). Depending on the size of the VM (in this example I’m creating a SQL Server Web Server), it should take about 20 minutes to an hour. You have to establish the SQL Reporting Services as by default, the URL, Manager, and the Reporting database is not installed (version SQL 2014).

Open Windows PowerShell on your Virtual Machine and open a port so this VM is available for Internet users.

                 i.          Open a command windows (cmd.exe)

                ii.          When the command widows opens, run PowerShell (Powershell.exe)
               iii.          Run the following command via PowerShell on the Virtual Machine:

New-NetFirewallRule -DisplayName “Report Server Name”-Direction Inbound –Protocol TCP –LocalPort 80

Open the SQL Services Reporting Configuration Manager to now configure the SQL Reporting Service. First open up the tool and Connect to the existing SQL Server. Next establish the SSRS Web Service URL…

 

Next establish the SSRS Web Service URL…

 

Make sure you create the database next. Follow the wizard and input the required information. This is a straightforward process that will either create a new database or use an existing one. I would suggest creating a new one as it is a lot easier.

Next create the actually SSRS Reporting Portal that you will typically use to upload your reports.

 

Once you have enabled / installed the SQL Reporting Database, the Report Server websites, you can now connect directly via a browser to the server. You will have to use the fully qualified domain name (FQDN) as follows:

<Servername>.cloudapp.net/reports  (*.cloudapp.net is the default Azure DNS)

You can now have users connect to the reports, develop reports and use other SQL Reporting tasks that you normally accomplish via a SQL Server on your premises.

 

Creating PowerPivot data sources

As I have demonstrated there are a lot of things about PowerBI to get excited about. One of these is Microsoft’s adaption of the technology xVelocity. The true power behind PowerPivot is the xVelocity in-memory analytics engine.

Why XVelocity is important

Before Microsoft’s introduction of xVelocity most of the report offerings was via SSRS, ASP.NET, and Performance Point Services. SSRS an ASP.Net uses a lightweight development model. These visualization tools relied on data sources like SQL Server that traditionally was stored on hard disks.  Hard disks are relatively slow. Whenever system must use resources stored on disk, this will impact the response time of the report or dashboard. This is why Microsoft now uses xVelocity.  xVelocity gives you two very important things, faster dataset creation (via PowerPivot) and the faster response from visualization tools that rely on these datasets.

PowerPivot as a data source

The power to create PowerPivot data sources cannot be understated. With this technology you can create a centralized data source that encompasses various data sources. You can create a PowerPivot data source that is comprised of text files, Excel worksheets, and database tables into one data source. In my example, I’m going to demonstrate how to do this with SQL Server. PowerPivot data source differ from others because its dataset is stored entirely in memory. Again, this is due to the xVelocity technology.

PowerPivot is essentially the database “Tabular” model

Tabular model is a database design much like OLAP Cubes. However, Tabular cannot totally replace MDX cubes. The main reason is due to the memory requirements for tabular. Tabular models must be stored in memory while MDX can be stored on physical disks, essentially allowing an extremely large data sources. Unfortunately, the MDX language is more difficult to use and the OLAP cube development can be much more frustrating.  DAX, which is the expression language of Tabular/PowerPivot data sources, is easier to use and understand. Excel users will find DAX easy to adapt because of its familiarity to Excel expressions.