Blog – Page 67 – Prologika

Adding a Set Total

March 8, 2013 / No Comments »

Scenario: You have defined an MDX set in the cube script, such as a set that returns a few months. For the sake of simplicity, we will hardcode the months. CREATE SET CURRENTCUBE.[12Months] AS { [Date].[Month].&[201302].Lag(11):[Date].[Month].&[201302] }; As useful as the set might be, it has an issue. It doesn't return the total and users might complain about it: Solution: As a first try, you might attempt to add the [All] member to the set: CREATE SET CURRENTCUBE.[Last12Months] AS { [Date].[Month].&[201302].Lag(11):[Date].[Month].&[201302] + [Date].[Month].[All] }; You're on the right path. Now, you'll get the grand total row in Excel but it shows the unrestricted (non-visual total) total instead of the total across set members only. Instead, use the VisualTotals() function: CREATE DYNAMIC SET CURRENTCUBE.[12Months] AS { [Date].[Month].&[201302].Lag(11):[Date].[Month].&[201302] + VisualTotals ( { [Date].[Month].[All], [Date].[Month].&[201302].Lag(11):[Date].[Month].&[201302] } ) }; Here, the VisualTotals function returns the All member with the total adjusted to set...
Optimizing Massive SQL Joins

March 3, 2013 / No Comments »

Scenario: Run ETL to perform a full data warehouse load. One of the steps requires joining four biggish tables in a stating database with 1:M logical relationships. The tables have the following counts: VOUCHER: 1,802,743 VOUCHER_LINE: 2,183,469 DISTRIB_LINE: 2,658,726 VCHR_ACCTG_LINE: 10,242,414 Observations: On the development server, the SELECT query runs for hours. However, on the UAT server it finished within a few minutes. Both servers have the same data and hardware configuration, running SQL Server 2012 SP1. Solution: Isolating the issue and coming up with a solution wasn't easy. Once we ruled out resource bottlenecks (both servers have similar configuration and similar I/O throughput), we took a look at the estimated query plan (we couldn't compare with the actual execution plan because we couldn't wait for the query to finish on the slow server). We've notice that the query plans were very different between the two servers. Specifically, the estimated...
CU2 for SQL Server 2012 SP1

February 27, 2013 / No Comments »

Microsoft released a while back a cumulative update 2 for SQL Server 2012 SP1. Besides other things, it includes a fix for the "wide" MDX query performance degradation issue with Analysis Services that I previously reported. Knowledge base for the performance issue Knowledge base with SP1 CU2 download link
Nevron Chart for Reporting Services

February 18, 2013 / No Comments »

Nevron was kind enough to show me their chart and gauge for Reporting Services. In certain areas, their products exceed the charting capabilities of Reporting Services. Here are the highlights: The product provides support for SQL Server Reporting Services (SSRS) 2005, 2008, 2008R2 and SQL Server Data Tools (SSDT) 2012. Advanced Chart Designer Complete set of 2D and 3D Charting Types Support for Code Customization - Nevron Chart for Reporting Services supports customization through C# code, which allows you to use the full Nevron Chart for .NET API in SSRS and achieve a higher degree of customization. Expressions Everywhere - Nearly all properties of the Chart report item can now be specified by expressions. Appearance styles (fill, stroke, shadow and text) are also reworked to support both constant specification and specification that is based on expressions. This allows you to bind nearly all configurable aspects of the report item to...
SQL Server 2012 Semantic Search

February 11, 2013 / No Comments »

Suppose your company has a web application that allows customers to enter comments, such as to provide feedback about their product experience. How do you derive knowledge from these comments? Perhaps, customers are complaining a lot about a particular product. Or, they are raving about your service. Enter semantic search – a new feature in SQL Server 2012. Building upon full text search, semantic search allows you to search not only for words, but also for the meaning (semantics) of these works. Mark Tabladillo, Ph.D., gave us great presentation at our Atlanta BI January meeting. He demoed how semantic search can be used to find the most common phrases in a document and how to find similar documents from a given search criteria. Besides external documents, you can apply semantic search to text data already stored in a database, such as a text-based Comments field. Again, this is possible because...
Gartner’s 2013 Business Intelligence Magic Quadrant

February 8, 2013 / No Comments »

Gartner released the 2013 BI Magic quadrant report. And the winner is…
Gartner’s 2013 Data Warehouse Magic Quadrant

February 5, 2013 / No Comments »

MVP fellow, Andrew Burst, discusses Gartner's newly released DW (not BI, mind you) magic quadrant. It looks like Gartner fell in love with Teradata but Microsoft is moving up as well thanks to the advances in its Parallel Data Warehouse (PDW) and in-memory offerings.
Performance Degradation with the Aggregate Function and Unrelated Dimensions

February 5, 2013 / No Comments »

I ran into a severe performance issue with the Aggregate function and unrelated dimensions which I reported on connect. In this context, an unrelated dimension is a dimension that doesn't join any measure group in the cube. Why have unrelated dimensions? My preferred way to implement time calculations is use a regular dimension that is added to the cube but not related to any measure groups. Then, I use scope assignments to overwrite the time calculations, e.g. for YTD, MTD, etc. For example, the scope assignment for YTD might look like: Scope ( [Relative Date].[YTD] ); this = Aggregate ( {[Relative Date].[Current Period]} * PeriodsToDate([Date].[Fiscal].[Fiscal Year], [Date].[Fiscal].CurrentMember) ); End Scope; Notice the use of the Aggregate function which when executed maps to the default aggregation function of the underlying measure. For some reason with SQL Server 2012, a query that uses the Relative Date dimension experiences a significant performance hit....
PowerPivot Data Refresh in Excel 2013

February 4, 2013 / No Comments »

What options does a PowerPivot user have to refresh data in a PowerPivot model on the desktop, aka PowerPivot for Excel? Prior to Excel 2013, the answer was just one – manual refresh by either clicking the Refresh button in the PowerPivot window or the Refresh button in the Existing Connections dialog box. Note that the Refresh button in the Excel ribbon doesn't work since Excel doesn't know anything about PowerPivot. Not does the checking the "Refresh data when opening the file" checkbox in the PowerPivot connection. In Excel 2010, these options won't reopen the PowerPivot connections to the data sources. Instead, the net effect is that they will simply refresh the pivot reports from the pivot cache which is not what you're after. Starting with Excel 2013, however, Excel and PowerPivot play better together as I discussed in my What's New in Office 2013 BI blog. And, now we...
PerformancePoint 2013 Dependencies for Analysis Services

February 4, 2013 / No Comments »

Besides the usual hassle configuring PerformancePoint, including insufficient permissions to databases and service accounts, version 2013 requires the SQL Server 2008 R2 drivers. This is surprising considering that SharePoint 2013 shipped after SQL Server 2012. In a previous blog, I explained how to configure the SharePoint 2013 BI Center. When you go to any of the PerformancePoint-related links, such as Dashboards or PerformancePoint Content, you'll see the following ribbon. The Dashboard Designer button is the new way to launch the PerformancePoint Designer. However, when you attempt to configure a data source pointing to Analysis Services, you will likely get an error. The first stop for troubleshooting SharePoint and PerformancePoint issues is of course the Windows Event Log. If you examine the Windows Event Log, you'll see that PerformancePoint fails to load the 10.0 version of Microsoft.AnalysisServices.AdomdClient. This is the version that's included in SQL Server 2008 R2. This sends you...