Keep track of all the latest news and events on developer tools and technologies you care about
by Jen Stirrup
Mobilisation is a key trend in business intelligence. Azure is ‘up and coming’. What happens if you put these things together? This article will discuss some context around mobilising Azure for business intelligence. Once the context around mobile business intelligence has been outlined, I will lead you through an example of using Azure as a database and as a reporting server, and then displaying that report in a mobile device, so you can see it working for yourself.
The SQLPass Summit 2011 keynotes focused on new advancements on Azure. If you want to know more specific details of these advancements, you can watch Quentin Clark’s Summit Keynote here. The takeaway point for me, however, hit home when I read the Windows Azure blog; all you need to do to take advantage of these new features is to log into the Windows Azure Management Portal and the new features are yours to keep. As someone who has spent a lot of time doing upgrades over the years, this is very impressive because it simplifies the addition of new features via upgrade paths. The simplification is in response to two things: firstly, a need for upgrades that are as painless as possible, and secondly, the requirement for speed of implementation.
At the same time, the demand for speed and delivery of data to the right people all of the time is growing. According to Howard Dresner and his team, who have recently released the third Mobile Business Intelligence study, there is a distinct paradigm shift towards mobile business computing and mobile business intelligence. So what is mobile BI?
Mobile BI is about providing BI on any technology not restricted in use at any place and at any time. Platforms for this include smartphones, iPads and tablets. But mobile BI is not just about providing access to data irrespective of place and time. Speaking broadly, mobile BI provides end users with the capability to access data and take informed decisions anywhere, and at any time.
Despite the demand for mobile business intelligence, there are still issues around its implementation. Mobile Business Intelligence is a methodology: a design and strategy to make sure that critical data is available in a real-time and secured manner. One issue is security, and this impacts both internal and external aspects of the enterprise.
Some of the questions around security are fairly obvious. For example, what happens if a mobile device is stolen or not handed back by a retiring employee? Some security implications are less obvious, however. Given that data can be retrieved over mobile devices from any number of a range of mobile devices, security needs to be deployed. It is possible to roll out security software on corporate mobile devices. The ‘Consumerisation of IT’ 2010 study by Unisys and IDC showed that, whilst many IT departments provide mobile devices for mobile team members, there are still many team members who use their own mobile devices for accessing corporate data. Thus, additional security measures would need to be taken in order to insulate the source systems against inadvertent behaviour or unacknowledged threats from internal team members, in addition to external malicious behaviour.
The growing impetus between the demand for mobile, and the growth of adoption, means that putting Azure and Mobile Business Intelligence together could accelerate the adoption of these individual entities. With Azure, organisations can deploy a mobile solution without worrying about the additional cost and management of additional on-premise hardware. Further, they can just send data to the cloud that they need to mobilise. In other words, there's no need to open up internal source systems to the Internet for mobilisation. Instead, the data can be hosted remotely, meaning that there is no need to expose source systems to theInternet for mobilisation. As an additional point, Microsoft partner organisations, such as Attunity, offer real time synchronisation between on-premise source systems and Azure. This would mean that the Azure target is opened to mobile Internet, and the security is supported by Microsoft security systems and expertise, whilst the internal source systems are secured internally.
It is also possible that Azure could become a data store for the integration of data from disparate sources, which are then mobilised quickly. For example, if Oracle data is sent into Azure, this data could be viewed over a mobile device via SSRS. This means that there would be no difference between viewing data that had been imported into Azure from Oracle, and viewing data that had been imported from a Microsoft data source.If you are a business user, this would mean that you could use equally Oracle and/or SQL Server data and view it on a mobile device. Microsoft partner organisations, such as Attunity, offer components that will import data from Oracle, and this can also help to deliver quickly.
In terms of speed, mobile users still expect high performance when using mobile reports for analysis. This expectation needs to be met as far as possible. A lack of speed can have a poor impact on user adoption, and will obviously have an adverse impact on productivity and employee satisfaction.
The design and display of reports and dashboards is always a contentious issue, and this issue won’t go away with the deployment of mobile reports. Some general guidelines about the display of reports and dashboards on mobile devices are given below:
Set User Expectations – users need to understand that they are connecting wirelessly and that 3G access can be slow. This means that the mobile report might not quickly display all of the fancy graphics that the userexpects.
Simple charts, gauges and tables – it’s better to stick to simple charts so that they will be more likely to render. If you select charts that are too complex, then they will take longer to load, thereby badly impacting usersatisfaction.
Maps – maps add a great deal of context to the data visualisation, but be aware that they can take time to load. Again, this might be an issue for end-user satisfaction. Therefore it is recommended her to use them sparingly.
Capabilities of different mobile devices – as indicated above, people will use their own mobile devices to access systems. If possible, report writers can try to test out their mobile data visualisations on different mobile devices in order to test the mobile experience for the end user. For example, some older devices may have an issue with zooming, and this is one area that is good for testing.
Filtering – in order to maximise the use of the space on the mobile ‘canvas’, users should be able to filter their data.
Now, let’s have some fun with Azure! We will create a sample database, a sample report, and test it out on a device! This section assumes that you have a subscription to Azure. If not, you can sign up with your Windows Live ID here.
Creating an Azure Database
It is very simple to create an Azure database. When you are logged into the Azure Management Portal, there is an option to ‘Create Database’. This can be seen from Figure 1:
Figure 1: Create a new Azure database
You then have an option to create a database by clicking ‘Database’ on the left hand side of the screen, as you can see in Figure 2:
Figure 2: Creating a Database via the Database option
Before we create a new database, however, we must create a server first. When we click on ‘Create a new SQL Azure Server’ in Figure 2, this takes us to a wizard which allows us to create a SQL Azure Server. The first step is to associate the initial screen with a subscription, and the wizard can be found in Figure 3:
Figure 3: Associate a new SQL Azure Server with a subscription
Once the SQL Azure Server is associated with a subscription, it needs to be associated with a region. There are a variety of regions to choose from, and an example of this selection window can be found in Figure 4:
Figure 4: Associate the new SQL Azure server with a region
Once the server is associated with a region, it is set up and ready to use. Now, we are ready to create a new database. The first part in the process of creating a database is to associate the new database with a subscription. Figure 5 shows this piece:
Figure 5: Associate a new database with a subscription
Your database setup is now complete! You can see your new database under ‘Subscriptions’ on the left hand side. An example can be viewed in Figure 6.
Figure 6: New SQL Azure database is completed
Connecting to your Azure Database
Once the SQL Azure database is completed, it is possible to connect to it via SSMS. To do this, however, it is important to note a couple of things:
Figure 7: Connect to Azure via SSMS
Once you’ve hit ‘Connect’, that’s it! You’re connected to your SQL Azure database. Now you need to create a table in your Azure instance.
Create a table in an Azure Database
Creating a table in your Azure database is very similar to creating a table in an on-premise SQL Server. However, there is one main difference; the Azure table must have a clustered index on the table. The reason for this is the replication methodology that Azure uses. The Azure data is written to one primary and two secondary replicas, and the data is the same across all three replicas. A table which doesn’t have a clustered index is basically a heap, which means that the data rows and pages aren’t ordered. The replication technology requires that the data pages and rows are ordered so that it is faster and easier to assess if the data has been written across all three replicas: it is this point at which the data is considered to be committed.
Examples always help, so here is an example of a table below, which holds data about the number of Jedi Knights reported in the UK 2001 Census and their location.
CREATE TABLE [UKJediGeography](
[JediCountID] [int] IDENTITY(1,1)NOT NULLPRIMARY KEY CLUSTERED,
[Area] [nvarchar](255) NULL,
[Postcode] [nvarchar](255) NULL,
[Latitude] [nvarchar](255) NULL,
[Longitude] [nvarchar](255) NULL,
[Country] [nvarchar](255) NULL,
[District] [nvarchar](255) NULL,
[DistrictFull] [nvarchar](255) NULL,
[Sum of All people] [float] NULL,
[Sum of Jedi] [float] NULL,
[Sum of Jedi as percentage of total people] [float] NULL,
[Sum of Rank within EW] [float] NULL
Once the table has been created, it needs to be populated with data. The easiest way to do this is via SSIS. In this example, SQL Server 2008 R2 version of Integration Services has been used. Once the table has been populated with data using SSIS, then it will be possible for the report to be created and tested effectively.
Populating the Azure Database Table
If you know how to use SSIS to populate data into SQL Server, then the process is very similar for populating an Azure table. There are two main differences however;
The connection to SQL needs to be ‘.Net Providers\SqlClient Data Provider’. If you try to use the other connectors, you will get a ‘failed to initialise provider’ error. Here is an example in Figure 8:
Figure 8: Sample Connection Manager for SQL Azure
As before, it will be necessary to use SQL Server Authentication in order to gain access to the SQL Azure database.
Once the table is populated with data, it is possible to create an SSRS report using the Azure source, and deploying the report to Azure. This example will use SSRS 2008 R2 to create the report, and to deploy it to Azure CTP Reporting Services. There are a few steps in this sequence, which will be outlined here:
Setting up an Azure SSRS Reporting Server
Figure 9: Agree to the license for the SQL Azure Reporting Server
Figure 10: Create an Azure Reporting Server
Once you’ve completed these details… that’s it! Your Report Server is up and running. How do we know this?
Creating a Report using SQL Azure as a Source
There are very few differences in creating a Reporting Services report which has SQL Azure as a source, and a Reporting Services report which has an on-premise SQL Server as a source.
SQL Azure as a Reporting Services Source
It is possible to use SQL Azure as a source for the Reporting Services. There are two main differences:
The source should be a URL that points at your database. Here is an example in Figure 11:
Figure 11: Data Source Connection to Azure in Reporting Services
Once you have ensured that the connection is successful by pressing ‘Test Connection’, you can proceed to create a dataset in Reporting Services that will identify the data that is required from the source.
When we click on Query Designer, we get a list of the tables and their fields. It is very straightforward to select the fields that you would like to see; simply select the tickbox next to the field. If you need to amend the query in any way, there is an option to ‘Edit as Text’ in order to make any changes to the fields such as converting types, and so on. The Query Designer can be found in Figure 12.
Figure 12: Query Designer for SQL Azure
The resulting SSRS query appears in the Query Designer box. You can see an example in Figure 13.
Figure 13: Design Query Check
You can either click ‘Next’ to continue, or click ‘Query Designer’ to amend the query to continue.
Now that your dataset is created, drag the data from the dataset into a table. Your Business Intelligence Development Studio will appear as follows in Figure 14:
Figure 14 Report as it appears in BIDS
Once the report is created, you can deploy it to Azure straight from BIDS. To do this, you simply need to provide the URL for the report server in the Project Properties, which can be found by right-clicking on the Project Name in the Solution Explorer. A sample can be found in Figure 15:
Figure 15: Azure Report Server URL for deployment
Once the report is deployed, it will be possible to see the final report in a browser.
How is it possible to see the report on a mobile device? Fortunately, the report is accessible via a URL over the browser. All you need is the URL for the SQL Azure Reporting Server. It will look like this:
Here is an example of the Azure CTP browser window in Figure 16.
Figure 16: SQL Azure Reporting Preview Welcome Screen
Once you’ve typed in your username and password, you can click ‘Sign In’ to see the report. Here is an example tabular report displayed in an Internet Explorer browser. This report can be accessed via URL using a browser.
Once you have logged in, you can see a hyperlink to the folder name where your Azure Report is deployed. When you click on this hyperlink, it will take you to the report. A sample is found next.
Figure 17: Sample Report deployed to Azure
The security of the Azure database and report is handled by Azure, and there are different ways of enforcing security at the user level. In this report, the user is presented with an initial challenge screen. Before the report is executed, the user is required to type in some further username and password before the data source is accessed. This security applies to mobile and standard laptop devices from the Azure perspective.
To summarise, we have looked at the context around mobile Business Intelligence and Azure. We have looked at developing a sample mobile report that uses Azure in order to store the source data and to the hold the deployed report. Finally, we have looked at how to view this data on a mobile device by simply using the URL in a browser.
Azure is an excellent facility for the mobilisation of business intelligence since the Azure team have thought about security and accessibility. In adopting Azure for mobilising reports, this should help developers to have a competitive edge by adding Azure to advance skill sets. The flexibility of Azure means that the mobile business intelligence solution can be scaled back or increased in order to meet demand within the organisation.
The steps given here should help you to get started to mobilise business intelligence within your enterprise, using Azure as a source and target database plus reporting services included, all in one place.
To re-create a report, all you need is:
Jen Stirrup is a SQL Server MVP, with a Bachelors degree in Psychology and two Masters in Artificial Intelligence and Cognitive Science. Jen is joint owner of Copper Blue Consulting, delivering data and Business Intelligence solutions to global clients. Jen is a veteran SQL Server Developer with over 12 years SQL experience in delivering end-to-end Business Intelligence solution. Jen blogs for SQLServerPedia as well as her own blog. She is very active in many SQL Server User Groups in the UK, helping to run the SQLHerts group in England. Jen's focus is on the Microsoft BI stack, with a specific passion for Data Visualisation.