Data, data and  data everywhere: a typical scenario for an Enterprise DBA to handle the mission critical applications and comprehensive information across the organization using SQL Server technologies.

Since the inception of SQL Server 2005 version Business INtelligence has taken new heights for Data Warehousing and Data Mining perspective to manage the show as if it's all right here--from data mining, warehousing, and scripting techniques to MDX queries, KPI analysis, and the all-new Unified Dimensional Model. Initial information is available in the SQL Server books online (2005 & 2008) and to know more on the perception that data mining is complex. 

More help is provided online (virtual labs, webcast) from Microsoft that are useful for IT Professionals how data mining can be used in IT to support real business scenarios. Easy to understand sessions are good for the users to understand more on the point of data managing with practical information, what they can do with new BI techniques and for such information refer to this Data Mining and Business Intelligence for Enterprises Technet link and Business INtelligence Data Mining Technet virtual lab. 

As it seems the error text on the subject looks familiar when you have installed SQL Server or working with SQL Server databases. How about getting the error on Analysis Services databases (OLAP) within your BI Platform. So in the case of handling the Business Intelligence platform you must be aware of necessary data providers that are required in order to connect the end user application to backed database platform. If not various various exceptions can appear, including:

• "The specified module could not be found."
• "The handle is invalid."
• "The parameter is incorrect."
• "Errors in the metadata manager."
• "A connection cannot be made. Ensure that the server is running."
• "An unexpected internal error has occurred."

I was able to produce the text of these errors based on the occurrences within my platform, it is obvious that the text may be different if there is an issue of security and not with the data providers you use, in such cases the exceptions includes::

• "Unspecified error."
• "Either the user, Domain\User, does not have access to the Database Name database, or the database does not exist."
• "An existing connection was forcibly closed by the remote host."

So how it is possible to find or obtain the 'right' data provider or avoid such type of common errors, I feel it is worth covering from SQL Server 2000 version onwards where the Analysis Services has obtained much of importance for managing data warehouse based applications. There are various ways to resolve them provided you have identified the required  information.

Within SQL Server 2000 version OLE DB drivers usage has been obvious to connect to other data sources such as excel, oracle etc. in order to install the SQL Server Pivot table series look at this this link. But there is a difference when it comes to SQL Server 2005 Analysis Services, usage of ADOMD.NET (.net based services) has been introduced with Visual Studio versions and to download such drivers refer to this link, or for older versions of Visual Studio download ADOMD.NET 8.0 along with the hotfix here. Its worth referring the use the OLE DB (COM) Provider for Analysis Services 2005 with an additional pre-requisite of Core XML Services installation.

When you have the database and provider to connect the end-user API you need a connection string that will identify the language compatibility between these layers. IN such cases for both ADOMD and ADOMD.NET, the connection string parameters are listed here in the MSDN Library. If in case your Analysis Services database is on seperate machine and the application generates calls from the web server, so the usage of  Windows domain account is common when running OLAP Services. This account can then be granted access in Analysis Services. In ASP.NET, you can use impersonation to change the identity of the ASP.NET process, or in IIS6 or above change the identity of the application pool used for the web site. To secure such connections you can adopt the HTTP access to Analysis Services. This is more complicated than using Windows domains but lets you extend access outside of your local intranet. To end up the subject of connectivity issues I suggest the 2 articles on Microsoft about Configuring HTTP Access to SQL Server 2005 Analysis Services on Microsoft Windows XP & Configuring HTTP Access to SQL Server 2005 Analysis Services on Microsoft Windows Server 2003 type of configuration.

Granting permissions to relevant account is a required criteria within the SQL Server databases management, for this even Reporting Services is not exceptional. As per the configuration by default the users connect to the report server by using their own Windows domain credentials and integrated security. TO change this criteria you can also configure a report server to use forms authentication if you create and deploy a custom authentication extension, or basic authentication if the report server is deployed in a Workgroup.

Once this authentication process is completed the report server checks for permissions that authorize access to report server content and operations. The permissions are defined in role assignments that describe which tasks a user can perform. Each user who connects to a report server must have role assignments defined on the account that he or she uses to connect to the report server. To clarify further Books ONline clearly states that:

Because the report server is implemented as a Web service and Microsoft Windows service, each service must be able to connect to the database. When configuring the connection to the report server database, you can choose from the following approaches:

• Use the service accounts. Each service runs under its own service account. You can use the service accounts to connect to the database.
• Use a domain account. Both services connect by using the single domain user account that you specify.
• Use a SQL server login. Both services connect by using the single database user account that you specify.
  

So in order to execute a report that account must have the verification of the RSExecRole which is a database role in the report server database and temporary database. RSExecRole must have select, create, update, delete, and reference permissions on the report server database tables, and execute permissions on the stored procedures. 

Few times you may get the following error when you are trying to select the option execution from a report:

EXECUTE permission denied on object 'xp_sqlagent_notify', database 'mssqlsystemresource', schema 'sys'.

Its a general assumption that if the user has full privileges on the user database and reportserver & reportservertempdb the process is completed, in order to proceed further you should need to grant permissions to SQL Agent stored procedures that are used by Reporting services when you schedule as a job. You can use following TSQL to accomplish the task:

USE master

GO

GRANT EXECUTE ON master.dbo.xp_sqlagent_notify TO RSExecRole

GO

GRANT EXECUTE ON master.dbo.xp_sqlagent_enum_jobs TO RSExecRole

GO

GRANT EXECUTE ON master.dbo.xp_sqlagent_is_starting TO RSExecRole

GO

USE msdb

GO

-- Permissions for SQL Agent SP's

GRANT EXECUTE ON msdb.dbo.sp_help_category TO RSExecRole

GO

GRANT EXECUTE ON msdb.dbo.sp_add_category TO RSExecRole

GO

GRANT EXECUTE ON msdb.dbo.sp_add_job TO RSExecRole

GO

GRANT EXECUTE ON msdb.dbo.sp_add_jobserver TO RSExecRole

GO

GRANT EXECUTE ON msdb.dbo.sp_add_jobstep TO RSExecRole

GO

GRANT EXECUTE ON msdb.dbo.sp_add_jobschedule TO RSExecRole

GO

GRANT EXECUTE ON msdb.dbo.sp_help_job TO RSExecRole

GO

GRANT EXECUTE ON msdb.dbo.sp_delete_job TO RSExecRole

GO

GRANT EXECUTE ON msdb.dbo.sp_help_jobschedule TO RSExecRole

GO

GRANT EXECUTE ON msdb.dbo.sp_verify_job_identifiers TO RSExecRole

GO

GRANT SELECT ON msdb.dbo.sysjobs TO RSExecRole

GO

GRANT SELECT ON msdb.dbo.syscategories TO RSExecRole

GO

Also ensure to check whether the RsExecRole is created and consists relevant permission stated above within ReportServer, ReportServerTempDB Databases.

During a conference few users asked about resources and information to go through the Reporting Services, as it is a general assumption from the end-user community that referring to Books Online aka BOL doesn't get them anywhere. I always recommend to read through BOL if you need a basic information you can further see this BOL-WhatItIsFor? article I wrote on how best you can get information on BOL.

So back to the resources on Reporting Services you can go through the following articles that are lined up on Technet site:

InfoCenters are navigation topics in the SQL Server 2005 Books Online that link to the information that is most important for people working with Reporting Services in different roles.

• Roles and InfoCenters
• Reporting Services Administrator InfoCenter
• Reporting Services Developer InfoCenter
• Reporting Services Analyst InfoCenter
• Reporting Services Information Worker InfoCenter
  

Getting Started:

• Introducing Reporting Services
• Installing SQL Server Reporting Services
• Download Eight New SQL Server 2005 Report Packs
• Business Intelligence and Data Warehousing in SQL Server 2005
  

Deployment within Business Intelligence platform:

• Implementing Smart Reports with the Microsoft Business Intelligence Platform
• Using SQL Server 2005 Reporting Services with SAP NetWeaver Business Intelligencet
• Using SQL Server 2005 Reporting Services with SQL Server 2005 Express Edition
• Migrating from Business Objects Crystal Reports to SQL Server 2005 Reporting Services
  
• Managing and Working With Published Reports
• Troubleshooting Reporting Services

Development practices:

• Reporting Services: Using XML and Web Service Data Sources
• Using Visual Studio 2005 to Perform Load Testing on a SQL Server 2005 Reporting Services Report Server
• Integrating Reporting Services into Your Application
• Designing and Delivering Rich Office Reports with SQL Server Reporting Services 2005 and SoftArtisans OfficeWriter

Making best use of SQL Server Integration Services within your ETL processes is a flexible choice, which has a huge share within the Business Intelligence application platform. You may be aware that SSIS introduces several new and enhanced features and tools to help you manage packages for ETL processes more effectively. To be specific about IS Service which is another set of Windows Services that needs to secured properly with account privileges that will be used to manage package storage and displays a hierarchical view of saved packages in SQL Server Management Studio, also this service packages are stored in the msdb database in an instance of SQL Server or in the file system.

For a brief note on how you can manage the packages is to use BI Development Studio (BIDS) such includes graphical tools and wizards for building and debugging packages; tasks for performing workflow functions such as SQL command execution, FTP operations, and e-mail messaging; data sources and destinations for extracting and loading data; transformations for cleaning, aggregating, merging, and copying data; a management service for administering Integration Services; and application programming interfaces (APIs) for programming the Integration Services object model. More on the tools side of it you can refer to SQLStorageEngine-Tools blog section here.

Coming to the best practice of designing the ETL processes is to discuss that say that whole data exists on a database, even wtih ETL tables and stored procedures including the dimension tables, fact tables and reporting views for report purpose.  This kind of setup is quite common I can say and you must adopt a better backup strategy in order to ensure the data is not compromised if any issue occurs, otherwise it will be another big procedure to get back what you need. As refers in the best practices for ETL purpose it is always better to work with STAGING tables in the database or additonal database, where the code and tables will be used for the purpose of performance and reduce the load on the live database that is used by other processes. Then comes the hardware setup on the SQL Server where grouping the correct set of disks with SAN luns etc. and this is more important on the terms of performance as any process initiated by SQL Server or BI ETL it will have knock on affect if the hardware resource such as memory or disk is not optimized enough to resource, overall everywhere it is a  matter of fact that performance is a HUGE problem on such type of application.

Also having the DATAMART type of application, where you need to provide resource for exception handling that can bring down the server resources. In this case a single solution often are confined to few servers and a single application, integration solutions span servers, networks, and applications. As a result, integration exceptions can propogate at any juncture in an integration solution. This is where you can take help of BizTalk and integration with Sharepoint to resolve such exceptions. References on Biztalk are MSDN-article on biztalk adapter & TechSavvyGal blogs will be helpful for you.

Also the ETL processes with DataMart type of application is purely dependant on the number of tools involved such as BizTalk, Reporting Services for your reporting purpose and SSIS for major ETL programmability. If you can manage the execution of ETL with these tools from Microsoft or third party it will be an easy path to manage the process, also choosing the better hardware in spreading the load of SQL processes sequentially will optimize the ETL performance.

Another tool within BI platform to use when you have the huge farm of Analysis Services data warehousing & mining database setup at your end.

So the provision of BI within SQL Server 2005 is a complete enterprise-ready and scalable data warehouse platform you can set, with the  features and functionality you need to put critical, timely information in the hands of employees across your organization. Integrate multiple data sources quickly with SQL Server 2005 Integration Services. Enrich data and build complex business analytics with SQL Server 2005 Analysis Services.  So when you have such a setup the Reporting Management is also an important to deliver the rich reports with your SQL Server database using Reporting Services and other tool such as Performance Point Server.

To go into the details this Office PerformancePoint Server 2007 is an integrated performance management application designed to help improve operational and financial performance across all departments and all levels of your organization. So what you can do more within this tool, monitor progress, analyze what is driving variances, and plan your business from budgeting to creating management reports. This will also give you the  metrics, key performance indicators (KPIs), and reports delivered to every desktop through intuitive scorecards, dashboards, and the easy-to-use 2007 Microsoft Office system environment. Recently I have been asked to evaluate this product and I have no doubt in stating this is 'a' key component of the BI offering that will get a better understanding on how performance can align with personal and departmental goals and objectives.

So now the question is the resources such as learning & product features, you can get more information from below links:

http://download.microsoft.com/download/e/f/e/efe23d78-7987-4895-b4b9-c8f421fcb77c/PerformancePoint%20Server%20Datasheet-%20Management%20Reporting_10_07.docx - data sheet  for management reporting

http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?culture=en-US&EventID=1032353652&CountryCode=US - Webcast

http://download.microsoft.com/download/4/5/2/452d8197-1940-4442-8134-b6b82f57cfe8/PerformancePoint%20Server%20datasheet-Overview_10_07.docx - Datasheet

http://www.microsoft.com/business/performancepoint/resources/training.aspx - online training with a fastest way to learn the basics of PerformancePoint Server 2007 is through these self-directed learning courses, which have been designed to give 5 a quick start on building your performance management solution.

As blogged here before Virtual Labs are very handy for the new users and existing professionals to test out new features without disturbing their own platform. In this scenario Microsoft events are helping the users by providing access to their virtual platform for BI & Relational Databases that are built in latest operating system with new SQL Server version (2005 onwards).

During last year Tech-ed coference I have seen much rush towards BI - Data Mining stalls where users were very much interested to try out them, may the ones who missed to get hands on can try now by going to ThisWebcast that will give you much of route.

 As it says:

After completing this lab, you will be more familiar with preparing Excel data for building mining models. You will walk-through the process of applying a mining model to a new data set and then view the results in Excel. This will be done by acting as a representative from a sporting goods company that wants to take advantage of historical data on existing customers to create a marketing campaign to find out which prospects are most likely to purchase a bike.

So far the SQL Server 2008 Analysis Services new features were included until October CTP, top features that Microsoft would like to include within RTM version:

• New Aggregation designer - to browse and modify aggregation designs that are now shown grouped by measure group.
• Simplified and enhanced Aggregation Design and Usage-Based Optimization Wizards, this will help to modify the storage settings for the aggregrations and append new aggregations to an existing aggregation.
• Backup and Restore functionality with new storage structures, this will to enhance the performance during the backup/restore processes.
• Robust repository for archived databases with no limit on the size of database file or number of files it can have.
• Improvied Performance as it referred on the CTP BOL it only refers to the backup & restore functionality, for the actual figures unless you test this feature it is hard to say lot of improvement from previous versions.
• IIS requirement for Reporting Services is not required which will lessen the burden of pre-requisite within the BI platform.
• Change Data Capture as in Relational engine that will help warehousing scenarios and reduce your load time by a factor of 9x.
• Reporting Services scalability, that will load upto million row reports (that is dependant on hardware resources too), with a flavour of table and tablix.
• Integration Services thread schedule performance, for the data flow processes this is an important factore and also during synchronous transforms.
• Like third party tools Authoring Reports using Excel/Word style, may be a pinch to current market tools.

Many more to come, but for sure you can download the latest CTP which is February CTP is availabel to test these new features.

When you use SQL Server Profiler, note the following:

• Trace definitions are stored with the Analysis Services database by using the CREATE statement.
• Multiple traces can be running at the same time.
• Multiple connections can receive events from the same trace.
• A trace can continue when Analysis Services stops and restarts.

One of the [SSP] forum user asked about what is OLAP, how the process flow occurrs when update happens to a fact table!

It sounds like the basic question for Analysis Services user, OLAP - On-Line Analytical Processing for BI - Business Intelligence is useful for a fast and interactive access to aggregated data and the ability to drill down to detail, such an useful for Marketing/Sales personnel. SQL Server OLAP engine works on the performance by letting the users view and interrogate large volumes of data (often millions of rows) by pre-aggregating the information. Cubes and Fact tables are common table layers on the backgrou that puts the final data-presentation needed to make strategic decisions directly into the hands of the decision makers, not only through pre-defined queries and reports, but also because it gives end users the ability to perform their own ad hoc queries, minimizing users' dependence on database developers.

Basic layer of OLAP leverages data from a relational schema (data source) by using key performance indicator (measures) into the data-context (dimensions) that will process into multi-dimensional database (cube). All the indicators for performance are pre-loaded and aggregated when a data retrieval is initiated, a significant performance difference compared to relational database. Such in a state the processed cube can then be made available to business users who can browse the data using a variety of tools, making ad hoc analysis an interactive and analytical process rather than a development effort. Between the SQL Server version such as 2005 and 2000 the BI suite has a substantial improvement in terms of performance, management & capability. 

In this case the process flow for the OLAP application involves another layer of table-access (that is referred in the Technet documentation) ...one of which is by providing the capability to temporarily or permanently write values back to a set of cells within a cube. This capability, called cell writeback, supports strategic planning by facilitating interactive "what if" analyses. You can temporarily write values to a cube and examine the resulting aggregations locally, without actually changing data on the Analysis server. Once you have completed your planning activity and have a useful set of values, you can permanently write the values to the cube.

Permanently writing a value to a leaf cell in a cube requires an individual writeback operation, which writes a single record to a writeback table. However, permanently writing a value to a non-leaf cell can require hundreds of thousands of individual writeback operations, each of which writes a single record to a relational database. The value for a non-leaf cell must be allocated across all of the leaf cells that are subordinate to the non-leaf cell, and then the values of the leaf cells are aggregated to provide the value of the non-leaf cell. Because this process can require a high volume of individual transactions, permanent cell writeback operations can significantly affect the performance of the underlying relational database.

Further  information on the internals and operations of OLAP within BI look at  http://sqlserver-qa.net/blogs/bi/archive/2007/07/03/sql-server-2005-analysis-services-operations-guide.aspx post too.

Business Intelligence (BI) - is the new term from Microsoft for all Data Warehousing & Data Mining requirement since the SQL Server 2005 version is released. Say many organization face challenges with the Data Explode, this BI offers management and development tools for IT department and Business segment within your organization with a perfect analysis and consumption tools for non-technical business users.

If you are new to the SQL Server field and to make use of BI within your needs then try downloading the BI suite. This BI suite consists a complete suite of server, client, and developer products, supports decision makers in developing deeper knowledge and insight that helps you make better business decisions, faster. This suite also integrates with the new 2007 Microsoft Office system that is integrated with the proven and scalable SQL Server platform.

• For SQL Server 2005 tools you could Download the trial software that will consist of Integration Services, Reporting Services including Relational database engine that will work for 180 days as an Evalution software.
• Going further you can even try using SQL Server 2008 (CTP) and Download the Pre-release (CTP) Software from here.

That BI suite consists of integration with Microsoft Office tools such as Word, Excel & Powerpoint, now they are not a simple desktop related tools anymore you could try using new Office Professional 2007 by going to Download the trial software from here. Similarly to manage the complete platform with Microsoft tools you could make use of Sharepoint Server too, and you can try Download the trial software from here.

I was going through Books Online in learning the concepts of slicing of data within Analysis Services for the new project that is upcoming, all these years I have basic knowledge of SSAS and with latest available features in SQL Server 2005 Analysis Services there is more to learn from there.

Slicing is the one that is most useful when you have a huge analysis services database as you can use this view to examine the structure of a cube and to check data, calculation, formatting, and security of database objects. You can quickly examine a cube as end users see it in reporting tools or other client applications. When you browse cube data, you can view different dimensions, drill down into members, and slice through dimensions.

Further information on this subject can be found from SSAS Partition Slicing link.

Got this update from SQLCAT blog on MSDN about new whitepaper on Analysis Services many-to-many dimensions that helps in MDX query performance  optimization steps you need to take care.

Since the evolution of SQL Server 2000 version the backup and restore methods have been enhanced for better performance to provide a better option for the DBA, as usual the backup and restore operations are an integral part of the Analysis Services infrastructure. The same methods/process has been completely rebuilt in SQL Server 2005 relational engine and Analysis Services.