|
|
| A Cooperative Effort between SQL Server and Analyzer |
|
|
|
|
Microsoft® has a long history of solid growth in SQL Server Business Intelligence capabilities. Starting with the BI features introduced in SQL Server 2000, Microsoft then significantly added to SQL Server’s BI capabilities, scalability, and performance with both SQL Server 2005 and 2008 and its latest release, SQL Server 2008 R2. With every new release of SQL Server, more and more companies are discovering how they can implement world-class, enterprise-wide Business Intelligence at a fraction of the cost of traditional BI systems.
|
|
|
Using Analyzer and SQL Server, a comprehensive enterprise BI solution can be deployed in a matter of weeks or months, much more quickly and at a much lower cost than traditional BI systems. Strategy Companion’s strong partnership with Microsoft, and Analyzer’s seamless integration with SQL Server Business Intelligence, brings the BI engine and front-end together in a single, proven solution providing high-quality analysis, deeper business understanding, and actionable insights.
|
| |
|
|
|
| Why Microsoft? Why Analyzer? |
| The foundation of the Microsoft Business Intelligence (BI) offering, Microsoft SQL Server 2005 gives you the ability to: |
|
 |
Integrate multiple data sources quickly–with SQL Server 2005 Integration Services (SSIS). |
|
|
Write, manage, and deliver rich reports–with SQL Server 2005 Reporting Services (SSRS). |
|
|
Enrich data and build complex business analytics–with SQL Server 2005 Analysis Services (SSAS). |
|
| Analyzer takes full advantage of SQL Server 2005’s new capabilities in the areas of: |
| As business intelligence and Analyzer become pervasive and are used by more and more workers, both inside the organization and out, it’s increasingly important that the underlying database is available 24/7 and can grow with the business. SQL Server 2005’s enhancements in the areas of availability, scalability, manageability, and security ensure that it will be able to satisfy the increasing demand for data. |
|
| SQL Server 2005 offers 35 percent faster transaction processing, which means as your data grows your processing time won’t. With improved backup and recovery, and a five-times faster failover, users can spend more time using Analyzer to discover business insights with full confidence that the information they use and today will be there tomorrow. |
| |
| |
|
 |
Enterprise Data Management |
| |
| As business intelligence and Analyzer become pervasive and are used by more and more workers, both inside the organization and out, it’s increasingly important that the underlying database is available 24/7 and can grow with the business. SQL Server 2005’s enhancements in the areas of availability, scalability, manageability, and security ensure that it will be able to satisfy the increasing demand for data. |
|
| SQL Server 2005 offers 35 percent faster transaction processing, which means as your data grows your processing time won’t. With improved backup and recovery, and a five-times faster failover, users can spend more time using Analyzer to discover business insights with full confidence that the information they use today will be there tomorrow. |
|
|
|
Developer Productivity |
| |
| SQL Server 2005 now lets you take advantage of your IT staff’s existing Visual Studio skills. Developers can build new applications using a combination of Analyzer and SQL Server 2005 with Visual Studio/Business Intelligence Development Studio, which is now integrated with SQL Server 2005 and is the only tool needed for Transact-SQL, XML, Multi-Dimensional Expressions (MDX), and XML for Analysis (XML/A). In addition, both Analyzer and SQL Server 2005 can be customized to work with existing applications through the use of web services, relieving developers from having to learn and work in multiple development environments. |
|
|
|
Business Intelligence |
| |
| SQL Server 2005 delivers an integrated platform that manages data from the point of extraction to the point of presentation. Improvements to the ETL tool (Extract, Transform, and Load) helps organizations more easily bring together data from multiple sources, and SQL Server Analysis Services® (SSAS) and Reporting Services® (SSRS) allow data to be transformed into useable metrics. Analyzer completes the solution with its easy-to-use, feature-rich end user reporting and analytical tool. |
|
| SQL Server is the platform organizations worldwide trust to manage their data and is more widely deployed than any other data management platform. Its robust functionality and proven history make it the perfect foundation for Analyzer and your enterprise-level business intelligence applications. |
|
|
|
| |
 |
|
| SQL Server 2008 Business Intelligence |
|
SQL Server 2008 brings numerous Business Intelligence enhancements designed to improve performance and increase
developer productivity. The performance enhancements do not require modifications to your existing SQL Server BI
solutions.
|
|
|
Subspace Computations in Analysis Services |
| |
| The SQL Server Analysis Services formula engine devises a query execution plan to retrieve data requested by a query. Each node of the query execution plan utilizes one of two types of evaluation modes: a subspace computation mode or a cell-by-cell evaluation mode. If the subspace computation mode is used, the Analysis Services formula engine operates only on cells with data that contribute to the result. If the cell-by-cell evaluation mode is used, the Analysis Services formula engine operates on each cell that theoretically could contribute to the result, regardless of whether it contains data. In a sparse cube, which is typical, retrieving data using the cell-by-cell evaluation mode is inefficient for calculation-intensive queries, because many null cells (which do not contribute to the result) are operated on. |
|
| In SQL Server 2005, before Service Pack 2 (SP2), the query execution plans devised by the Analysis Services formula engine primarily contained nodes that utilized the cell-by-cell evaluation mode. In SP2 of SQL Server 2005, a small number of MDX functions support the subspace computation mode. In SQL Server 2008 Analysis Services, the vast majority of MDX functions use the subspace computation mode. With SQL Server 2008 Analysis Services, tremendous performance gains have been observed with the following: |
|
‧Sparsely populated cubes
‧Cubes with complex calculations |
|
| These performance gains with SQL Server 2008 Analysis Services are achieved without modification of your business intelligence solution. In addition, you can frequently further increase the performance of cubes with complex calculations by performing additional tuning of your MDX calculations in the cube. For more information, see Performance Improvements in MDX in SQL Server 2008 Analysis Services and the SQL Server 2008 Analysis Services Performance Guide. |
|
|
|
Scalability and Availability Enhancements in Analysis Services |
| |
| In SQL Server 2005, backups of large Analysis Services databases have limited scalability, and Analysis Services
supports only a single location for all Analysis Services databases within an instance of Analysis Services, although
measure group partitions within a database can be distributed across multiple drives to distribute the I/O.
Furthermore, after processing a single database for an Analysis Services instance on a processing server, you cannot
detach the processed database and then attach it to a query server for increased scalability and availability.
In addition, in SQL Server 2005 Analysis Services, attaching a read-only copy of an Analysis Services database to multiple
servers for increased scalability and availability is not natively supported; this capability is supported only through
the use of SAN snapshot technologies (see Scale-Out Querying with Analysis Services Using SAN Snapshots) |
|
In SQL Server 2008 Analysis Services, these issues are resolved with the following scalability and availability enhancements |
|
|
You can back up an Analysis Services database of any size. The performance of Analysis Services database backups scales linearly, and this performance is comparable to file-based backups.
|
|
|
You can deploy each database within an Analysis Services instance to its own storage location, enabling you to place each Analysis Service database within an instance on separate drives.
|
|
|
You can detach an entire database from an Analysis Services instance, copy or move it to a new location, and then attach it to another Analysis Services instance. This feature increases scalability and performance by enabling you to process new data into an Analysis Services database on a processing server while users query the current version of the Analysis Services database on a query server. After processing is complete, you can detach the processed database from a processing server and then attach it to the query server. Furthermore, if you utilize two Analysis Services instances on the query server and use a load balancer to distribute queries to the appropriate instance, you can direct new user connections to the newly attached database without affecting availability, because currently connected users can complete their currently running queries against the previously attached database. This feature also enables you to detach a database from an Analysis Services instance for archiving or backup purposes. |
|
|
You can attach an Analysis Services database in read-only mode to multiple Analysis Services instances for increased scalability and availability. The read-only database can be stored on a SAN and mounted to multiple instances, or it can be shared via a file system share and mounted to multiple instances. |
|
Note: With large databases in which history partitions do not change, you will want to use high-speed SAN copy capabilities to clone the detached database or use Robocopy. For information about using Robocopy, see Sample Robocopy Script to customer synchronize Analysis Services databases. |
|
|
|
|
| |
The following diagram illustrates the use of these new features with a single processing server attached to a database in read/write mode and four query servers attached to a single read-only database. A load balancer distributes queries among the four query servers. |
|
| |
|
| ▲ |
Figure 1: Query Scale-Out using Read-only Databases |
|
|
| |
Optimized Design Experience in Analysis Services |
| |
You design SQL Server Analysis Services solutions in Business Intelligence Development Studio (BIDS). In SQL Server 2005, when you design your Analysis Services solution in Business Intelligence Development Studio, it is too easy to design a solution that does not follow best practices performance guidelines and as a result has performance issues. For example, the failure to design appropriate attribute relationships is a significant design and performance issue, but SQL Server 2005 Business Intelligence Development Studio does not notify you if you fail to create such relationships. In addition, with SQL Server 2005 Analysis Services, you frequently have to manually design aggregations to achieve optimum performance |
|
In SQL Server 2008 Analysis Services, the Business Intelligence Development Studio development environment includes the following improvements to promote best practices and help developers design high-performing solutions |
|
|
Best practice alerts called AMO warnings have been added to the AMO object model. These warning messages surface in Business Intelligence Development Studio to alert developers when their designs are not consistent with any of over 40 best practices. These warnings are integrated into real-time designer checks through the use of blue squiggly alert lines, and they provide a nonintrusive (and dismissible) way for developers to detect potential problems with their design long before deployment. You can also use this new capability to review your existing SQL Server 2005 Analysis Services cubes for compliance with design best practices guidelines, either by opening your Analysis Services database in the SQL Server 2008 version of Business Intelligence Development Studio or by running the newest version of the SQL Server 2005 Best Practices Analyzer (which incorporates these same AMO warnings). You can also access these AMO warnings from PowerShell or a custom client application.
|
|
|
A new attribute relationship designer has been added for viewing and editing attribute relationships, with built-in validations to help in creating optimal dimension designs.
|
|
|
The dimension editor has been streamlined with a single dimension wizard, better detection and classification of dimension attributes and member properties, and the automatic detection of parent-child relationships.
|
|
|
A new aggregation designer has been added within Business Intelligence Development Studio with a new algorithm for creating new aggregations. The aggregation designer is optimized to work with usage driven aggregations. You can now easily look at your existing aggregations, add to those aggregations, or remove and replace them. Intelligent support is provided to help with merging existing and new aggregation designs. For more information about the new aggregation designer and using it with usage-driven aggregations, see Reintroducing Usage-Based Optimization in SQL Server 2008 Analysis Services.
|
|
|
|
| |
 |
| |
|
 |
|
At PharmaBI.COM, we realize that organizations need a powerful reporting & analysis solution that is easy to use and easily extended to sales & management. With Analyzer, our SaaS model allows for this flexibility. |
|
 |
|
Overview
