I’ll be presenting “The Analysis Services 2012 Tabular Model” for the Atlanta BI Group on Monday, May 21st. And, Darren Herbold will share some great insights harvested from a real-life project of how to use SSIS to integrate with Salesforce.com. I hope you can make the event, which will be sponsored by Prologika.
“SQL Server 2012 introduces the BI Semantic Model that gives BI pros two paths for implementing analytical layers: Multidimensional and Tabular. The Tabular model builds upon the xVelocity engine that was introduced in PowerPivot. Although not as feature-rich as Multidimensional, Tabular promotes rapid professional development and great out-of-box performance. This session introduces you to Tabular development and shares lessons learned. I’ll also discuss how Tabular and Multidimensional compare.”
https://prologika.com/wp-content/uploads/2016/01/logo.png00Prologika - Teo Lachevhttps://prologika.com/wp-content/uploads/2016/01/logo.pngPrologika - Teo Lachev2012-05-19 12:34:582016-02-16 07:39:16Presenting at Atlanta BI Group
Performance tuning – my favorite! This blog originated from a quest to reduce the processing time of an SSAS cube which loads some 2.5 billion rows and includes DISCINTCT COUNT measure groups. The initial time to fully process the cube was about 50 minutes on a dedicated DELL PowerEdge R810 server, with 256 GB RAM and two physical processors (32 cores total). Both the SSAS and database servers were underutilizing the CPU resources with SSAS about 60-70 utilizations and the database server about 20-30 CPU utilization. What was the bottleneck?
By using the sys.dm_os_waiting_tasks DMV like the statement below (you can use also the SQL Server Activity Monitor), we saw a high number of CXPACKET wait types.
SELECT dm_ws.wait_duration_ms,
dm_ws.wait_type,
dm_es.status,
dm_t.TEXT,
dm_qp.query_plan,
dm_ws.session_ID,
dm_es.cpu_time,
dm_es.memory_usage,
dm_es.logical_reads,
dm_es.total_elapsed_time,
dm_es.program_name,
DB_NAME(dm_r.database_id) DatabaseName,
— Optional columns
dm_ws.blocking_session_id,
dm_r.wait_resource,
dm_es.login_name,
dm_r.command,
dm_r.last_wait_type
FROM sys.dm_os_waiting_tasks dm_ws
INNER JOIN sys.dm_exec_requests dm_r ON dm_ws.session_id = dm_r.session_id
INNER JOIN sys.dm_exec_sessions dm_es ON dm_es.session_id = dm_r.session_id
The typical advice given to address CXPACKET waits is to decrease the SQL parallelism by using the MAXDOP setting. This might help in some isolated scenarios, such as UPDATE or DELETE queries. However, the SQL Sentry Plan Explorer showed that each processing query is highly parallelized to utilize all cores. Notice in the screenshot below, that thread 16 fetches only 14,803 rows.
Therefore, the CXPACKET waits were simply caused by faster threads waiting for other threads to finish. In other words, CXPACKET wait is just a coordination mechanism between the threads being parallelized. To confirm this, we set the SQL Server MAXDOP setting to 1. Surely, the CXPACKET waits disappeared but the overall cube processing time went up as well. In our case, the biggest benefit was realized not by decreasing the SQL Server parallelism but by increasing it, by increasing the maximum number of database connections. This resulted in decreasing the overall processing time some 20%.
You need to be careful here though. While increasing the connections to max out the CPU on the SSAS server will yield the biggest gain, it might also slow down other processing, such as reports that query the cube while the database is being processed. So, as a rule of thumb, target no more than 80% CPU utilization to leave room for other tasks.
When moving to SQL Server 2012, ETL developers would need to rethink custom ETL frameworks. If you embrace the new project deployment model, SQL Server 2012 offers a lot of plumbing features out of the box. Jen Underwood wrote a great SQL Server 2012 ETL Framework Features in the New SSIS Catalog article about what supported and what’s missing. I guess in most cases developers will find that they’ll need to implement incremental features on top of the Microsoft provided toolset that are not supported, such as restartability and execution control, while letting SSIS 2012 handle logging and performance reporting. Another great resource for getting started with the new project deployment model is the SSIS Logging in Denali blog by Jammie Thompson.
We ran into an interesting snag when validating and executing packages using the new project deployment model with Windows integrated security:
Developer initiates the validation and execution in SSMS from his machine.
Packages are deployed to a dedicated SQL Server and packages use Windows integrated security to connect to a SQL Server database on a different server.
The source SQL Server database is on a different server.
As you’ve probably guessed it, this scenario requires configuring Kerberos delegation and registering SPNs for both the SQL Server hosting the SSIS packages and the SQL Server hosting the source database. That’s because validation and execution happen under your login and a double-hop is required to delegate your credentials to the source SQL Server. This can be avoided by using standard security (username and password) to establish connections from your packages to the source SQL Server. This wasn’t an option in our case because customer requirements dictated using Windows security to SQL Server.
I personally believe that the new Integration Services enhancements alone warrant upgrading to SQL Server 2012.
https://prologika.com/wp-content/uploads/2016/01/logo.png00Prologika - Teo Lachevhttps://prologika.com/wp-content/uploads/2016/01/logo.pngPrologika - Teo Lachev2012-04-29 21:06:002021-02-16 03:49:06SQL Server 2012 ETL Framework Features in the New SSIS Project Deployment Model
Scenario: Processing a 100 GB SSAS 2012 cube on a dedicated DELL PowerEdge server (128 GB RAM with 32 logical cores) reveals that 50% of the time is spent on processing the cube indexes and building aggregations with only 20% average CPU utilization and not even a small dent to the memory consumption. It was clear that SSAS doesn’t utilize the server resources and additional tuning is required to increase the parallelism of this phase.
Solution: Following Greg Galloway’s advice, we’ve changed the OLAP\Process\AggregationMemoryLimitMin to 2 and OLAP\Process\AggregationMemoryLimitMin to 3 to increase the parallelism of the index processing. This led to 25% reduction of the overall processing time and increased the CPU utilization to 70-80%. As to how we derived to these numbers, James Rogers has a great write-up. For the sake of preserving the precious formulas:
In case you’re curious about what these setting do, the amount of memory needed to build an aggregation is unknown up front. The engine has to estimate it based of certain factors (estimated rows, granularity attributes, measures, etc.) – and it uses the AggregationMemoryLimitMin value to ensure that if it’s estimate is wrong, that at least there will be a sufficient buffer for underestimation. Similarly, if the estimate is wrong in the upward direction, it will use the AggregationMemoryLimitMax value to trim the max value downward.
https://prologika.com/wp-content/uploads/2016/01/logo.png00Prologika - Teo Lachevhttps://prologika.com/wp-content/uploads/2016/01/logo.pngPrologika - Teo Lachev2012-04-15 13:26:552016-02-16 07:47:27Reducing Analysis Services Index Processing Time
Confused about the BI alphabetical soup? Not sure which one makes sense for your organization? Join this session to learn you can use the Microsoft BI platform to address various analytical needs. Discover how to implement the Personal-Team-Organizational continuum on a single platform. I’ll also discuss how Tabular and Multidimensional compare.
I hope you can make it. SQL Saturday is always a good show and this year we have great speakers and great BI content.
Issue: Previewing a Report Builder report connected to Analysis Services works in Report Designer but it fails in Report Builder 3.0 with the following error:
An existing connection was forcibly closed by the remote host
—————————-
Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host.
—————————-
Further, SQL Profiler reveals that an Anonymous connection is attempted to Analysis Services even though you use an embedded report data source (using a shared data source on the server will run the report on the server while with an embedded data source results the report runs on the client).
Resolution: Check if Report Builder is connected to a report server running in SharePoint mode by examining the status bar. If it is, click the Disconnect link. Report preview should now work.
Question: How do I find the source column name in PowerPivot if I’ve renamed and moved columns around?
Answer: Assuming that the table doesn’t use a custom query to import data or it wasn’t derived from an Excel linked table or Windows Clipboard, you can use the table properties to find the source column name as follows:
In the PowerPivot Window, click the table to select it.
In the Design ribbon tab, click Table Properties.
Make sure that the Column Names From radio button is set to Source.
Apparently, the name “VertiPaq” wasn’t catchy enough so Microsoft rebranded it as xVelocity (probably got tipped by Comcast). This is just a name change, no new features.
Glossary
xVelocity: The SQL Server family of technologies that utilize in-memory columnar storage to achieve very high-performance in query processing.
1. xVelocity for Business Intelligence Introduced as VertiPaq in the SQL Server 2008 R2 release, this in-memory columnar storage technology is the analytical engine that powered PowerPivot in that release, and is now in both PowerPivot and SQL Server Analysis Services in Tabular mode for the SQL Server 2012 release. xVelocity for BI consists of:
xVelocity In-memory Analytics Engine in SSAS 2012 Tabular Mode (used for enterprise-grade, scalable BI)
xVelocity In-memory Analytics Engine (previously called Vertipaq) in PowerPivot (used for self-service and team-oriented BI scenarios), available in PowerPivot for Excel and SharePoint Server
2. xVelocity for Data Warehousing Memory-optimized columnar (columnstore) index technology for use with SQL Server Data Warehouses. Data is stored in column-wise fashion that can be used to answer a query just like data in any other type of index. A columnstore index appears as an index on a table when examining catalog views or the Object Explorer in Management Studio. The query optimizer considers the columnstore index as a data source for accessing data just like it considers other indexes when creating a query plan.
Exceptions
In cases where the VertiPaq name appears in the product (VertiPaqpagingpolicy parameter, VertiPaq SE event, etc.), please continue to use VertiPaq, or xVelocity (VertiPaq) if necessary to avoid confusion.
Fellow MVPs have just released the latest public beta build of BIDS Helper, which should one of the first utilities you install after you install SQL Server on your machine. Besides fixes and updates, this release adds support for SQL Server 2012 and new features specific to Analysis Services Tabular.
This beta release is the first to support SQL Server 2012 (in addition to SQL Server 2005, 2008, and 2008 R2). Since it is marked as a beta release, we are looking for bug reports in the next few months as you use BIDS Helper on real projects. In addition to getting all existing BIDS Helperfunctionality working appropriately in SQL Server 2012 (SSDT), the following features are new.
To continue my Report Server Settings in SQL Server 2012 SharePoint Integration Mode blog, here is another example of how to use the new PowerShell-based configuration mechanism to hide a rendering extension. In this case, the script finds an SSRS application called SQL Server SSRS (replace with the name of your SSRS application from SharePoint Central Admin) and hides the XML renderer so the XML export option doesn’t appear in the Actions drop-down.
$apps = Get-SPRSServiceApplication | where {$_.name -like “SQL Server SSRS”}
