Expanding upon the previous posts regarding SSAS 2005 functionality and performance testing with TD v12.0, a white paper I co-wrote on the topic has recently been made available up on Microsoft's Global ISV "Connector" site for Teradata (
http://www.microsoft.com/isv/teradata/default.aspx). Here's the link to download the thirty page paper:
http://download.microsoft.com/download/e/7/7/e77e1cc3-8922-4942-8f6b-1f35e2382a02/Functionality%20and%20Performance%20Testing%20SSAS%202005%20with%20Teradata%20v12.pdf. Enjoy!
I often get asked by organizations how they can leverage their SAP "data" investment against the Microsoft Business Intelligence suite of tools. The set of questions usually falls into one of these four categories:
- Ability to extract data directly from SAP R/3 modules for later reporting and analysis.
- Ability to extract data directly from SAP NetWeaver® BI (SAP BW) for later reporting and analysis.
- Ability to report directly off SAP NetWeaver® BI (SAP BW) data with Microsoft BI tools.
- Ability to leverage Excel directly against SAP NetWeaver® BI 7.0 without BEx.
Well, the stock answer usually goes something like this:
Microsoft Business Intelligence (BI) provides easy-to-use, certified access to SAP data to help you make better, more informed decisions. With Microsoft BI, you can use the familiar tools in Microsoft SQL Server 2005 and the 2007 Microsoft Office system environment to create, analyze, and share your own reports and BI dashboards from all sources.
Using Microsoft SQL Server and Office SharePoint Server 2007, teams across your organization can collaborate on information coming from both the SAP and Microsoft environments to ensure everyone is on the same page when it comes to analyzing results and making critical business decisions.
Finally, our strategic relationship with SAP has gone commercial with the launch of Duet, our jointly developed product that allows you to easily and quickly interact with your SAP and Microsoft Office environments. For more information on Duet, visit the Duet for Microsoft Office and SAP page (http://www.microsoft.com/isv/sap/technology/duet.aspx).
Ok...but what does that translate into in terms of architecture and implementation choices? Glad you asked. :-)
Here's the high level architecture on how Microsoft interacts with SAP data (at least as of today, July 8th 2008).
Implementation of data extracts from SAP R/3
SQL Server 2005 includes support for accessing SAP data by using the Microsoft .NET Data Provider for mySAP Business Suite (see this link for more details: http://msdn.microsoft.com/en-us/library/cc185434.aspx). This provider lets you create a package that can connect to a mySAP Business Suite solution and then execute commands on the server. You can also create Reporting Services reports against a SAP server. The Microsoft .NET Data Provider for mySAP Business Suite is tested on SAP R/3 versions 4.6C and higher. Earlier versions of SAP R/3 are not supported.
You can use the Microsoft .NET Data Provider for mySAP Business Suite in the SQL Server Import and Export Wizard, the Script task, the DataReader source, and the Script transformation that Integration Services provides, as well as the data processing extensions in Reporting Services.
You must provide a select query to specify data to be imported. The query must confirm to the semantics supported by the Data Provider for SAP. For more information about the grammar for a SELECT query for the Data Provider for SAP, see Syntax for a SELECT Statement.
Once data is extracted out of SAP R/3, the destination of that data could be any relational database (such as SQL Server), and then reports (from SSRS) and multi-dimensional cubes (from SSAS) can be created from that database data. The resulting SQL Server reports/cubes could then be used within the Microsoft Business Intelligence delivery mechanisms, such as through Excel, SharePoint, PerformancePoint, etc.
Implementation of data extracts from SAP NetWeaver® BI (SAP BW) using Open Hub and SQL Server 2005 Integration Services
The SAP Open Hub service enables you to distribute data from an SAP BI/BW system into external data marts, analytical applications, and other applications. With this, you can ensure controlled distribution using several systems. The central object for the export of data is the InfoSpoke. Using this, you can define the object from which the data comes and into which target it is transferred, leveraging SQL Server Integration Services (SSIS) as the transfer/transform mechanism.
SAP BI objects such as InfoCubes, ODS objects, or InfoObjects can act as open hub data sources. You can select database tables or flat files as open hub destinations, and then use those destinations as actual sources within SSIS. To automate the extraction using SAP’s Open Hub Service, you’ll need to setup a process chain. Note that both a full and delta modes are also available as the extraction mode.
As stated already with direct extraction from SAP R/3, once data is extracted out of SAP NetWeaver® BI (SAP BW), the destination of that data could be any relational database (such as SQL Server), and then reports (from SSRS) and multi-dimensional cubes (from SSAS) can be created from that database data. The resulting SQL Server reports/cubes could then be used within the Microsoft Business Intelligence delivery mechanisms, such as through Excel, SharePoint, PerformancePoint, etc.
Note that SQL Server 2008 Integration Services will include direct support for the NW BI 7.0 OHS - http://download.microsoft.com/download/1/7/5/175af735-edab-41db-b762-1b259ec5b362/Microsoft%20BI%20and%20SAP%20NetWeaver%20-%20SSIS.pdf.
Implementation for reporting against SAP NetWeaver® BI (SAP BW) data
Through a rich extensibility model, the report authoring and report deployment features of Microsoft® SQL Server™ Reporting Services 2005 can integrate with any number of business intelligence data sources. To answer the strong need for a rich reporting tool for SAP NetWeaver® Business Intelligence (SAP BW) and to bridge the gap between these two powerful business intelligence platforms, Microsoft has developed a new .NET Framework data provider and query designer for SAP NetWeaver® Business Intelligence.
With the release of SQL Server 2005 Service Pack 1 organizations can now take advantage of SQL Server’s Business Intelligence (BI) capabilities such as Reporting Services on SAP BW Data without purchasing independent and expensive reporting solutions. SQL Server 2005 Reporting Services is now certified to run reports on SAP BW. Customers that already own or plan to purchase SQL Server 2005 SP1, will get two new components that provide support for reporting on SAP BW in the SP1 release:
• The 'Microsoft .NET Data Provider 1.0 for SAP NetWeaver® Business Intelligence’
• A new query designer to enable the creation of SAP-compatible queries for SAP BW
The new Microsoft .NET Data Provider 1.0 for SAP NetWeaver® BI is a standards based provider that uses standard internet protocols and XML for Analysis (XML/A) (an XML standard for Online Analytical Processing - OLAP) to communicate with the SAP server. The .NET provider enables users to directly access QueryCubes as well as InfoCubes and MultiProviders. Since XML/A support is built into SAP BW, SAP BW reports can be authored and developed using SQL Server 2005 Reporting Services regardless of which relational database SAP BW data is stored in. To significantly reduce bandwidth requirements and improve network performance, the Microsoft .NET Data Provider 1.0 for SAP NetWeaver® BI uses GZIP compression when communicating with the SAP BW server, enabling organizations to efficiently work with large XML documents. Security is essential for data integrity; and the Microsoft .NET Data Provider 1.0 for SAP NetWeaver® BI supports and leverages internet standards such as SSL. This enables the system to safely transmit data between your SAP BW server and SQL Server 2005, providing added protection to your essential business information.
The new custom built Query Designer enables you to rapidly build reports for SAP BW. The query designer builds SAP-compatible MDX queries to base your reports on and provides insight into cubes, cube meta-data, calculated members, functions and variables.
By connecting SQL Server 2005 SP1 Reporting Services to SAP BW data, SAP BW users can quickly and easily take advantage of the flexible, yet easy-to-use reporting capabilities of SQL Reporting Services without migrating their data to another platform. This new integrated solution makes it easy to create and manage reports on information inside any SAP BW Data Warehouse, empowering organizations to easily get important SAP business intelligence information to the people who need it. SAP BW report authoring now becomes easy-to-do, consistent and familiar using SQL Server 2005 Reporting Services. Plus, deploying and distributing reports becomes a simple one-step process that targets the Web as the reporting platform – viewing reports becomes as simple as clicking a hyperlink.
In order to use the provider, the following components must be installed:
· Microsoft SQL Server 2005 Reporting Services Service Pack 1 or later
· Microsoft .NET Framework 2.0
The provider has been developed for and tested against SAP BW 3.5. However, the provider should also be compatible with BW 3.1 and BW 3.0B servers that have been patched to a sufficient service pack level, described below.
· Support Package 30 for SAP BW 3.0B
· Support Package 24 for SAP BW 3.1
· Support Package 16 for SAP BW 3.5
· Support Package 10 for SAP NetWeaver® 2004s (BW 7.0)
For more information on using Reporting Services directly against SAP BW, follow this link: http://download.microsoft.com/download/4/7/a/47a548b9-249e-484c-abd7-29f31282b04d/UsingRSwithSAPNetWeaver.doc.
Implementation for reporting against SAP NetWeaver® BI 7.0 Using Excel (without BEx)
Users familiar with SAP’s BW or NetWeaver BI often are interested in native Excel integration with SAP’s business intelligence data. SAP only offered BEx Analyzer in the past…however some casual users weren’t interested in learning this Excel Add-on tool. Now with SAP NetWeaver® BI 7.0, Excel 2007 users can tap into the full power of SAP BI data from within the comforts of the native Excel 2007 environment.
More potential for SAP NetWeaver® BI
· SAP customers can deploy SAP NetWeaver®BI beyond the realm of classical BEx users
· Microsoft customers can continue working with Excel to leverage data from SAP NetWeaver®BI Warehouses
SAP supports the standard
· Microsoft Excel is a long established standard for reporting
· The Pivot Table is a fast and intuitive way to analyze data
· Microsoft Excel 2007 provides enhanced reporting and formatting features like “top n” analysis
Very simple prerequisites, full support
· SAP NetWeaver®BI 7.0 natively and fully supports Microsoft Excel 2007
· The prerequisites are described in Note 1134226
· No BExAnalyzer or SAP NetWeaver®BI training necessary
Trusted integration
· Microsoft Excel has been supported in the past with some known some limitations in areas like hierarchy handling or filtering
· The new native Microsoft Excel 2007 integration provides access to SAP NetWeaver®BI data in a reliable and trusted way
To learn more about this native Excel 2007 interaction with SAP NW BI, go here: https://www.sdn.sap.com/irj/sdn/weblogs?blog=/pub/wlg/8483.
Through conversations with many customers, I’ve been asked either directly or indirectly what makes a dashboard good in terms of design. Put it another way…what are some design best practices to make a dashboard more effective at conveying the “data” message.
Well…here’s the list of dashboard design best practices that I try to follow. How many do you follow already?
1. Keep the dashboard to a single web page that viewable in a single screen. If additional detail is needed, have that detail (or related data) be navigation to different pages on the dashboard.
2. Make sure there is context to all data elements presented on the page…meaning if presenting a number (let’s say sales revenue by quarter to quarter comparison), is the latest number good or poor.
3. Keep “like” data grouped (arranged) within the dashboard.
4. Keep precision and detail of data to a minimum. For example, to conserve space on a page, instead of showing $3,000,000 show $3m. Think of “detail data” in the same light…does it really make sense to show all that detail if it is better served on a subsequent dashboard page.
5. Don’t use pie charts or radar charts on a dashboard.
6. Use a uniform color scheme and object rendering as appropriate –put in other words, don’t just use a cool image/representation of data “just because”.
7. Don’t use bright colors in charts/graphs…use either light colors or even grayscale. Use the bright colors for highlighting important information, like yellow or red KPI status.
8. Keep most important data (aka data your want the user to see first) in the upper left, least important data in lower right. Also keep in mind relative size to one another…for example; a large colorful graph in the lower right may overpower the important data in the upper left.
9. Don’t over complicate the dashboard page(s) with design elements…aka, fancy gauges, images, etc. Not only do these designs waste space, but they draw the user’s attention away from the important data.
10. Reduce non data pixels as much as possible…meaning do away with borders, gradient fill colors, grid lines, extra images, etc. The simpler the better.
11. Use “sparkline” and “bullet” graphs to represent data. Both can be achieved within Reporting Services 2005/2008 (as seen below).
Here at the NY Microsoft Technology Center (NY MTC), we recently ran a series of SQL Server Upgrade/Application Compatibility Labs with various customers and ISVs.
To learn more about the labs, watch this video blog entry shot during one of the actual labs: http://blogs.msdn.com/mtcny/pages/sql-server-2008-upgrade-lab-april-2008.aspx.
The two tools used in these labs were SQL Server Upgrade Advisor and SQL Server Upgrade Assistant.
SQL Server Upgrade Advisor helps you prepare for upgrades to SQL Server 2008. Upgrade Advisor analyzes installed components from earlier versions of SQL Server, and then generates a report that identifies issues to fix either before or after you upgrade. Additional information on the Upgrade Advisor can be found here: http://msdn.microsoft.com/en-us/library/ms144256(SQL.100).aspx.
SQL Server Upgrade Assistant (SSUA) is a tool made available as a free download from Scalability Experts. The tool can be downloaded at the following link: http://www.scalabilityexperts.com/default.asp?action=article&ID=43 . SSUA uses traces to allow an IT professional to evaluate the behavior of an application on different versions, service pack levels, editions, or configurations of SQL Server. The tool can even be used to test the behavior of different version of an application release when a hotfix or service pack is installed on SQL Server. SQL Server Upgrade Assistant currently supports upgrading SQL Server 7.0 and SQL Server 2000 to SQL Server 2005, as well as supports SQL Server 2000 and SQL Server 2005 to SQL Server 2008.
The following techniques can help you to optimize the data files and temporary files used during processing:
· Place the Analysis Services data files on a fast disk subsystem.
The location of the data files is determined by the DataDir server property. To optimize disk access for querying and processing, place the Analysis Services Data folder on a dedicated disk subsystem (RAID 5, RAID 1+0, or RAID 0+1).
· If temporary files are used during processing, optimize temporary file disk I/O.
The default location of the temporary files created during aggregation processing is controlled by the TempDir property. If a temporary file is used, you can increase processing performance by placing this temporary folder on a fast disk subsystem (such as RAID 0 or RAID 1+0) that is separate from the data disk.
Are you a DBA tasked with keeping a SQL Server 2005 based data warehouse/datamart performing properly? Are you looking for a no-cost method to analyze performance metrics of your server(s)? If so, the SQL Server 2005 Performance Dashboard Reports are for you!
The SQL Server 2005 Performance Dashboard Reports are Reporting Services report files designed to be used with the Custom Reports feature introduced in the SP2 release of SQL Server Management Studio. The reports allow a database administrator to quickly identify whether there is a current bottleneck on their system, and if a bottleneck is present, capture additional diagnostic data that may be necessary to resolve the problem. For example, if the system is experiencing waits for disk IO the dashboard allows the user to quickly see which sessions are performing the most IO, what query is running on each session and the query plan for each statement.
Common performance problems that the dashboard reports may help to resolve include:
- CPU bottlenecks (and what queries are consuming the most CPU)
- IO bottlenecks (and what queries are performing the most IO).
- Index recommendations generated by the query optimizer (missing indexes)
- Blocking
- Latch contention
The information captured in the reports is retrieved from SQL Server's dynamic management views. There is no additional tracing or data capture required, which means the information is always available and this is a very inexpensive means of monitoring your server.
Reporting Services is not required to be installed to use the Performance Dashboard Reports.
To download the performance reports, you can find the install files here: http://www.microsoft.com/downloads/info.aspx?na=90&p=&SrcDisplayLang=en&SrcCategoryId=&SrcFamilyId=1d3a4a0d-7e0c-4730-8204-e419218c1efc&u=http%3a%2f%2fdownload.microsoft.com%2fdownload%2f0%2f6%2fe%2f06ebef2e-2cc0-4b7a-a81a-deb4eec607d7%2fSQLServer2005_PerformanceDashboard.msi
With Integration Services, Reporting Services, and Analysis Services, their respective business intelligence objects (packages, reports, and cubes) are designed through the Business Intelligence Design Studio (BIDS), which is really the Visual Studio shell incorporated with special SQL Server 2005 business intelligence project templates installed. Using BIDS, or Visual Studio 2005 with the BI templates installed, allows the developer to deploy the projects directly to a server environment. This works fine for a development environment where the developer most likely has access and permissions to deploy objects to the server, but what about Q/A, Beta (Test), and Production environments? Most likely, organizations do not want their developers impacting installed objects on their test and production environments…for obvious reasons. Therefore, a change management/object promotion process must be utilized against the business intelligence environment, allowing non-developer resources to quickly and easily deploy new object types.
Also of consideration is object versioning. When working with Visual Studio, there are multiple product options that allow versioning and check-in-check-out of project objects. From Microsoft, two products exists that enable versioning/checking object code, those being Visual Source Safe, and Team Foundation Server.
The process for deployment "object promotion" to non personal-development environments is accomplished through three different options:
1) Use BIDS/Visual Studio 2005 to open the project/solution, and then deploy the data sources and reports to the appropriate server. The project object allows for multiple configuration settings, and is an exposed set of properties that can identify development, QA, test, and product environment settings.
2) Use a scripting engine (for Reporting Services that scripting engine is RS.EXE, for Analysis Services it’s ASCMD, and for Integration Services it's DTUTIL) to develop a script that deploys, installs, and secures objects to a particular target server. (Note – There are several sample scripts that come with SQL Server 2005 for reference).
3) Use application interfaces to create a custom deployment tool. For example, the Reporting Services management web service endpoint (http://<Server Name>/ReportServer/ReportService2005.asmx?wsdl) could be used to accomplish the same deploy, install, and securing of Reporting Services objects that the RS.EXE scripting command implements.
Once you have identified potential Analysis Services bottlenecks, and have made the proper adjustments to Analysis Services server properties and perhaps added more memory/CPU power to the server, the next phase is to look into tuning the OLAP cube design and the related MDX queries used against the cube.
The sections outlined below will give you best practice insight (that I've used during customer proof of concepts) for fine-tuning your cube and query design, thus making your OLAP cube as efficient as possible for queries being sent to it for results.
Cube Design Best Practices - Dimensions
· Consolidate multiple hierarchies into single dimension (unless they are related via fact table)
· Avoid ROLAP storage mode
· Use role playing dimensions (e.g. OrderDate, BillDate, ShipDate) - avoids multiple physical copies
· Use parent-child dimensions prudently
o No aggregation support
· Set Materialized = true on reference dimensions
· Use many-to-many dimensions prudently
o Slower than regular dimensions, but faster than calculations
o Intermediate measure group must be “small” relative to primary measure group
Cube Design Best Practices – Attributes/Hierarchies
· Define all possible attribute relationships!
· Remove redundant attribute relationships
· Mark attribute relationships as rigid where appropriate
· Use integer (or numeric) key columns
· Set AttributeHierarchyEnabled to false for attributes not used for navigation (e.g. Phone#, Address)
· Set AttributeHierarchyOptimizedState to NotOptimized for infrequently used attributes
· Set AttributeHierarchyOrdered to false if the order of members returned by queries is not important
· Use natural hierarchies where possible
Cube Design Best Practices – Measures
· Use smallest numeric data type possible
· Use semi-additive aggregate functions instead of MDX calculations to achieve same behavior
· Put distinct count measures into separate measure group (BIDS does this automatically)
· Avoid string source column for distinct count measures
Cube Design Best Practices – OLAP Partitions
· No more than 20M rows per partition
· Specify partition slice
o Optional for MOLAP – server auto-detects the slice and validates against user specified slice (if any)
o Must be specified for ROLAP
· Manage storage settings by usage patterns
o Frequently queried à MOLAP with lots of aggregations
o Periodically queried à MOLAP with less or no aggregations
o Historical à ROLAP with no aggregations
· Alternate disk drive - use multiple controllers to avoid I/O contention
· Remote partitions for scale out – VLDB
Cube Design Best Practices – Aggregations
· Define all possible attribute relationships
· Set accurate attribute member counts and fact table counts
· Set AggregationUsage to guide aggregation designer
o Set rarely queried attributes to None
o Set commonly queried attributes to Unrestricted
· Do not build too many aggregations
o In the 100s, not 1000s
· Do not build aggregations larger than 30% of fact table size (agg design algorithm doesn’t)
MDX Query Design Best Practices
· Use calculated members instead of calc cells where possible
· Use .MemberValue for calculations on numeric attributes
o Filter(Customer.members, Salary.MemberValue > 100000)
· Avoid using CalculationPassValue
o Rely on auto recursion resolution using scopes and assignments
· Avoid redundant use of .CurrentMember and .Value
o (Time.CurrentMember.PrevMember, Measures.CurrentMember ).Value can be replaced with Time.PrevMember
· Avoid LinkMember, StrToSet, StrToMember, StrToValue
· Replace simple calculations with computed columns in DSV
o Calculation done at processing time is always better
· Many more at:
o http://sqljunkies.com/weblog/mosha
o http://sqlserveranalysisservices.com
Ok...here's part two of the Teradata v12 series.
The first blog entry was focused on highlighting a sequence of functionality tests that were recently concluded back in February 2008 against Teradata version 12 using SQL Server 2005's Analysis Services (and the latest beta v12 .NET Provider from Teradata.)
This follow-up blog entry will focus on the benchmarking result efforts (up to 1000 simulated users) of queries against Analysis Services. What was recorded was average response time per SSAS query using a Teradata backend source, comparing response times when Teradata v2R6.2.1.3 is used and v12 is used (both using the v12 .NET Provider).
Background
The testing focused on optimizing the Project REAL Toolkit data warehouse with a goal of identifying the optimal concurrent workload for sub-second response time and indentifying best practices for a ROLAP environment.
Database preparation was the first step, and scope of that effort was as follows:
- Migration of the Project REAL sample data kit to a Teradata system provided by Teradata.
- Identify an appropriate database schema for Project REAL analytics based on the POC objectives.
- Redesign the Project REAL solution into a Snow-Flake schema to more effectively take advantages of Teradata optimization capabilities.
- Grow the data to 100GB.
- Tune the Teradata system for the user load.
For conducting the testing effort, we focused on the following:
- Functionality testing of Analysis Services running against Teradata v12 .NET Provider and v12 Database Engine (aka, the first blog entry in this series).
- Test script preparation leveraging Visual Studio Test Suite for Testers.
- Benchmark and optimize the Project REAL solution for user load against TeraData v2R6.2.1.3 using the new Teradata v12 .NET Provider.
- Database migration from Teradata v2R6.2.1.3 to Teradata v12
- Benchmark and optimize the Project REAL solution for user load against TeraData v12 using the new Teradata v12 .NET Provider.
Test Results
The successful execution of the engagement resulted in the following benchmark results (which was based on SQL Server 2005 Analysis Services SP2, Update 3175 applied):
- Ran various user loads up to 1000 users with response times less than 1.2 second for tests against the Teradata v2R6.2.1.3 engine using the v12 .NET Provider.
- Ran various user loads up to 1000 users with response times less than 0.93 second for tests against the Teradata v12 engine using the v12 .NET Provider.
Test Environment
The hardware configuration used for the testing was as follows:
Analysis Services System:
- DELL two socket, quad-core system (eight total processor cores) with 1.86 GHz Intel Xeon and 4GB RAM
- 1 TB drive - The Analysis Services System does not require disk storage space for a ROLAP storage deployment. In a Teradata scenario, a best practice is to store the dimensions as MOLAP as a result an insignificant amount of disk space was used.
- Windows 2003 Server X64
- SQL Server 2005 x64 SP2 (patch level 3175)
Note that besides Analysis Services, SQL Server's RDBMS and the Visual Studio Team Suite Testing tool also ran on this server. On average, only 25% of server resources were available to SQL Server Analysis Services for the tests - which certainly reduced the overall performance of Analysis Services capabilities. However, this benchmarking test was a comparison of TD v2R6.2.1.3 to TD v12...so it didn't matter that resources were consumed by other services...since the benchmarking tests were resource and query "apples to apples" similar.
Teradata System:
4-Node Teradata 5500 system running database v2R6.2.1.3, which was also then upgraded to v12.
- Disks: RAID1 with 96 x 73GB disks
Each node has:
- 12 AMPs and 2 PE's
- 4 CPU's @ 2.66Ghz
This blog will be a two part series…culminating from my recent internal testing of Analysis Services with Teradata v12.
The first blog entry in this series will be focused on highlighting a sequence of functionality tests that were recently concluded against Teradata version 12 using SQL Server 2005’s Analysis Services (and the latest beta v12 .NET Provider from Teradata.)
The second entry in this series will focus on the benchmarking result efforts (up to 1000 simulated users) of queries against Analysis Services. What was recorded was average response time per SSAS query using a Teradata backend source, comparing response times when Teradata v2R6.2.1.3 is used and v12 is used.
The successfully completed series of functionality tests used an Analysis Services 2005 “Post-SP2” (build 3175) instance connected to Teradata v12 for functionality testing. Using this Post-SP2 build is critical, since this SSAS SP2 doesn’t contain Teradata support. You can find information for this Post-SP2 build here: http://support.microsoft.com/kb/936305/.
The functionality tests included the following areas:
· Ability to create an Analysis Services Data Source View (DSV) against Teradata v12 using the .NET Provider for Teradata v12.
· Ability to process Analysis Services dimensions and cubes which were sourced from Teradata v12 using the .NET Provider for Teradata v12.
· Ability to query Analysis Services ROLAP cubes that are created against Teradata v12 using the .NET Provider for Teradata v12.
· Ability to execute reports from Reporting Services 2005 which have their data source defined against Analysis Services cubes that have Teradata v12 data using the .NET Provider for Teradata v12.
· Ability to execute reports from Reporting Services Report Builder 2005, whereby the Report Model was generated from an Analysis Services 2005 ROLAP cube that has its data source connected to a Teradata v12 database using the .NET Provider for Teradata v12.
I’ll be posting benchmark results during mid-March for those that are interested in seeing comparative Analysis Services query response time numbers between Teradata v2R6.2.1.3 versus Teradata v12 backend data sources.
Well...this is certainly not a normal post. This is a snippet of code for a SQL Server 2005 lab that I'm assisting with...if your in the lab, copy/paste. It will help with the lab "typing".
CREATE FUNCTION dbo.fn_ClassifyApps() RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
DECLARE @ret sysname
IF (APP_NAME() LIKE '%Low Importance Application%')
SET @ret='Low Importance Group'
RETURN @ret
END
GO
ALTER RESOURCE GOVERNOR
WITH (CLASSIFIER_FUNCTION = dbo.fn_ClassifyApps)
ALTER RESOURCE GOVERNOR RECONFIGURE
I've seen this question come up on many a proof...and that is how do you generate Report Models off an Analysis Services UDM (OLAP cube) once you've put SQL Server 2005 Reporting Services into Microsoft Office SharePoint Server (MOSS) Integration Mode. Well, when not in MOSS integration mode, it was rather simple...just deploy the data source to Analysis Services and then either use Report Manager or SQL Server Management Studio to generate the "model" off the data source. So now you’re staring at the MOSS UI, but don't see a way of generating the UDM based model...well, you’re probably missing some MOSS configuration to enable report model generation. The configuration you need is to enable multiple content types for the library your working with (for example the data connections library for the "Reports" subsite in MOSS), and then add report server types to that library. I normally will go ahead and add the "Report Builder Report", "Report Model" and "Report Data Source" content types to the library. For full details on how to configure these content types in MOSS, refer to this BOL section from SQL Server 2005.
Once you've added these content types to the library...actually creating the Report Model from the UDM is rather easy, and it’s just two steps: 1) Create the Report Data Source (if not already deployed to the MOSS site), and 2) Create the Report Model. With the new content types added to your MOSS library, both of these steps are as easy as clicking on the "New" item from the MOSS menu, and picking the correct item (either data source or model). Obviously you'll want to generate the UDM model based from the UDM data source you created in step 1. For full instructions on creating the Report Model from within MOSS, review this BOL section from SQL Server 2005.
Ok...everyone should know by now that SQL Server 2005's Report Builder doesn't support parent child relationships within a report model (think about the classic employee to supervisor hierarchy...or for another example, how a company may roll up into a parent company/entity). So...how do you overcome this limitation of the SQL Server 2005's Report Builder/Model? Well, unfortunately there isn't a simple answer or solution...but there is a partial workaround. The workaround leverages SQL Server 2005's Common Table Expressions (CTE) and a compromise by the business users.
First...lets tackle the compromise by the business users. Since a parent-child hierarchy can be infinitely long...you'll need to pin down from the business users the anticipated largest depth of the hierarchy. For example, does the employee to manager hierarchy have three levels, or four levels, or five levels deep? Once you determine the longest "known" depth of the hierarchy, you'll use this knowledge to create a recursive query within the database to produce known levels for traversal.
The next step is to create a recursive query within the SQL Server 2005 database. If you need to learn about the recursive query CTE, then read this SS2K5 BOL entry, or check out this great blog entry on the topic. The CTE based recursive query should give you a result set something like "parent, child, level". Once you have the recursive query in place, you'll then want to construct a view from the CTE, whereby you'll leverage the view to join the identifier parent and child pairs to the decode table to return identifier names (assuming your model is normalized in this manner) down to the number of levels deep as compromised by the business users.
Once the recursive query is defined, you'll most likely then create a named query with Report Builder's "Data Source View" (DSV). This named query can be simple, or rather complex depending upon your database model and the number of custom Report Builder "click through" reports you want developed. Meaning, if you want a custom click through report for level 1 versus a different custom click through report for level 2, you'll need to create two separate named queries within the DSV, since an entity can only have one custom report per scalar or aggregate value. (For more information on creating custom click through reports for Report Builder, view this SS2K5 BOL entry).
Ok...that's it. You should now be able to traverse a parent-child relationship successfully within Report Builder...albeit with a compromise from the business end-users.
As with any dashboard implementation, an intuitive user interface design is desired, thus leveraging the latest concepts in dashboard user interfaces. One of those concepts is to display Sparkline type charts. A “Sparkline” is a word coined by Edward Tufte to describe “small, high-resolution graphics embedded in a context of words, numbers, and images.” Sparklines are “word-sized graphics” which can be used to add context to data in a sentence. More than that, though, Sparklines are word like graphics that can be used almost as words.
To implement Sparklines within Reporting Services is rather easy…in fact, the charting object within Reporting Services can be shrunk within a table object, thus producing the Sparkline image.
Do note though that placing many inline Reporting Services charts within a table will put more processing burden on the Reporting Services server, since each chart needs to be determined and rendered.
Other than leveraging the chart object within Reporting Services, there are a few third party solutions that “plug into” the Microsoft BI Platform to allow Sparkline and other visualization techniques. One such offering is from BonaVista Systems – through a product called MicroCharts. For more information visit this link: http://www.bonavistasystems.com/Products_SparkLinerforMicrosoftBI.html.
Hi There!
Glad you made your way to my blog...what you'll get here is some hands-on insight into the Microsoft Business Intelligence platform based upon my dealings with our toolsets used within client engagements as well as my own tests.
I'll be covering areas of the BI platform that include: SQL Server (RDBMS, Integration Services, Analysis Services, Reporting Services), Performance Point (mostly the M&A portion), Excel, and Sharepoint.
