Microsoft

Blog Categories

Subscribe to RSS feed

Archives

Follow our Microsoft Technologies board on Pinterest

Mike Burger

Posts by this author: RSS

SQL Server 2012 (RC0) Installation and Configuration

Step-by-Step Instructions

Before getting started I would like to point out the other blogs in this series. If you do not need help with the installation of the SQL Server 2012 bits then perhaps one of the other blogs will be of help.

Installation Order:

  1. SQL Server 2012 (RC0) Business Intelligence Configuration
  2. SQL Server 2012 (RC0) Installation and Configuration
  3. SharePoint 2010 Installation for SQL Server 2012 (RC0)
  4. Additional SSAS Instance Installations in SQL Server 2012 (RC0)
  5. SQL Server 2012 (RC0) PowerPivot for SharePoint 2010 Configuration
  6. SQL Server 2012 (RC0) SSRS Configuration in SharePoint Integrated Mode (including Power View) (Coming Soon)
  7. SSRS Alerting (Coming Soon)
  8. MDS (Coming Soon)

The SQL Server 2012 RC0 bits can be downloaded here. When you get your hands on the installation media click on the install file to open the SQL Server Installation Center. I will not cover it here, but the initial screen displayed contains a variety of Planning resources that can be used to help determine if your server meets the minimum requirements for a SQL installation, as well as resources to run a few tests to see if your server is eligible for an upgrade from a previous version of SQL.

After you are satisfied you have exhausted your ability use the resources on the Planning tab (or if you are impatient like me), click on the Installation link on the left side of the menu. Since I am installing everything on a single server, I then click on the option to install a new stand-alone SQL Server.

At this point the installation will check some Setup Support Rules. If you did not restart your computer after you ran some Windows Updates you may require a server restart. Additionally, you may need to install .NET. I would recommend taking care of any issues or warnings that occur in this screen so you can be confident that your installation will go smoothly.

If everything passes go ahead and click OK. The next screen will check for any available updates, when this is complete click Next. The installation process will then install some additional setup files. Again, let the process do its thing and then click the Install button.

Again a few Rule checks are performed, however, this time there are a few Warnings. One warning lets us know that we are installing SQL on a domain controller. This is okay, and in fact, this is intentional. Since we are installing everything on a single server there is no way to avoid this. The second warning lets us know that there is a potential issue with the Windows Firewall. This is a warning that I could fix. However, since I am never going to need to have any of the SQL services access resources outside of this single server the firewall warning will never cause an issue. If there aren’t any other issues, once again click on Next.

The next screen asks for a Product Key. Since I am installing the RC0 bits I don’t need one, I can leave the edition as Evaluation and continue. The last step before we begin setting up the server roles makes us agree to the License Terms. Click the check boxes as needed, and continue to the next step of the setup.

Now it gets a little more interesting. We are going to start installing the SQL Server services that will provide us with all of the functionality within the BI stack. On the Setup Role screen, we are first going to select the SQL Server Feature Installation option. You will see this screen a couple times in the future if you plan on coming back and modifying the current installation or if you plan to install additional instances of SSAS in Tabular mode or PowerPivot for SharePoint. I prefer to pick the SQL Server Feature Installtion option over the All Features With Defaults because I prefer to walk through the setup of each service. Click Next to continue to the next screen.

On this screen, we get to pick the Features we would like to include on our server. Since we are installing everything on a single server this is super simple, select everything! In a typical production environment on a select few services would be installed on the server, with other services being distributed amongst other servers in the farm (SSAS and SSRS for example).

Note: Since I already know I am going to install one of each available SSAS services (Multi-dimensional, Tabular, and PowerPivot) I prefer to just install the standard Multi-dimensional version first. Later in this blog series I return to these same setup screens and install SSAS services for both Tabular Mode and PowerPivot.

I would like to point out that we are installing Reporting Services in both Native and SharePoint mode, along with the Add-in for SharePoint Products. This is okay even though we don’t have SharePoint installed yet. We will install the Add-in for SharePoint now and apply it to our SharePoint installation when we configure SSRS. If you know that you are only going to use SSRS in SharePoint Integrated mode you can unselect the option to also install Reporting Services in Native mode. Since I am building a demo server I will install both so that I can showcase both SSRS installation methods and uses. At the very least I would also recommend installing the Management Tools and SQL Server Data Tools since these will be needed by any DBA or Developer that is going to use the server. If you are satisfied with your feature selection you can move to the next step by clicking on Next.

On the next screen a couple Installation Rules are checked. At this point you may need to update prior installations of Visual Studio or the .NET Framework if they exist. Once both of these Rules pass, proceed to the next screen.

Since we have now decided which Roles and Features we are going to install on the server, we now need to perform the necessary configuration for each in order to complete the installation. The first step is to setup the instance for the Database Engine. Since I do not have a previous instance on this server I can use the default of MSSQLSERVER. You may need to change this if you already have an existing instance. You can also modify the root directory for the Instance if you have multiple drives to choose from.

On the next screen the install process then checks to make sure the drive you selected has enough free space for all the roles and features that were selected. Click next to continue to the Server Configuration setup screen.

This Server Configuration screen is very important. This is where we determine which accounts will be used in order to run each of the related SQL services. Since our server is already setup as a domain controller and we have already created all the necessary domain users, this step is pretty simple (check out the first blog in this series if you have not performed this setup already). I use my SQL domain account to run the SQL Server Agent, SQL Server Database Engine, and SQL Server Integration Services. Additionally, I have a separate SSAS domain user for the Analysis Services service, and a separate SSRS domain user for the Reporting Services service. Click Next.

We are now given configuration screens for each of the individual features that we chose to install. On the first Database Engine Configuration screen we can choose the Authentication Mode for the server (Windows Authentication or Mixed Mode), the storage location of the database files, and some FILESTREAM settings. To keep things simple on my single server I selected the Windows Authenticated mode and then added myself and an additional Administrator account as the SQL Server Administrators. Once you have finalized the settings, click on Next.

We now need to configure Analysis Services. Since I already know that I am going to need three instances of SSAS on this server, I always choose to install the Multidimensional and Data Mining Mode. This is the standard version of Analysis Services that most people are probably used to. This allows for the creation of classic dimension / fact data warehouses. In a larger BI implementation, this instance of SSAS could be installed on a server separate from the Database Engine. This would allow the Database Engine server to run the SQL Server Engine and SSIS and remove the processing load that SSAS normally puts on a server. In addition to this, the future installations of SSAS in POWERPIVOT and Tabular Mode could be on their own separate servers as well. As part of you infrastructure planning you should take into account how much each of these services will be used in order to determine if they will need to sit on a separate server.

On this screen, I again leave the defaults for the Data Directories (these can always be changed later if necessary), and add myself and the Administrator account as admins for Analysis Services. Once again, click on Next.

Next is the Reporting Services configuration. Since we chose to install SSRS in both Native and SharePoint Integrated Mode earlier in the installation we will see both of them here. Integrated Mode only allows us to install without any automated configuration, however, Native Mode offers a few options. For this, I am also going to choose to install and not configure since I have no immediate use for Native mode as I will be leveraging integrated mode with SharePoint 2010. Once you have made your selections, click on Next.

Note: From my experience, I have always had a lot better luck choosing to install only for SSRS in Native Mode. SSRS has a pretty easy to use configuration tool once installed that makes it pretty easy to setup.

The installation process then goes through a few more minor screens, continue to get next until you are given an option to click on Install. I always advise to quickly scan through the list of items that are ready to install to make sure I didn’t make any accidental selections. When you are ready, start the installation!

The installation will run for a bit, and then give you the final configuration screen. If everything went well you have successfully installed SQL Server 2012 RC0!

SQL Server 2012 (RC0) Business Intelligence Configuration

Overview

With every new version of SQL that is released there are always a few changes and challenges related to the installation and configuration of all the tools within the BI stack. Admittedly, even with the ever changing toolset within the BI stack, this has gotten easier over time. Furthermore, the deep integration with SharePoint now makes it impossible to setup a VM and use the BI stack without it.

If you have never installed SQL server or any tools on the BI stack don’t worry! The intent of this blog series is to walk through the configuration of each of the tools within the BI stack, including the SQL Server Database Engine and SharePoint 2010. With that said, my entire configuration is done on a single VM. Even though you will not run into many client environments with such a simple setup, a lot of the techniques and principles I cover can be translated to more complex situations. I completely understand that every environment is slightly different so it is possible that this step by step guide won’t work for everyone. For this reason, feel free to use this blog a starting point for a new VM or installation of SQL, as well as an area to discuss any other installation issues that occur.

Let’s get started!

To begin, I started with a clean installation of Windows Server 2008 R2. I also installed Office 2010 and made sure that all of the necessary Windows Updates and patches had been applied. For me, the best place to begin is to setup the VM as a Domain Controller. I do this for two reasons:

  1. A PowerPivot installation integrated with SharePoint requires the SSAS PowerPivot service account to be a domain user that can manage the installation through Central Administration.
  2. I have always found that it is significantly easier to setup all of the BI services if there is a separate domain account for each core group of services. This makes it easy to manage the accounts and ensure that the services remain isolated from each other.

Note: If you have not setup a Domain Controller before please check out this video series. It is put on by Dave Wickert, a fantastic Principal Program Manager at Microsoft. Dave is a great presenter that always makes complex topics and concepts easy to understand. If you get a chance to see him present in person make sure to check him out.

Below is a screenshot of the users I created on the machine in order to complete the rest of the server configuration. Along with using the Administrator, I also created a SharePoint, SQL, SSAS, and SSRS domain user. You may need to modify the security and rights of each user depending on how you use them in the future. My use of each of these domain users is explained later within this blog series.

What’s next?

We are now ready to start some of the important SQL 2012 installations. Below is a list of each installation process that is included in this blog series, along with a link to the related content.

Installation Order:

  1. SQL Server 2012 (RC0) Business Intelligence Configuration
  2. SQL Server 2012 (RC0) Installation and Configuration
  3. SharePoint 2010 Installation for SQL Server 2012 (RC0)
  4. Additional SSAS Instance Installations in SQL Server 2012 (RC0)
  5. SQL Server 2012 (RC0) PowerPivot for SharePoint 2010 Configuration
  6. SQL Server 2012 (RC0) SSRS Configuration in SharePoint Integrated Mode (including Power View) (Coming Soon)
  7. SSRS Alerting (Coming Soon)
  8. MDS (Coming Soon)

Additional SSAS Instance Installations in SQL Server 2012 (RC0)

Overview

Please check out the other blogs in the series related to setting up each of the main SQL Server 2012 features. Links to each of the related blogs are shown below.

Installation Order:

  1. SQL Server 2012 (RC0) Business Intelligence Configuration
  2. SQL Server 2012 (RC0) Installation and Configuration
  3. SharePoint 2010 Installation for SQL Server 2012 (RC0)
  4. Additional SSAS Instance Installations in SQL Server 2012 (RC0)
  5. SQL Server 2012 (RC0) PowerPivot for SharePoint 2010 Configuration
  6. SQL Server 2012 (RC0) SSRS Configuration in SharePoint Integrated Mode (including Power View) (Coming Soon)
  7. SSRS Alerting (Coming Soon)
  8. MDS (Coming Soon)

At this point in our configuration, we have already installed and configured one instance of SSAS in Multidimensional and Data Mining Mode. In SQL Server 2012, there are now two more types of SSAS services available for installation.

The first, PowerPivot for SharePoint Integrated Mode was introduced in SQL Server 2008 R2. This provides users the ability to view and store PowerPivot workbooks in SharePoint 2010. I would compare this functionality to Excel Services; however, these PowerPivot workbooks can be much more powerful since they truly extend the ability for Excel to provide insightful analysis on multiple data sources. The creation and use of PowerPivot within Excel is beyond the scope of this blog but I am already in the middle of putting together a blog on the new PowerPivot features in SQL Server 2012.

The second SSAS service that can be installed in addition to the classic Multidimensional Mode is new to the SQL Server business intelligence stack. This is SSAS in Tabular Mode. As opposed to a multidimensional cube, you can really think of the Tabular Mode as a simpler “table” approach for analyzing data. I think that Tabular Models will be easier for companies that are new to BI to understand and initially configure, and I really think of Tabular Mode as the “Corporate” version of a PowerPivot document. This gets into a larger discussion of the Microsoft BI Semantic Model (something we will all have to get used to hearing about). In fact, with a few clicks PowerPivot data models can be pulled out of Excel PowerPivot workbooks and loaded onto the SSAS Tabular Mode instance. At this point, IT can gain control of the model, enhance it, distribute through additional presentation layers, and add security. This will be quite beneficial for allowing IT put some additional structure around the use of Tabular Models that have become mission critical for an organization. There will be more specific blogs on this topic from me in the future.

For now, don’t worry too much about each SSAS mode if you have not used them before. Let’s just concentrate on how to install them. As I have mentioned a few times in this blog series, I am going to install all three types of SSAS on one server. This is for simplicity within this blog series, and does not represent how a production system would actually be configured.

Typically I would recommend at least a couple of the instances of Analysis Services be moved to a different server so that the processing of the SSAS databases could be spread across multiple machines. For a lot of the clients I work with, PowerPivot and Tabular mode are (or will be) new. Initially this means they probably won’t be used heavily so are good candidates to be combined onto a single server.

Step-by-Step Instructions

To begin, we’ll start with installing SSAS in Tabular Mode. Open up the SQL Server 2012 installation media and proceed through the Setup Support Rules. At the point where you are able to select an Installation Type, select the option to perform a new installation of SQL Server 2012.

Continue through the installation options until you reach the Setup Role screen. Since we are installing SSAS in Tabular Mode first we need to select the option of SQL Server Feature Installation. Once you have selected the proper options click on Next.

On the Feature Selection screen unselect all of the options except for Analysis Services. Once again, click on Next.

I prefer to name my instances in a way that will make them easy to identify in the future, so I will simply name this instance “TABULAR” (I am so creative right?) To keep things simple, I will give this nice instance the same Name and ID.

Skipping up to the Server Configuration screen, I will once again use my SSAS domain user. If you do not have your server setup as a domain check out the first blog in the series. You will need this server to be setup as a domain controller or part of a domain in order to configure SSAS PowerPivot with integration with SharePoint 2010.

The key to our installation is to select Tabular Mode on the Analysis Services Configuration screen. Set your administrators for the SSAS instance and then click all the way through to complete the installation. That’s it, very easy to install.

Now that we have walked through and completed the installation of SSAS in Tabular Mode, we now need to install SSAS for PowerPivot for SharePoint. For the sake of time I will skip over the steps that are the same as the Tabular Mode installation, however, there are a few points to make.

First, there is an option on the Setup Role screen to add a SQL Server database engine to the installation. Since we already have a brand new instance of SQL Server Relational Engine installed earlier in the blog series I unselect this option before moving forward.

Secondly, on the Installation Rules page you could receive two warnings. The first warning was caused by unselecting the option to install the database engine on the previous screen. This is okay. Essentially it is telling us that PowerPivot for SharePoint needs SharePoint, and that SharePoint needs the database engine. Again, since we have this already we are okay.

The second warning lets us know that we need SharePoint 2010 SP1. If you have not installed SharePoint 2010 SP1 please refer to the SharePoint 2010 Installation for SQL Server 2012 blog found earlier in this series.

Since none of these warnings apply for us, we can continue. Unlike the Tabular Mode, we are not able to provide a Name for the PowerPivot instance, this must be POWERPIVOT. Again, to keep things simply I give this instance the same ID as shown in the screenshot below. Click through the rest of the installation and you’re done!

SQL Server 2012 (RC0) PowerPivot for SharePoint 2010 Configuration

Overview

If you do not already have SQL Server 2012 PowerPivot and SharePoint 2010 installed please check out one of the earlier blogs in this series. All of the blogs in the series are listed below. If you did not follow the SharePoint 2010 Installation for SQL Server 2012 blog then your PowerPivot configuration might vary slightly from what I shown below.

Installation Order:

  1. SQL Server 2012 (RC0) Business Intelligence Configuration
  2. SQL Server 2012 (RC0) Installation and Configuration
  3. SharePoint 2010 Installation for SQL Server 2012 (RC0)
  4. Additional SSAS Instance Installations in SQL Server 2012 (RC0)
  5. SQL Server 2012 (RC0) PowerPivot for SharePoint 2010 Configuration
  6. SQL Server 2012 (RC0) SSRS Configuration in SharePoint Integrated Mode (including Power View) (Coming Soon)
  7. SSRS Alerting (Coming Soon)
  8. MDS (Coming Soon)

To me, one of the best features of SQL Server 2012 PowerPivot is that it can easily (and from my experience consistently) also configure SharePoint 2010. Granted, I am doing this on a new server, but so far I have had very little issues when following the steps described below.

Note: You must have SharePoint 2010 SP1 installed to successfully perform all of the following steps.

Step-by-Step Instructions

When we installed SSAS in PowerPivot for SharePoint earlier in the series, we also installed the PowerPivot Configuration Tool. This configuration tool can be found in the Start Menu, then Microsoft SQL Server 2012, and finally the Configuration Tools folder.

When the configuration tool opens we only have one option since we have not configured PowerPivot for SharePoint on this server. I would like to point out that if you do have issues with your installation you can always come back to this configuration tool and Repair or Remove a botched installation. Additionally, if you already have a previous version of PowerPivot you can try to upgrade the Features, Services, Applications, and Solutions of an existing SharePoint 2010 farm. I have not tried this yet but would be glad to hear how it has worked for you.

If you click OK without having insalled SharePoint 2010 SP1 the configuration tool will not let you proceed any further. Reference my installation of SharePoint 2010 earlier in the series for how to install SP1 if you have not done so already.

After selecting to Configure or Repair PowerPivot for SharePoint we are taken to a screen that lists all of the tasks completed by the configuration tool. This is where all the magic happens. If you read through all of the configuration tasks it is actually quite incredible what this configuration tool does. It does everything from configuring a new farm (since PowerPivot requires a SharePoint farm), deploys all the PowerPivot integration, starts the Claim to Token Service, and even configures Secure Store and Excel Services for us! Pretty cool right? If your server is configured properly with all the prerequisites you will be able to successfully validate before running the configuration tool.

Note: Do not forget the Passphrase that you choose since this is used in the SP2010 setup. If you lose this, you will have to reinstall SharePoint later down the road or will be unable to completely configure the installation. Also, if anything fails you can hop over to the Output tab for more information. For example, I got an error due to the fact that a SharePoint site already existed in SSIS with the same name. This was from a previous installation on the server. I went into IIS, deleted the old site, validated again and everything worked fine.

Another error you could get during the validation is that the Analysis Services password is incorrect. If this is the case, use the left menu to click on the menu item for registering SSAS and type in the password as shown below. Once validated, run the configuration wizard to completion.

The next couple steps may not be needed, but I have found that if they are completed they can go a long way towards avoiding hours of troubleshooting some initial errors that PowerPivot and the PowerPivot Admin site will throw. Go to the new Central Admin site and in System Settings click on Manage services on server.

After this ‘Stop’ and ‘Start’ the ‘Claims to Windows Token Service’ and then run an IIS Reset and return to Central Admin. Until I did this, the PowerPivot administration screen would throw errors when I tried to view it. Additionally, when I tried to refresh PowerPivot workbooks I would get errors as well.

To view the PowerPivot management screen click on Application Management in Central Admin, then Manage service applications, and then Default PowerPivot Service Application. This step validates that the PowerPivot Admin page is up and running. You probably won’t have much data to look at, but as long as no errors are thrown then you should be good to go.

At this point, you will also need to confirm that PowerPivot workbooks can be viewed and refreshed in SharePoint. To do this, you will obviously need a PowerPivot workbook! Creating a PowerPivot workbook is beyond the scope of this blog, but in order to do it you will need the PowerPivot Add-in for Excel. This is a free download and the SQL Server 2012 (RC0) version can be found here. Once I had this installed I created the PowerPivot document shown below.

Lastly, to validate the installation, navigate to your new SharePoint homepage. Click on the PowerPivot Gallery library and upload the newly created PowerPivot document. You might need to let the timer job run or refresh the browser in order for the gallery to update the thumbnails of each tab within the Excel document. If everything is working, you should see a view similar to the one below.

Click on the sheet you wish to view to confirm that the document can load in the browser. Click on a couple of the slicers to validate that the document is able to refresh the data. If so, your installation is successful!

SharePoint 2010 Installation for SQL Server 2012 (RC0)

Step-by-Step Instructions

I would like to point out that this installation is actually pretty simple for a brand new BI environment. If you are new to the series, please click on one of the other blogs in the series to get up to speed. If not, continue on!

Installation Order:

  1. SQL Server 2012 (RC0) Business Intelligence Configuration
  2. SQL Server 2012 (RC0) Installation and Configuration
  3. SharePoint 2010 Installation for SQL Server 2012 (RC0)
  4. Additional SSAS Instance Installations in SQL Server 2012 (RC0)
  5. SQL Server 2012 (RC0) PowerPivot for SharePoint 2010 Configuration
  6. SQL Server 2012 (RC0) SSRS Configuration in SharePoint Integrated Mode (including Power View) (Coming Soon)
  7. SSRS Alerting (Coming Soon)
  8. MDS (Coming Soon)

As stated earlier, the installation of SharePoint 2010 for us is actually quite simple. This is great for those that have spent a lot of time using the BI tools, but not a lot of time installing and configuring SharePoint 2010. This is not to say that no planning is needed, however, the installation and configuration of SSAS in PowerPivot mode will do most of the initial SharePoint configuration for us.

Note: Optionally, you can install and configure SharePoint yourself then add SSAS in PowerPivot integrated mode at a later date. Since this is configuration for a new server I am going to do it the easy way, and just let the PowerPivot configuration do all the heavy lifting for me.

To begin, open up the installation media. Enter your product key, agree to the license terms, choose the installation directory, and start the installation. That’s not too hard right?!?

The last key to the installation process is to make sure to uncheck the option to run the SharePoint configuration wizard at the end of the installation. Again, we are having the PowerPivot configuration wizard do this for us later in our server configuration so we don’t need to do it now.

Important: Before we can be done with the SharePoint installation, there is one more step that we can do to save us some time down the road. This would be to install SharePoint 2010 SP1. The new version of PowerPivot on SQL Server 2012 does require SP1, so if we don’t install it now, we will have to install it later. I firmly believe that installing the service pack before configuring the SharePoint farm is the way to go if that is an option for you.

You can find the SharePoint 2010 service pack here. Rather than just opening the file from the site, save the file locally on your server. Once downloaded, double click on the file to run the SP1 installation.

So we are done right? Nope! There is always one step that I forget. There is one more step needed to install the service pack. To do this, open a command prompt by right clicking on it, then running as Administrator. Once open, navigate to the following directory:

C:Program FilesCommon FilesMicrosoft SharedWeb Server Extensions14BIN

Once there, run this command:

PSConfig.exe -cmd upgrade -inplace b2b -force -cmd applicationcontent -install -cmd installfeatures

Now you are all done with all the SP2010 installation steps.

Note: At this point we have installed quite a lot of new software onto our server including the OS itself, Office 2010, and SQL Server. For this reason I would recommend taking a break from the SQL 2012 installations to check for and install any Windows Updates that are available.

Ahhhh!! There’s data everywhere!!

In a world where data is collected for almost everything, companies often find they have trouble figuring out what to do with it. Theoretically companies could report on all their data, however, the larger the dataset gets the more money it will take to setup the infrastructure to support it. For most companies, especially those that are new to Business Intelligence and Data Warehousing, this is not realistic.

 

For this reason it is just as important for companies to put in the time and effort to identify their key data assets and make sure to build the infrastructure to support these very well. A vertical focus on a few key data assets can provide very high business value and decision making abilities.

 

As the volume of data and number of data sources grow, it becomes even more important to put in time and effort to analyze the quality of the data and cleanse the data. Most companies don’t want to spend time or money on data governance initiatives, but if the data fed into the reporting tools is bad or inaccurate, then the reporting will be bad and inaccurate as well. In short, “Garbage in, garbage out”.

BI Mobility with SharePoint and Mobile Entrée

It is almost impossible to have a conversation about Business Intelligence these days without talking about mobility. Mobile internet and data consumption is growing rapidly and it doesn’t look like that is going to change any time soon. There are many mobility options to choose from but not all of them work on all mobile devices. Some require heavy custom application development so many companies are investing heavily in buying their work forces smart phones or tablets so that all users can connect to their corporate data and dashboards using the same devices.

That led me to start thinking, are there any other better options? Does it really have to be this hard? There has to be an easier way to do this…

Then I came across Mobile Entrée. Mobile Entrée is a slick, easy to install, mobile option for organizations that allow decision makers to access data from anywhere, from any mobile device, in a secure way. This is because Mobile Entrée is built on technologies that many companies are already familiar with, SharePoint and Excel. Mobile Entrée can be installed, managed, secured, and scaled all within SharePoint. Since it is developed in Visual Studio and is packaged and deployed as a SharePoint feature, it can inherit security from a SharePoint site.

Not only that, a big differentiator for Mobile Entrée is that it is a browser based solution. This is what allows Mobile Entrée to run on all major smart phones and tablets. There is no need for a separate application on the phone, although there is a development API which developers can use to enhance the already long list of great features.

Since Mobile Entrée works out of the box with most out of the box SharePoint functionality, Mobile Entrée allows for a laundry list of great SharePoint integration:

  1. Themes can be applied to control the look and feel using CSS, XSLT, and JavaScript…similar to how master pages are used on a SharePoint site.
  2. Users have read and write access to SharePoint lists
  3. Users can consume data from SharePoint KPI lists
  4. Users can manage documents, including the ability to check in and check out documents
  5. Users can leverage SharePoint workflows, for example, allowing users to approve items while on the go
  6. Users can use view colleagues, news feeds, note boards, and other My Site functionality
  7. Users can search the SharePoint site using defined scopes

With all that said, I was most impressed with how Mobile Entrée allows users to use data from Excel. Mobile Entrée provides an add-in that allows for the ability to configure cell ranges and items that can be consumed by smart phones. If you have used Excel Services before, I would compare the simple setup to how cell ranges and items are defined for an Excel Services report. The add-in provides a “Mobile View Configuration” section on the Excel “Insert” tab (both Excel 2007 and Excel 2010). This allows users to create a PivotTables and Charts within Excel as they normally would, then perform a few very simple configuration steps to make uploaded Excel documents available for consumption through smart phones and tables. Users can then interact with the PivotTables and Charts to consume live data! In fact, users can even use the PivotTable filters to filter the data displayed on the tables and charts. In the newer release of Mobile Entrée 2.1 filter selections are shown on dashboards to make the displayed data easier to understand. Due to the great design of Mobile Entrée, the consumption of PowerPivot dashboards and content is possible as well!

All in all, Mobile Entrée seems to have a large upside. From all the research on mobile solutions I have done along with all the documentation and blogs that I have read, it is hard to find much wrong with the easy setup and rich functionality of Mobile Entrée. I am already in the process of developing some more blogs which will show all of the great Mobile Entrée functionality discussed in this blog in much more detail.

Sources:

Mobile Entrée Website

Mobile Entrée Educational Site

SSAS Query Log Truncation During Synchronization

Problem:

During one of my projects I found that the SSAS query log table seemed to be periodically getting truncated. I would notice this because I would run some queries against the cube, watch them get logged into the logging table, and then a few hours later they would be gone! I could not figure this out and it kept erasing all of our historical usage data! I eventually tracked down the issue, explained in this Microsoft Knowledge Base Article. Every time we were using Synchronization to sync up our environments it was truncating the log table! Even if the Database IDs and Cube names were exactly the same. It even happened if it attempted to sync and found no differences between the two versions!

From the article, this quote explains it best:

“This problem is not fixed in SQL Server 2008 or in SQL Server 2008 R2. SQL Server 2008 truncates the OlapQueryLog table every time that synchronization runs, because synchronization replaces the destination database by using a fresh copy of the database. This affects scenarios such as synchronization, attaching an existing database, or restoring an existing database.”

Solution:

The article does offer some suggestions on how to get around this, but for us none of them were an option at the time. For this reason, we simply created a tiny SSIS package that would run and pull all the transactions out of the query log table and put them into a separate fact table. This package was set to run every time before Synchronization was kicked off. The package only takes a matter of seconds to run, and when the query log table is truncated afterwards, no harm done.

On top of this, that gave us a static table that we could use to build these dashboards!

SSAS Query Log Table Time Issue

Have you ever used the SSAS out of the box functionality to log queries? This can be used to see how many queries are run, how long each query runs, and which users are running the queries. If not, check out this blog for a description of some dashboards that can be created from this log table. If so, have you ever noticed that the time SSAS attaches to each query is in UTC?

Here is the create statement for the default query logging table created by SSAS when logging is turned on:

CREATE TABLE [dbo].[OlapQueryLog](

[MSOLAP_Database] [nvarchar](255) NULL,

[MSOLAP_ObjectPath] [nvarchar](4000) NULL,

[MSOLAP_User] [nvarchar](255) NULL,

[Dataset] [nvarchar](4000) NULL,

[StartTime] [datetime] NULL,

[Duration] [bigint] NULL

) ON [PRIMARY]

For this blog, the important field to note is the [StartTime]. As each query is logged, the starting time for the query is inserted into the OlapQueryLog table along with all the other related information (the cube, the user, the actual query, and the duration). We wanted to build a cube off of this table in order to analyze the peak and low usage Hours of the day for the system. This would allow us to help plan maintenance windows and system downtime for migrations (if needed). However, we soon found out that the usage patterns seemed to be way off, by about a quarter of a day.

Digging into this more, we soon found out that the SSAS query log table logs all activity in UTC (as far as I know this can’t be changed). For this reason, we needed a way to modify the dates as we pulled the data into our fact table, which luckily enough can be done with a simple SQL statement.

In fact, since our servers and main user base sits in the Eastern Time zone, we know we need 5 hours subtracted from the time listed in the log table (as shown in the screenshot above). We can easily just subtract 5 hours, but we can also make the SQL dynamic so that it can be used by any server in any time zone:

Select StartTime,

DATEDIFF([hour], GetUTCDate(), GETDATE()) AS [Difference],

DATEPART(HH, DATEADD([hour], DATEDIFF([hour], GetUTCDate(), GETDATE()), StartTime)) As NewStartTime

FROM dbo.OlapQueryLog

As you can see in the screenshot, I have returned three fields. StartTime being the date returned from the query log table, Difference being the delta in hours between the UTC time (“GetUTCDate()”) and the server time (“GETDATE()”), and NewStartTime being the new hour formatted in the servers local time.

We can now easily browse the cube we created by Hour and get an accurate representation of how the cube is actually being used!

SSAS Usage Statistic Dashboards

One of the most popular questions after an SSAS cube is moved into production and users begin accessing the data is, “Who’s using the cube?” It makes sense right? After spending a lot of time and money getting an analysis cube built, who wouldn’t want to know what the usage was like? Luckily, there is an easy and slick way to surface all of this information.

First, turn on query logging for SSAS. This can be done in SSMS. Connect to the cube, right click on the instance of SSAS, and select “Properties”.

Make sure to configure the following properties. Please note that it is not best practice to set QueryLogSampling to “1” since this can have negative performance impacts on a highly utilized system, however, I have set it to 1 so that I can show a true representation of cube usage.

Taking a little time to build a quick ETL solution and cube off of the usage table, you can get two nice dashboards. The first dashboard (shown below) analyzes system usage by measuring the number of queries sent to the system. This can be used to help plan system maintenance windows and downtime when the smallest number of users will be impacted. In our case this would be Saturday between 10 PM and 12 AM. Also included, but not shown below is a simple table with the Top and Bottom 10 users of the system. This way you can really tell who is actually using the new cube!

You can also build a dashboard that analyzes the duration of each query. This can be used to see when the system is hit hard, if the duration of queries is related to the amount of queries being sent to SSAS, or if the server might just need a quick reboot! As you can see, this dashboard has a very similar look and feel to the screenshot shown above.

The analysis that can be performed by this quick solution includes the following dimensions and measures:

Measures:

  1. Number of Queries
  2. Query Duration (milliseconds)

Dimensions:

  1. Date Hierarchy
  2. Hour (kept separate from the date dimension, this can be used to perform hour by hour analysis)
  3. User
  4. Cube (the cube to perform analysis on, this can be one or many)
  5. System (the system / server to perform analysis on, this can be one or many)