In the spatial data series we looked at free-form text based address data and worked towards developing a Geospatial Dashboard . In this artilce we will look at using one of the new enhancements introduced in SQL Sever 2008 R2 - Shared Datasets. Now, you can get SQL Server 2008 R2 hosting with only $ 5.00/month. You can get this opportunity in ASPHostPortal.
Why Use Shared Datasets ?
Our BI-Satellite application performed the below tasks:
1) Used data from a SQL Server table, read this data in SQL Server Integration Services (SSIS) and geocoded it using Bing Maps web services.
2) Stored the data in SQL Server Spatial datatypes.
3) We used the dataset in SQL Server Reporting Services (SSRS) and developed a report using the Map control.
4) Finally, made the data in our SQL Server table more detailed and developed a scaled down version of a Geospatial Dashboard.
To keep the focus on the technical study of the geospatial data, we kept the data relatively simple. Consider this data as the regional sales data from a company. When someone would like to view the report of sales of a salesperson , or if the senior management of the company would like to check the details of the performance of all the salesmen of the company it would be mandatory that either the geocode details of the salespeople's locations are stored in the database whenever a new record is entered or they are updated each time a location value changes.
Adding to this complexity, consider a scenario that departmental stores in some regions have shifted from one place to another. So effectively their geocode values would also change. In these cases, one would be required to maintain a batch process where each change in address would continuously require updating.
But step 2 and step 3 can be eliminated by a solution integrating SSIS and SSRS. We can use a SSIS data processing extension from SSRS, and geocode values on a "on demand" basis. SSIS packages as explained in Part I would geocode values, and this package can be used as a data source in SSRS using SSIS data processing extension. Explanation of implementation details of the same is out of scope of this article, so we would take it as granted that we have implemented this solution.
Now when we use such data source and create a dataset, it would be easier to implement for theIT staff but the same cannot be expected from business users who would need the same data for use in their reports using Reports Builder. As a solution we can opt to make the data source Shared. But when there is a complex process involved in extracting a dataset, one would also want to share the same to make it reusable and easy to use for business users. In our case the dataset cannot be created using a SQL Query as our data source type is the SSIS Data Processing Extension. So we need to create the dataset in a way that can be shared by multiple reports and business users without requiring them to get into the details of how to extract this data.
How to use Shared Datasets ?
The question of "Why" we would want to use shared datasets is now hopefully clear, but the question of "How" we would used shared datasets is yet to be answered and "What" are the advantages of the same is yet to be seen. And we would look at the same by implementing it with our BI App. As discussion of SSIS Data Processing Extension is out of the scope, we would use the same "Address" table in the normal way. Follow the steps below to implement the solution:
1) The first point that should be made is that shared datasets can only use a shared data source.
2) Right-click on the shared data sources folder in the solution explorer pane, and select "Add New Data Source". Create a connection to the database which has your "Address" table and name it something relevant. After you have created your shared data source, you would find a .rds file listed in this folder.
3) Now add a new shared data set by right-clicking the Shared Datasets directory and selecting "Add New Dataset". You should be able to see the dialog as shown in the below screenshot. Select data from Address table and name it "FamousBuildingsSharedDS". After that a .rsd file would get created, which would be listed in this folder.
4) Deploy this data source and dataset.
5) Open Reports Builder and create a new blank report. On the reports data pane, right-click Datasets directory and select "Add Dataset". Select "Use a shared dataset" option and click on the browse button. Navigate to the directory where your shared dataset would have got deployed (normally with default deployment settings in native installation mode, you would find it in datasets directory). You should be able to find the dataset we just deployed in step 2 as shown in the screenshot below. Use this dataset and name it "MyDS".
6) Now just to check that this data is accessible, add a table to the report. Select a few fields from this dataset and your report should look like the below screenshot.
Advantages of using Shared Datasets
1) The first and biggest advantage in my view is reusability. Shared datasets can be used by multiple reports, and embedded datasets are limited to the scope of report in which it is defined. Also as we discussed in this article, if you have a specific complex or a generalized dataset, and the same dataset or it's subset is used by multiple reports, it would be more efficient to implement a generic version of the dataset by using shared dataset feature.
2) As multiple reports are using the same dataset definition, this makes the definition more centralized and easier to maintain. Changing the definition of the dataset at a single location would cascade changes to all the reports using this dataset. This is an advantage, but it should be used with caution.
3) By separating the dataset element from the definition of the report, it becomes easy to individually configure datasets on a broad level. When datasets are consumed in reports, they can be tuned as per the needs of report like selection of fields can be modified, more filter parameters can be added, datasets can be cached separately from the report etc.. But the reverse is not possible, which means that individual reports consuming a shared dataset cannot override the definition of the shared dataset. In a way, this can be seen as a contract implementation by reports using this dataset. Once you have a report using this shared dataset, the scope defined by the shared dataset is guranteed to be available to consumers of this dataset. This is more desireable from an architectural standpoint.
Shared Datasets is one of the new features that is available in SQL Server 2008 R2 version. Use this feature to increase reusability of your data logic and practice a more robust architecture design.
Reasons why you must trust ASPHostPortal.com
Every provider will tell you how they treat their support, uptime, expertise, guarantees, etc., are. Take a close look. What they're really offering you is nothing close to what ASPHostPortal does. You will be treated with respect and provided the courtesy and service you would expect from a world-class web hosting business.
You’ll have highly trained, skilled professional technical support people ready, willing, and wanting to help you 24 hours a day. Your web hosting account servers are monitored from three monitoring points, with two alert points, every minute, 24 hours a day, 7 days a week, 365 days a year. The followings are the list of other added- benefits you can find when hosting with us:
- DELL Hardware
Dell hardware is engineered to keep critical enterprise applications running around the clock with clustered solutions fully tested and certified by Dell and other leading operating system and application providers.
- Recovery Systems
Recovery becomes easy and seamless with our fully managed backup services. We monitor your server to ensure your data is properly backed up and recoverable so when the time comes, you can easily repair or recover your data.
- Control Panel
We provide one of the most comprehensive customer control panels available. Providing maximum control and ease of use, our Control Panel serves as the central management point for your ASPHostPortal account. You’ll use a flexible, powerful hosting control panel that will give you direct control over your web hosting account. Our control panel and systems configuration is fully automated and this means your settings are configured automatically and instantly.
- Excellent Expertise in Technology
The reason we can provide you with a great amount of power, flexibility, and simplicity at such a discounted price is due to incredible efficiencies within our business. We have not just been providing hosting for many clients for years, we have also been researching, developing, and innovating every aspect of our operations, systems, procedures, strategy, management, and teams. Our operations are based on a continual improvement program where we review thousands of systems, operational and management metrics in real-time, to fine-tune every aspect of our operation and activities. We continually train and retrain all people in our teams. We provide all people in our teams with the time, space, and inspiration to research, understand, and explore the Internet in search of greater knowledge. We do this while providing you with the best hosting services for the lowest possible price.
- Data Center
ASPHostPortal modular Tier-3 data center was specifically designed to be a world-class web hosting facility totally dedicated to uncompromised performance and security
- Monitoring Services
From the moment your server is connected to our network it is monitored for connectivity, disk, memory and CPU utilization - as well as hardware failures. Our engineers are alerted to potential issues before they become critical.
ASPHostPortal has architected its network like no other hosting company. Every facet of our network infrastructure scales to gigabit speeds with no single point of failure.
Network security and the security of your server are ASPHostPortal's top priorities. Our security team is constantly monitoring the entire network for unusual or suspicious behavior so that when it is detected we can address the issue before our network or your server is affected.
- Support Services
Engineers staff our data center 24 hours a day, 7 days a week, 365 days a year to manage the network infrastructure and oversee top-of-the-line servers that host our clients' critical sites and services.