Finding Source Column Names in PowerPivot

/0 Comments/in /by

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:

  1. In the PowerPivot Window, click the table to select it.
  2. In the Design ribbon tab, click Table Properties.
  3. Make sure that the Column Names From radio button is set to Source.

032812_1329_FindSourceC1

VertiPaq Rebranded as xVelocity

/0 Comments/in /by

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.

BIDS Helper 1.6 Beta Released

/0 Comments/in /by

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.

  • Analysis Services Tabular
  • Smart Diff
  • Tabular Actions Editor
  • Tabular HideMemberIf
  • Tabular Pre-Build

Hiding SQL Server 2012 Reporting Services Extensions in SharePoint

/0 Comments/in /by

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”}

Set-SPRSExtension -identity $apps -ExtensionType “Render” -name “XML” -ExtensionAttributes “<Visible>False</Visible>”

Can’t Activate Reporting Services Service in SharePoint

/0 Comments/in /by

I upgraded SQL Server 2012 from RC0 to RTM today on a SharePoint application server which was configured for Reporting Services integration. The upgrade went with no errors but I got this error when requesting reports:

  • The requested service, ‘http://amatltapp02:32843/1dacf49a2f7a4a6daa8db5768539893f/ReportingWebService.svc’ could not be activated. See the server’s diagnostic trace logs for more information.
    • The requested service, ‘http://amatltapp02:32843/1dacf49a2f7a4a6daa8db5768539893f/ReportingWebService.svc’ could not be activated. See the server’s diagnostic trace logs for more information.

    And, when browsing directly the service:

    After some digging out, we solved the issue by changing the Enable 32-Bit Applications property of the IIS application pool for the SharePoint web application to False.

    031512_1512_CantActivat1

Server Error in ‘/1dacf49a2f7a4a6daa8db5768539893f’ Application.

The farm is unavailable.

Presenting at SQL Server Special Ops Tour Atlanta

/0 Comments/in /by

I’ve been honored to be selected by Microsoft to present the Self-service BI using PowerPivot and Power View session for the SQL Server Special Ops Tour in Atlanta on Monday, March 5th.

EVENT AGENDA
AgendaSpeaker
12:30pm – 1:30pmRegistration Opens
1:30pm – 2:30pmSQL Server 2012 – The New World of Data
Dandy Weyn – FTE – Sr. Tech Prod Mgr
2:30pm – 3:15pm  Introduction to SQL Server 2012 AlwaysON – Availability GroupsGeoff Hiten – MVP – Community
3:15pm – 3:30pm
Break – drinks and light snacks
3:30pm – 4:15pmSelf-Service BI using PowerPivot and Power View
Teo Lachev – MVP – Community
4:15pm – 4:45pmUnderstanding Microsoft Training & Certifications for Microsoft SQL Server 2012Bob Taylor – FTE – Principal PFE
4:45pm – 5:15pmIn Summary
All speakers

Join us to celebrate SQL Server 2012!

Finding Reporting Services Service in SharePoint

/0 Comments/in /by

SharePoint is a like an ocean – the further you go, the deeper it gets

Ancient proverb 

Scenario: Configure SQL Server 2012 Reporting Services on a multi-server SharePoint farm with the following topology:

WEB1 – Web front-end Server 1

WEB2 – Web front-end Server 2

APP1 – Application server 1 that hosts the Central Administration site

APP2 – Needs SSRS 2012

APP2 – Needs SSRS 2012

Issue: I couldn’t find the SQL Server Reporting Services Service Application option when I expanded the New button in Central Administration ð Manage Service Applications

Resolution: Although the SQL Server 2012 Reporting Services service and Reporting Services Add-in were installed on APP2 and APP3, the SQL Server Reporting Services service wasn’t registered at a farm level. As a result, I couldn’t find the SQL Server Reporting Services Service in Central Admin -> Manage Services on Server on none of the application servers. As it turned out, the SSRS service must be also registered on the application server hosting Central Administration (APP1 in my case) as Prash Shirolkar explains in his blog:

  1. Remote in to APP1.
  2. Install the Reporting Services add-in to get the SSRS PowerShell cmdlets. In my case, I installed only the add-in and not the Reporting Services service since I didn’t want it on APP1.
  3. Run the SharePoint 2010 PowerShell as admin and execute the three commands in the Install and Start the Reporting Services SharePoint Service in the Install Reporting Services SharePoint Mode as a Single Server Farm document:
    1. Install-SPRSService
    2. Install-SPRSServiceProxy
    3. get-spserviceinstance -all |where {$_.TypeName -like “SQL Server Reporting*”} | Start-SPServiceInstance

Interestingly, the first two commands don’t echo any output. The third command, however, should show you a message that the SSRS service has been provisioned. Now, you can go to Central Administration ð Manage Services on Server and you should be able to find the SQL Server Reporting Services Service. More importantly, you should be able to go to Central Administration ð Manage Service Applications, expand the New button and then click SQL Server Reporting Services Service Application to finalize the Reporting Services setup.

Possible alternative resolution path

Although I haven’t tried it because the client woudn’t allow farm configuration changes, another potential resolution path could be to temporarily switch the application server that hosts the Central Administration utility to one of the SSRS application server (APP2 or APP3) as explained in the blog post, “How to change Central Admin Host in SharePoint 2010” by Kirk Barrett. Then, you can register the SSSRS service on that server by executing the steps in item 3 above. Finally, switch back the Central Administration host server to the original server (APP1).

Presenting Personal BI with PowerPivot v2

/0 Comments/in /by

022312_1752_PresentingP1I’ll be presenting “Personal BI with PowerPivot v2” for the Atlanta BI Group on Monday, February 27th. Join us to learn when personal BI makes sense and why PowerPivot is the best tool on the market when it does. The focus will be on the new features of PowerPivot version 2 which launch officially in a week or so.

For more information and to register visit our Atlanta BI home page.

 

Announcing My New Book

/0 Comments/in /by

021112_1455_AnnouncingM1My new book, Applied Microsoft SQL Server 2012 Analysis Services (Tabular Modeling), will start shipping in a week with all popular resellers, such as Amazon, B&N, etc. I’ve been working on it for a few months and I’m excited to have it done. While waiting to buy the book, check the following resources to get you started with PowerPivot and Tabular:

– A sample chapter – “Introducing Business Intelligence Semantic Model”

– Video demos – I recorded over three hours of video content for selected exercises in the book

An insightful tour that provides an authoritative yet independent view of this exciting technology, this guide introduces the Tabular side of the innovative Business Intelligence Semantic Model (BISM) that promotes rapid professional and self-service BI application development. Business analysts and power users will learn how to integrate data from multiple data sources, implement self-service BI applications with Excel, and deploy them to SharePoint. Business intelligence professionals and database administrators will discover how to build corporate solutions powered by BISM Tabular, delivering supreme performance with large data volumes, and how to implement a wide range of solutions for the entire spectrum of personal-team-organizational BI needs.

WHAT’S INSIDE:
Chapter 1: Introducing Business Intelligence Semantic Model

PART I: PERSONAL BI WITH POWERPIVOT FOR EXCEL
Chapter 2: Personal BI Basics
Chapter 3: Importing Data
Chapter 4: Refining the Model
Chapter 5: Analyzing Data
Chapter 6: Implementing Calculations

PART II: TEAM BI WITH POWERPIVOT FOR SHAREPOINT
Chapter 7: Team BI Basics
Chapter 8: SharePoint Insights
Chapter 9: Managing PowerPivot for SharePoint

PART III: ORGANIZATIONAL BI WITH ANALYSIS SERVICES TABULAR
Chapter 10: Organizational BI Basics
Chapter 11: Designing Storage and Security
Chapter 12: Managing Tabular Models

Enoy!

ProcessUpdate and Partition Scans

/0 Comments/in /by

I’m working on minimizing the cube processing for a cube with some two billion rows in a fact table. I put together an ETL package that processes the dimensions with ProcessUpdate following by processing the latest partitions. I’ve noticed that processing one of the dimensions , which happens to be the largest (some 1 million rows) and most complicated dimension, resulted in partitions scans. The SQL Profiler showed the scans with the following events:

Finished processing the ‘<partition name>‘ partition

These partition scans don’t result in SQL queries to the database and normally should execute pretty fast. In this case, however, the scans were taking plenty of time resulting in some 15 minutes delay in the incremental processing flow. With some help from T.K. Anand, it turned out that I had a design issue with that dimension. I discovered the issue by changing the KeyDuplicate error in the dimension ErrorConfiguration to True and fully processing the dimension. Dimension processing failed because of the attribute relationships implied a one-to-many relationship between two attributes but the data didn’t support it. Somewhere along the design cycle, I turned off KeyDuplicate probably to get around the same issue in order to process the dimension successfully.

So, the moral of this story is:

  1. Always check your dimensions with the Dimension Health Check feature of the BIDS Helper.
  2. Don’t turn off KeyDuplicate.

BTW, the problem with duplicate key errors is that the server can move members around and that would cause indexes and aggregations to require being rebuilt. For example, if you have the following members in the source data:

Attr1Key    Attr2Key

55        32

55        35

The first time, the engine may store 32 as the related member. But the second time it sees the rows during ProcessUpdate, it would potentially choose 35 as the related member. And that’s going to cause indexes and aggregations to need rebuilding.