Microsoft Reporting Services
SQL Server product of Microsoft is offered with a reporting service feature that is very useful. The Microsoft Reporting Services or Microsoft SSRS will let you design a whole lot of reports from the database that SQL Server provides. What the Microsoft SSRS lets you do is to design reports templates, get them filled in with data retrieved from the target database and in general, manage all the reports you create in the course of your work. They Microsoft Reporting Services is supported by a variety of tools and services out of the box. These are functional soon after the product is installed.
The latest version available is the Microsoft SSRS provided along with the community technology preview of the 2016 SQL Server product. The regular release is to follow soon. The full scale production version of the Microsoft Reporting Services is the package available with the SQL Server 2014. The reports you can design with the Microsoft Reporting Services report designer is a fully integrated version of the VB reporting services. The services within the VB environment work seamlessly with the SQL Server tools and components. The free standing report builder application lets users proficient in Microsoft Office environment to start using the services very easily.
The SSRS 2014 helps users work across devices easily. Creating an interactive report that may have tabular and/or graphical data that works for mobile devices too is quite simple. The reports generated by SSRS can handle various data type quite easily and these include relational, multi-dimensional as well as XML data. Visualization of data is facilitated by easy processes to utilize sparklines, KPI, charts and maps, etc. However wide a set of features are provided, there will always be situations when some specific details need to be taken care of and programming features help. This Microsoft Reporting Services provides the tools that can help you customize reports. APIs in Microsoft Reporting Services extends access to a whole range of data and processing of these customized reports.
The mobile reports you can generate with the Microsoft Reporting Services scales quite well to various mobile screen resolutions. The reports are designed on a design area that has grid rows and columns that can be adjusted. Data in these reports can be populated from data in the resident SQL Server databases or from Excel workbooks. The Microsoft Reporting Services or Microsoft SSRS allows these reports, if you like, to be stored on the standard web server provided in the product package. These can be taken into the BI package as well as into mobile apps on a iPhone or an iPad. The SSRS reports are published to a report server. These can be published as part of a SitePoint site or a Windows application. Accessing the SSRS can be done in several ways. Report processing can be scheduled. They can be accessed on demand or obtained on a subscription basis. One can set alerts when a report gets published on SharePoint. The Microsoft Reporting Services reports can be also be exported to other applications, Excel being a useful type.
What Is Business Intelligence?
The term Business Intelligence (BI) easily confused with some related processes. These include data analytics, big data and data science. Business Intelligence or SQL Business Intelligence (BI) helps users derive actionable intelligence/information from analyzing available data through software tools. These tools, Business Intelligence software, help business managers take better decisions. Sometimes, these insights can provide a completely new business decision or tactic that would not have been obvious otherwise. These tools came as an improvement of the decision support systems that were in widespread use in the sixties to the eighties.
The BI tools need to operate on data derived externally, for example about the products of the business and their behavior in the market (both own and competitors’). Data, internal to the company, such as financial and operations data are useful for analysis and derivation of the business insights. Often about 80% of the data in the business intelligence domain can be unstructured and the tools should be able to analyze them with ease. These SQL Business Intelligence (BI) tools let you discover relations between the available data and help drive the decision making through these insights developed. Strategic decisions would typically include identifying the way forward for a business and long-term goals while tactical decision making would cover such operational issues like determining pricing and market position of the company product. The following formal definition of BI is attributed to Howard Dresner. He defines Business Intelligence as “concepts and methods to improve business decision making by using fact-based support systems.” The concepts and tools are usually lumped together as analytics or data analytics in the SQL Server BI tools available in the market.
The features available in order of higher business value delivered by these Business Intelligence (BI) tools include a set of reporting functions at the bottom of the pyramid. These just provide business owners with reports that indicate what happened to the business during a given period. Analysis functions on top of these reporting tools describe reasons behind these happenings. These and part of the next level function, described as monitoring (of current events), are supported by basic statistical functions in the SQL Business Intelligence (BI) product offerings. Next level of outcomes from the system would support forecasting functions. Regression analysis techniques are useful here.
Predictive functions of the SQL Server BI tools attempt to predict the directions the analyzed data will likely change. Possible actions are often part of the business intelligence repertoire at the next level of reporting. These techniques need support of “what if” kind of simulation tool in the BI tool.
With bigger businesses one needs to handle a huge amount of data. Managing techniques of handling such large body of data is called “big data”. As the needs of analyzing and discovering trends and patterns grow, the sophisticated needs of analysis can only be met by customized set of tools. No amount of standardized tools available with the Business Intelligence (BI) products may be sufficient. What such customized analysis should be, get decided by the characteristics of the data involved and the science behind analyzing such large data. That is the domain of data science and the persons trained in these techniques are the data scientists.
MS SQL Server
The RDBMS product offering of Microsoft for the enterprise and desktop is the MSSQL Server. Since its first appearance as a product for the PS/2+OS/2 environment in the late eighties, it has evolved through several different versions. These versions have been released roughly at a 2 year interval. The very first product was a joint effort of Aston Tate and Sybase along with Microsoft. The MSSQL Server is the latest version until the general release of the version 2016 happens. The community technology preview product version of the 2016 upgrade has already been released. The SQL Server is a product completely owned by Microsoft.
By 2005 version, all the code owned by the other two companies has been replaced with Microsoft’s own efforts. The Microsoft SQL Server (MSSQL) grew from this codebase via the 2008, 2008 R2 (appeared in 2010) and the 2012 versions. With each of these releases Microsoft kept adding features. A few were dropped that were found to be not useful anymore. MSSQL Server adds features in keeping with the same tradition. The SQL Server 2014 builds on the feature set of the 2012 version. By this time the product was supporting 64 bit environment, support for XML and multimedia data types and many unstructured data through the file stream data type. The latest SQL Server is equipped for business intelligence and analytics support needed by businesses. Deriving business intelligence (BI) predictive and prescriptive analysis, often, up to 80% of the data could be unstructured. In memory operations, that has been built-in by now, is useful in carrying out BI analyses quickly enough.
While you could load small tables into memory in many earlier versions, with sql server 2014 lets you handle complete tables in memory. One can utilize a SSD buffer pool extension feature that can speed up the disk based SQL Server applications. The Microsoft SQL Server also offers AlwaysOn feature. With this feature, high availability of data bases and easy disaster recovery are possible quite easily. This feature of the SQL server 2014 database mirroring is possible, making it very simple for database fail-over Availability groups offered by this feature increases the availability of databases for a set of users. Through the availability groups a failover environment for a group of users could be created. Availability groups help handle failovers together. Failover actions could be automatic, manual or a forced manual failover in the SQL Server. These AlwaysOn features help meet the needs of the AlwaysOn business and the need for quick turnaround of Bi data.
Like all the other server products, MSSQL Server too comes in several versions. The free Express version of the SQL Server provides the opportunity to users to get familiar with the tool. The Express version has two other versions that offer features in addition to the standard limited set of features available for the Express edition. One of these SQL server 2014 Express versions is SQL Server Express with Tools, whereas the other is with advanced services. The tools offered include the basic management studio. The advanced services include full-text search and reporting services.
MS SQL Server Management Studio (SSMS)
Microsoft released the first version of the software application MS SQL Server Management Studio (SSMS) tool back with the SQL Server 2005 version. By then the base product SQL Server had grown so much in terms of features offered that there was a need for a tool that can help administer, set up, configure and, in general manage functions available to users. From June 2015, this tool was made free-standing and SSMS application started following a release history of its own. SQL Management Studio is essentially a collection of tools. It has graphical tools as well as script editors that work with the components or objects of the main product. This MS SQL Server Management Studio tools lets the database administrators and developers work with administering, configuring and managing these objects. For this purpose the SSMS includes an object explorer. Much in the fashion of the “windows explorer”, this object explorer lets you locate an object and act upon it. The SQL Server Management Studio received a complete makeover with the application being rewritten in Windows presentation foundation (much like the VB 2010). The user interaction with the studio components became much more consistent, simple and standardized.
Azure SQL Database also can be managed with the MS SQL Server Management Studio (SSMS). Tools available are useful whether you need to deploy data bases or data warehouses and other data-tier components in the end user application you are attempting to develop. Monitoring or upgrading of these components also is facilitated in the regular of the cloud version of the SQL Server product. It is possible to download and test the current version of the SSMS directly from Microsoft official website. A comprehensive set of tools in SQL Server Management Studio help you not only manage the database engine per se, but lets you manage the integration services, analysis services functions and the reporting service.
Besides the object explorer described earlier, the SSMS contains other components that helps manage all the database instances you may have running in an application. It is comparatively simple to set up common tasks. These may be functions like setting up keyboard short-cuts, viewing page properties, etc. How to connect to different instances of the database from the MS SQL Server Management Studio (SSMS) are described. Methods to connect to analysis services from the studio are available too. The template explorer and the solutions explorer help build applications quicker and with lower error rates. A set of tool included as part of the SSMS will make the visual design of the user applications. Script editors were mentioned earlier. These tools in SSMS help building of queries and scripts interactively. Debugging these script snippets before you run and actual query based on them is also made simple. The latest release of the SQL Server Management Studio supports server versions from 2005 to the latest 2016 releases that include the support for the Azure cloud database. OS support supported by the latest SQL Server Management Studio includes Windows 10. Windows 2008 R2 and server 2012 both in 64 bit versions are supported. Support also continues for W7, SP1 as well as W8.
SSRS Report Builder
One of the heavily used tool/services available for SQL Server Reporting Services (SSRS) is the report builder tool. Users could create a wide variety of reports and be able to manage them systematically through this reporting service. SQL server integration services and SQL Server Analysis Service and the SQL Server Reporting Services make up a vital support system for the SQL Server. SQL Server Reporting Services provides a report authoring tool. Business users who are familiar with the Microsoft Office environment will find the SSRS report builder very easy to get used to and use. When creating a report, the user needs to specify the source of the data and what exact data to be picked and how exactly they are to be displayed.
The report processor in the SSRS will retrieve the data as specified and create a report for the user as per the specified template. You can specify basic data tables and charts drawn from the data in the tables. The ribbon style menu in the reporting services will let you add elements to the report very easily. Besides the tables and charts users will be able to us the map wizard and format the data to be included in the report created with the SQL server reporting services.
It is possible to export the reports created into Excel environment. Other export formats are also available in this report building tool. The report builder makes it possible to save the reports into a SharePoint library. Alternately these can be saved into a report server or if you prefer, the local host. The SSRS reporting tool also makes it possible to take a look at how the reports will look in HTML format or in print format. The tool works through a web interface. It also needs Internet Explorer 6.0 or later and .NET framework 2.0 to work properly. The SQL Server Reporting Services can get the reports from the report server. This reporting service has many features that are similar to the report designer, meant for specialist (developers, administers) use.
The user will need to take care of quite a few configuration issues before they can start using the SSRS report builder. This ad hoc tool installs when the user installs the SQL Server Reporting Services in its native mode or the SharePoint integration mode. For proper access to the report server properties that indicate the availability of the report builder on the server should be set properly. The reporting services access to specific groups or individual users is controlled and right parameters must be set. Authentication parameters determine if an instance of access is being done by authorized users.
Availability of SQL server reporting services is based on the edition you are going to use. Not all editions of the SQL server include the reporting services and hence the SSRS report builder. The SSRS was originally made available with the 2003 release. Initially it used to be an adjunct product but was enhanced significantly with the 2008 and 2008 R2 releases.
SQL Server Express: overview and features of SQL Server Express
Each of Microsoft SQL Server products released over time comes in several versions and is directed for different user populations. However, what they have in common is that each new release of SQL Server always contains a version named SQL Server Express. In the 2014 version it is a free product that allows users to download and become familiar with the capabilities of the SQL Server. The SQL Server Express versions do not incorporate the full capabilities of the other commercial versions but do content the main features available for that particular release.
The SQL Server Express product has been available since the release of the server 2000. This particular variant of the SQL Server was specifically addressed to embedded and small scale applications acquired the brand ”Express” since its release with the sql server 2005 bundle. The restrictions imposed on the SQL Server Express versions include a maximum size of 10 GB per database. This limit has progressively increased through the product revisions. It was originally 2 GB in the first avatar of the product as released with the MSDE (first version of the SQL Server product). The database size limit increased to 10 GB from SQL Server Express editions of 2008 R2, 2012 and the SQL Server Express 2014. One is not allowed to use more than one CPU for the SQL Server Express edition products. You could use multi-core devices though. Analysis services or SQL Server agent services are not available for the SQL Server Express product variant (some variants of the Express version are available). Some usage limits exist. The SQL Server Express product variant will not be able to access more than 1 GB of RAM, even though the machine physically has more availability. The SQL Server Express variant with advanced services must have 4GB per instance of the reporting services.
The SQL Server Express editions do contain some of the very useful GUI tools. These include the
- Management Studio Express version,
- Configuration and Surface Area Configuration Tools.
- Business Intelligence Development Studio is also made available with SQL Server Express editions to let the user carry out BI related work comfortably.
However some useful services that are excluded (compared to Standard and other higher level editions) from the SQL Server Express editions include Integration Services, Notification Services. Analysis Services also are not included. All of these are included in higher versions as SQL Services Analysis Services (SSAS). This service enhances the capabilities of OLAP and data mining features. These capabilities thus are going to be limited in capability in the SQL Server Express products. Analyzing and spotting the trends that can only be discerned across tables could not be done in the Express edition. Integration, reporting and analysis services are part of this SSAS package. OLAP, data mining, data warehousing and overall business intelligence capabilities will be limited with these products.
Overall, even though SQL Server Express edition has its limitations, it is a great tool to start to get familiar with SQL Server and its features.
At SQL Server Tutorial we are always happy to help with any questions regarding SQL Server Express or any other related topics. Please don’t hesitate to contact us!
Recommended links:
https://www.microsoft.com/en-us/download/details.aspx?id=42299
https://en.wikipedia.org/wiki/SQL_Server_Express
http://www.microsoft.com/en-us/server-cloud/products/sql-server-editions/sql-server-express.aspx
SQL Database: overview and six editions of SQL Database 2014
The main product from Microsoft in the RDBMS category has been the SQL Database. The main query language supported is T-SQL which is the ANSI standard SQL extended by MS. MS SQL Server was first created for the PS/2 systems in collaboration with Sybase and Ashton Tate. The first release was in 1989, an updated version was released on average every two years. By the time the SQL Database 2005 version became available, the code base was completely owned by Microsoft and the SQL Database was offered as an independent product. Sybase developed a similar product with a similar sounding name.
Microsoft offers the product in a number of editions, including an Express version that is available for free. It is limited in features but does give you a good feel of what the SQL Database can do. The latest release has been Community Technology Preview which have been made public in April 2016. SQL Server 2016 became generally available on June 1, 2016
The SQL Database 2014 editions have changed over time. The different editions of the SQL Database in any specific release are addressed to different usage scenarios. There are six editions available with the SQL Database 2014. These include an Express edition, Enterprise edition, a Business Intelligence edition, Web edition, Developer edition, and a Standard edition.
- The Express edition provides all the the necessities to get started with SQL Server. It is free and easy to use.
- The Enterprise edition is intended for enterprise level applications. These typically have demanding and mission-critical services from the database and business analysis needs.
- When you have a need for building secure BI applications that are scalable as well as manageable, you need to invest in the Business Intelligence (or BI) edition.
- The Standard edition of the SQL Database is useful for BI and core database functionalities.
The latest SQL Database 2014 has incorporated in-memory that improves performance significantly by letting users’ process complete tables to in-memory. This feature is available in all the editions of the sql database and allows minimizing the time for transporting data back and forth from disc. Even if the applications need to be disk bound, performance is enhanced through the use of extension of SSD buffer pool in the sql database. These solid-state drives can act as a cache between the hard drives and main memory. Full text search capabilities incorporated into the latest editions of the sql database.
- The “Web” edition is meant for SQL Database integrated into web applications, and only available for service providers hosting public websites that use SQL Server.
- The “Developer” version is meant for application development, test and roll out of sql database applications. It has all the capabilities of a full SQL Database.
Please contact us at SQL Server Tutorial if you have any further questions regarding SQL Server or SQL Server 2014 in particular. We are ready to help!
Related sites:
http://www.microsoft.com/en-us/server-cloud/products/sql-server/
https://www.youtube.com/watch?v=00b3baKf3R0
MS SQL Server: A Glance at the History of MS SQL Server
Microsoft has a database offering for quite some time. Their product, the Microsoft SQL Server, is a RDBMS (relational database management system). In the early days of the PC (PS/2 and OS/2 combo) era Microsoft SQL Server product was offered in collaboration with Ashton Tate back and Sybase. However, from 2005 Microsoft provides SQL Server product completely independetly. Initially Microsoft SQL Server was the database product with which a large number of clients could connect and transact database operations. After that a number of versions have followed. Latest being the SQL Server 2016.
By the time Microsoft SQL Server 2000 came out, it was extended to the 64 bit architecture of the Intel processors. Supporting tools such as an IDE (Integrated Development Environment), an ETL (Extract-Transform-Load), etc. were added. A Reporting Server has been added to the Microsoft SQL Server portfolio as has the OLAP and data mining functionalities enhancing the analysis services. OLAP or the on-line analytical processing capabilities provide data analysis feature on multiple dimensions. Trend analysis and data modeling capabilities also become available to the Microsoft SQL Server. This feature helps discover patterns from large data sets where these patterns may not be easily discernable.
There have been several major versions released for Microsoft SQL Server since the SQL Server 2005. Next versions of the product came in 2008, 2010, 2012, 2014 and now in 2016. While the general naming pattern of the Microsoft SQL Server is SQL Server 2005 (year of release), the version released in 2010 was named SQL Server 2008 R2.
The 2005 version of the Microsoft SQL Server added the support of XML data in addition to the relational data. While Microsoft SQL Server supports an extended form of SQL, the T-SQL, XQuery based query feature was added to support the xml data. This version added some extensions to the T-SQL itself so that XQuery queries could be embedded in the T-SQL queries. Data mirroring support added in this Microsoft SQL Server 2005 helps implement high availability solutions with support of automatic or manual fail-over configuration. Support for working with web services was introduced in this version too.
Version 2008 introduced support for structured and unstructured data. Support for multimedia data in the form of BLOB (binary large objects) and “filestream” type provided support for semi-structured and unstructured data. This Microsoft SQL Server version also has support for spatial or location based data. Full text search features got integrated in this version of the Microsoft SQL Server 2008. Later Microsoft SQL Server kept introducing features that make it suitable for handling larger data analytics. In-memory execution feature already exists in the latest Microsoft SQL Server product that make execution of large data analytics quite quick.
If you have any questions or would like to learn more about Microsoft SQL Server please contact us at SQL Server 2014 Tutorial for a free consultation. We are looking forward to hearing from you.
More related links:
Cloud Database: Overview of Cloud Database
Big data constantly grows. Predictions are that it will continue its’ expansion at around 60% yearly. Therefore, it is obvious that enterprises need a place where their growing database is living.
Database that runs on the cloud platform – Cloud Database – provides this much needed space. There are two ways this can happen.
First – is to run one’s own database of the rented space (a server, virtual server, shared server or whatever) in the cloud service.
Second option would be a Cloud Database, which is provided by the cloud provider hosting a database and providing user access to it. Depending on what the service provider has, it could be the database of choice of the user:
- In the virtual machine image deployment mode for a Cloud Database users could purchase server space from the provider. It is possible to run a database of the user’s choice and upload a machine image with an optimized copy of the database of choice. You could set up such a Cloud Database very easily as product vendors make it simple with readymade images for this purpose. Oracle 11g Enterprise edition ready to go image for Amazon web services the EC2. One could set up a Cloud Database with Oracle for Microsoft Azure, the same way.
- A Cloud Database service provider may also have a database as a service or DBaaS. One big advantage of this mode of Cloud Database is that it is the service provider responsibility to keep the software current and manage maintenance needs. Users can simply pay as you go (based on usage) if the portfolio has the database you need. Amazon, for example offers “SimpleDB” and Amazon relational database service as well as “Dynamo DB”. The first one is a No-SQL service while the second offering offers SQL interface. Microsoft Cloud Database service on the other hand offers Azure version of the SQL Server database.
- In a third option, Cloud Database hosting provider can support a third party offering of database as a service. Cloud service provider Rackspace for example, provides MySQL service in dedicated as well as in cloud hosted mode. Cloud Database No-SQL Database through Object Rocket’s MongoDB as a service is available on Rackspace. MongoDB-as – as a service is also available on Amazon web services as well as on Azure. Consoles provided by the cloud service providers help access and use the database instance. Users are able to launch Cloud Database instances, create backups and monitor the operations.
Choice of databases in the Cloud Database includes both the SQL and the No SQL Database products.
- Common SQL based Cloud Database include Oracle, SQL Server, NuoDB, Maria DB, MySQL, PostgreSQL, etc. Scaling up with these older versions of databases may be difficult on the cloud platform because they were designed before the advent of the cloud as a computing platform. Thus, they may have limitations in quick scaling. Cloud Database services based on these databases have started to evolve to make the scaling problem easier.
- Cassandra, MongoDB or the CouchDB are the newer No-SQL Databases and have to inherent limitations in scaling up/low. However, since existing applications mostly in SQL, it is not very easy to migrate these legacy applications into the NO-SQL Cloud Database services.
If you have any further questions regarding Cloud Database and its functions, please don’t hesitate to contact us. We are always happy to help!
T-SQL Tutorial: Where to Find T-SQL Tutorials
One of the best places to start looking, if you decided to learn T-SQL, is at its source or Microsoft’s resources. Microsoft offers T-SQL for querying its database, the SQL Server. Thus, one of the first places to look for T-SQL Tutorial would be the MSDN page. These resources have been updated for supporting T-SQL Tutorial for the latest SQL Server 2016. This T-SQL Tutorial takes the approach that the learner is new to writing SQL statements. It teaches such users write simple statements for creating tables in a database and how to insert data into such tables.
Transaction-SQL or T-SQL has extension to the standardize query language. The MSDN tutorial pages or the other popular T-SQL Tutorial pages available on-line focus on all the features of T-SQL. However, they do not focus on any comparison between the extended language and the standard one. The MSDN tutorial clearly declares its aim as giving the user an introduction and feel of the language only. The T-SQL Tutorial coverage does not include the production database complexities. In actual use case, it is recommended to use the SQL Server Management Studio. After going through the T-SQL Tutorial, user can start using the database references to find detailed information.
While practicing this particular SQL database you will learn how to create a database, create and delete tables. Insertion, deletion, updating, reading and deleting data pieces are another set of exercises in this T-SQL Tutorial. Configuring a database, creating a view and developing stored procedures are other functions that one can learn in these T-SQL Tutorial pages. While it is not necessary for the user to know SQL, basic database concepts need to be understood to assimilate the T-SQL Tutorial .
While this is a good starting point, there are several high quality tutorials available on-line that offer a very detailed information. These tutorials give you a complete overview of the T-SQL language.
- First of these T-SQL Tutorial gives an overview of the SQL in a way that user gets a whole perspective on the database query language vis-a-vis the extended form of it, the T-SQL language.
- The Techonthenet Tutorial – another one of in-depth T-SQL Tutorials that covers all aspects of the language. This particular T-SQL Tutorial does not require any pre-requisites. After finishing it, the user will become proficient not only in the query language but in SQL Server basics and features as well. Introduction to the SQL Server is provided so that it is clear how data is retrieved and manipulated in the database. The T-SQL Tutorial then progresses to creation of users, their log-ins. Creating tables, functions and procedures are also covered. This T-SQL Tutorial has a separate section on those specific T-SQL functions that are not part of the standard SQL. No pre-requisites are required for this T-SQL Tutorial; rather a structured approach is taken so that learner improves as the he-she goes through the lessons.
If you interested in learning more about T-SQL Tutorial please don’t hesitate to contact us!
| Follow us on: |    |