Hi. Welcome to the fourth in our webcast series introducing Alerting and Reporting Pack ARP 1.0 with Dell Site Administrator 5 for SharePoint. If you've seen the prior videos in this series, we've already introduced what's in the ARP, how it relates to Site Administrator and Microsoft's BI tool set in the first module. Our second video module walks through how to configure SQL Server reporting services as well as get the ARP up and running and to get Site Administrator tuned to properly deliver data to those reports.
The third edition, I walked you through how to work with those reports and also take advantage of SSRS's intrinsic capability to deliver real time alerts to end users and administrators alike. Today we're going to look at the fourth part of that, and that's how to take the data that Site Admin has and integrate it with external line of business systems to create a more rich and dynamic reporting experience for everyone.
Quick summary. The ARP was released in July of 2013. We announced it on our website at sharepointforall.com. You can follow the URL listed here at dell.to/18zQbA1 to download a free copy of the ARP and get a summary of what's inside of it as well as the documentation for how to install and work with it.
The ARP was designed to solve four issues. We wanted to use the intrinsic capability of Microsoft's BI tool set to deliver alerts. We know that there are customer demands to customize those reports and to be able to take those reports and embed them inside SharePoint, as we've talked to you before.
But lastly, we know that there's sometimes external data that's equally important. And how we marry up the information and metrics that Site Admin gathers with what you have in other data repositories in your Enterprise is a really powerful way to take reports to the next level. Today we're going to be walking through that scenario.
The ARP has, as you'll recall, includes these reports, each of which correspond to a report that's inside of Site Administrator today. Each of those reports will export its data to the table that's indicated here in this chart. That's the base level of ARP.
Today we're going to be working with that sample integration report. And the scenario we're going to walk through is one that we know is fairly common in most organizations. Site Administrator collects a lot of data about the size and growth of content in databases. Many organizations use a chargeback model, and there will be separate pools of information which will collect information about how different sites should be charged, related to cost centers and billing rates.
So to do that, we're going to open up something called SQL Report Builder 3.0. Report Builder 3.0 is included as part of SQL Server Enterprise or BI editions. It is a browser download. It's what they call Click Once browser download, available on demand, and it runs on the desktop.
You can still build reports the traditional way inside Visual Studio and its related flavors, BIDS or SQL tools. And those have been available in older versions of SQL for a while. Microsoft over the last few iterations has introduced Report Builder as a way of putting report creation into users' hands, but it's also a powerful tool for admins and developers as well. And we'll be walking through that as well.
So if you may recall on our site, we uploaded all of the reports there, hosted them in a library. And inside this library, we have a report for site collections. Let's take a look at the native Site Collections report. When we capture this information, we're looking at all the web apps across the farms, and what are each of the site collections inside every one of those web apps, and collecting information about how long it's been there, who the admins are, when was it last modified, but most importantly, how big is it.
So when this data was last captured, which was about 6:00 AM this morning, we had about 847 gigabytes of content in there. So let's assume that there's a different system, and we want to go out and capture some business information and apply billing detail to that. That report has already been built and already been integrated here. Let's take a look at it.
That's called the Site Collections Billing report. Let's open it up on the screen, and then we'll plunge into what it is and how it was built. So the Site Collections Billing report takes that information and joins it with some external data source that's going to match up Site Collections with cost centers and billing rates to be able to apply a monthly cost for each site collection based on how much storage it's got.
We can see right here that the main SP 2013 demo site, which is the bulk of our data, about 708 megabytes, is going to generate about $17.72 in billing. It's part of Cost Center 2002. Different billing rates are applied to different sections. And this report then could be generated and handed over directly to finance or exported further using the intrinsic capabilities of SSRS to be able to send this to other sources.
Let's take a look at how that's built. So I'm going to jump back onto my server. Here we are. And let's go over to where we built these reports. These reports can always be opened up directly here in the browser, as we've done before. But once that report is up and running, I'm going to be able to do something here in the Actions menu, which is to open it up inside Report Builder inside the editor.
Now, in the interest of time, I've already downloaded that application, and that's up and running over here. Let's take a look at that. So here's the Site Collections Billing report. We look inside of this, and we'll see that there are two data sources.
Now, we talked before about how to configure the sample data source file for SASP repository. We take a look at that. We see that this points to a shared file that lives out there that's going to go out and define the connection to the default database for Site Administrator QSP repository. Your actual name-- in fact, the target server that you put it on is something that you specify during the Site Admin installation.
We also have a second data source here. And this relates to a different set of Business Intelligence systems. We see it points to MSSExternal, which is a separate data source file. I'm going to take a quick peek at what's inside of that guy by looking inside Enterprise Manager.
So if we jump over to Enterprise Manager, here's QSP Repository. You could see the table structure down there and all of the information that corresponds to each of those reports. So this SA for SP rep data site collection source, if we open this up, let's select the top thousand rows.
This is the underlying data table that feeds into our SSRS report. And it was what was used to run the data behind the scenes. But as you note, if we page through this, we've got size information but we have no billing detail whatsoever. The belongs to a different system. And we know that this different system could live inside SQL Server or even something inside Oracle or in the cloud, based on what your Business Intelligence source is.
By no means are we suggesting that you are constrained to SQL Server. But in this case, for the ease of the demo, we have a separate database out here. Inside of it, I've got two tables, one showing web apps by cost centers. Let's take a look at that guy.
So this is showing me for every web app URL, these are actually site collections. What cost center does that correspond to? 1,001, 2,002, 3,003, 9,009. And then a separate table that interprets what those cost center chargeback rates are. So we can see that 3,003 is the most expensive chargeback rate. 1,001, 2,002. We have all of that data in the table right there.
So in our report, once those data connections are out there and established, we build a data set. If I look at the properties of that data set-- let's open this up inside Query Designer, makes it a little friendlier for us to see.
You'll see that this is a slightly complicated SQL Select statement where I'm going out and I'm grabbing core data from that Site Collections table, which I'm aliasing as SC. So I'm grabbing all the fields from the Site Collection table.
And then I'm doing two what are called left outer joins. And in SQL parlance, that means we're going to grab everything from the Site Collection master table, and if there's a map on URL, we're going to go grab the cost center, and if there's a map on cost center, we're going to go grab the rates. So we're not necessarily going to exclude any data if a site comes up and it just hasn't been added to a cost center or there's no rate information for that cost center.
So that query's out there. It's reaching across two separate data sources, MSSExternals and QSP repositories. And with those two, we're able to then run the query. And we can see that our sample data, it shows here in the Query Designer. You can inspect it and making sure all that information comes over. And we now have a cost center as well as a chargeback rate for each area. Perfect.
Now I can go in and tweak the report accordingly. So here's this Site Collections Billing report. And it's been modified slightly from the native Site Collections report that we provide in the ARP. With this column over here, month cost, let's take a look at what's inside of that.
If I look at that expression, if I right-mouse-click on it, click on Expression. And I'll see this logic. And what it's telling me it wants to do is it wants me to sum up the size in value, because it's measured in kilobytes, multiplied by the chargeback rate. I'm dividing by a million, because I've got raw values out there. And that's going to give me a value.
I can make a few further changes to this report. If I wanted to make-- here's a simple one. If I wanted to just re-caption this, call it URL. I'm in the Designer. I can make further adjustments and changes to this if need be. I can preview this report directly here by clicking the Run button. It's going to go and execute that report here inside the client and show me what it's got.
Finally, if, when I'm ready, I can go save this report or save it as something else. Site Collections Billing Demo. And where do I want to put it? It's got a recent sites and servers. It sees that I've been connected to BI SASP. We can put it back in. It is a file just like other files. And I can save it back to the library where my other reports live.
And we're good to go. Once I finish that, then any user with the appropriate permissions to see that library and see that file is going to see the new report as I've published it. And when they click on it, they're going to receive the new information. And we know it's the new one, because we made that oh-so-important change to capitalize the word, URL, in the report.