Where is Power Pivot for SharePoint?

/0 Comments/in /by

In the process of configuring Power Pivot for SharePoint, you need to run the Power Pivot Configuration Tool, which the documentation explains well here and here. What it doesn’t tell you is that unless you overwrite the URL in the Activate PowerPivot Feature in a Site Collection step, Power Pivot will be activated for the first site collection. I ran into a situation where the customer had installed My Sites and Portal site collections and the tool happily defaulted to activating Power Pivot for the My Sites site collection although we expected it to show up in the portal site.

Ideally, the tool should support checking multiple site collections but it doesn’t – you can specify only one. And if you don’t overwrite the URL, it will pick the first site collection it finds. If your SharePoint farm has other collections, you need to manually activate Power Pivot after the Power Pivot Configuration Tool is done. You can activate Power Pivot for additional site collections as follows:

  1. On a SharePoint site, click Site Actions. Click Site Settings.
  2. In Site Collection Administration, click Site Collection features.
  3. Scroll down the page until you find PowerPivot Integration Site Collection Feature, and then click Activate.

Atlanta MS BI Group Meeting on Jun 30th

/0 Comments/in /by

Join us for our next Atlanta MS BI Group meeting tomorrow, Monday, June 30th, to learn about implementing BI for financial reporting. You’ll also get a glimpse of the newly announced Microsoft Azure Machine Learning. Spread the word and invite your coworkers but RSVP on our website so we can plan food accordingly.

Main Presentation:Microsoft BI for Financial Reporting
 Level: Intermediate
Date:Monday, June 30th, 2014
Time6:30 – 8:30 PM ET
Place:South Terraces Building (Auditorium Room)

115 Perimeter Center Place

Atlanta, GA 30346

Overview:Using a Finance data mart in SQL Server 2012, I will demo how to use SSAS 2012 and Excel 2013 for Financial Reporting. This will include the basic DW model for Financial Reporting, how to configure the key dimensions in SSAS, how to setup writeback functionality in the Finance Cube with Security, and how to use Excel for the Reporting and writeback execution to update Budgets within the Cube.
Speaker:Justin Stephens began working with SQL Server 2000 (DW, DTS, and SSAS) in the fall of 2000 for a small software company in Texas. Early on, he became the SME for MDX Reporting using ASP and later ASP.NET. He was given a few projects using DTS/SSIS that expanded the reporting capabilities. In 2005, Justin spread my wings and began a long exodus from Texas that led him to Atlanta. Over the last 13 years, he has worked for/with some notable companies (Wal-Mart, McKesson, Dell, AMD, BCD Travel, Barclay’s) in various industries (Retail, Wholesale Distribution, Finance, Travel, and Education). During this time, he achieved MCITP in Business Intelligence using the 2008 R2 BI Stack. Justin is a die-hard proponent of the MSBI stack.
Sponsor:At TEKsystems, we understand people. Every year we deploy over 80,000 IT professionals at 6,000 client sites across North America, Europe and Asia. Our deep insights into IT human capital management enable us to help our clients achieve their business goals – while optimizing their IT workforce strategies. We provide IT staffing solutions, IT talent management expertise and IT services to help our clients plan, build and run their critical business initiatives.
Prototypes with PizzaMicrosoft Azure Machine Learning by Teo Lachev
With machine learning, computers can approach human performance in perception and understanding across vast amounts of data. Expensive and disconnected tools stood in the way of this innovation, but today Microsoft is democratizing machine learning. Get a glimpse of the Microsoft new cloud-based predictive analytics offering.

Microsoft Azure Machine Learning

/0 Comments/in /by

Yesterday, Microsoft announced Microsoft Azure Machine Learning, previously known as project Passau. Your humble correspondent has been participating in the Preview Program. Basically, Microsoft Azure Machine Learning is a service for self-service cloud-based predictive analytics. You upload your data to the cloud, define datasets and workflows to create “experiments”. Previously, you could create data mining models using the SQL Server data mining capabilities. Microsoft Azure Machine Learning to organizational DM models is what Power Pivot to Analysis Services is. Besides its cloud-based nature, Microsoft Azure Machine Learning offers:

  1. Data sources – Allow business users to upload source data as files.
  2. Workflows – Business users can drag and drop tasks to create workflows, such as to perform basic data transformation tasks, remove outliers, train, and score mining models. Users will familiar with SSIS will undoubtedly find workflows similar. Users familiar with SAS data mining will do the same.
  3. Scalability – You can use Big Data coming from Azure HDInsight and scale out accordingly.
  4. Endpoints – You can easily publish the predictive results as an ODATA service.
  5. Algorithms – There are more algorithms than the nine algorithms in SQL Server

061814_0133_MicrosoftAz1

Optimizing Arbitrary Shaped Sets

/0 Comments/in /by

I’m working on optimizing a fairly large cube (2.5 TB) with some 25 billion rows in the fact table. The customer was complaining about long-running queries. Most queries would specify a time range that was passed to the query filter. For example, if the user wants to see the aggregated data from May 25th 20:00 to June 2nd 19:00, the query WHERE clause would like this:

WHERE

(

{

[Date].[Date].[Date].&[20140527] * [Hour].[Hour].&[20] : [Hour].[Hour].&[23],

[Date].[Date].[Date].&[20140528]:[Date].[Date].[Date].&[20140601] * [Hour].[Hour].&[0] : [Hour].[Hour].&[23],

[Date].[Date].[Date].&[20140602] * [Hour].[Hour].&[0] : [Hour].[Hour].&[19]

}

… additional dimension filters here

)

This an example of using arbitrary-shaped sets which Mosha wrote about here and Thomas Kejser here.

“The resulting union set has interesting shape – it cannot be represented as pure crossjoin of attribute hierarchies. Such sets are nicknamed “arbitrary shaped sets” in Analysis Services, because they represent shapes of arbitrary complexity. Analysis Services in general doesn’t like arbitrary shaped sets, and there are often performance issues when using them, but if the business requirement calls for them – they can be used and will work correctly, although not most efficiently.”

Indeed, the server doesn’t like them and it would happily scan all partitions. In this case, the cube is partitioned by hour and data is kept for 40 days so there are 960 partitions. Although the above query spans 120 hours (partitions), the profiler would show that the server scans all 960 partitions, resulting in enormous amount of data being read. As it turns out, the WHERE clause is not optimized to project the filter on partitions. However, SUBSELECT filters are optimized because most reporting tools, such as Excel and SSRS, use them. The solution was simple: replace the WHERE clause with SUBSELECT to bring the query execution time from minutes to seconds:

FROM

(

SELECT

{

[Date].[Date].[Date].&[20140527] * [Hour].[Hour].&[20] : [Hour].[Hour].&[23],

[Date].[Date].[Date].&[20140528]:[Date].[Date].[Date].&[20140601] * [Hour].[Hour].&[0] : [Hour].[Hour].&[23],

[Date].[Date].[Date].&[20140602] * [Hour].[Hour].&[0] : [Hour].[Hour].&[19]

}

ON 0 FROM [<Cube>]

)

WHERE (… additional dimension filters here)

Partition Bug with Visual Studio

/0 Comments/in /by

There is a glaring bug with Multidimensional and Visual Studio 2012 and above. When attempting to add a new partition, the Partition Wizard finishes without error but no partition is added. Interestingly, no one has reported this bug more than two years after these Visual Studio editions have shipped. A couple of workarounds exist:

  1. Use SSDT or Visual Studio 2010 to create partitions. You can open your SSAS 2012 project in any Visual Studio edition starting with 2010.
  2. Add the partition either programmatically using AMO or by making changes directly to the *.partitions file.

On the subject of partitions, note that MS has marked the following features deprecated:

  • Remote partitions
  • Remote linked measure groups
  • Linked Dimensions
  • Dimension writeback

These features won’t be terribly missed. I haven’t seen remote partitions being used in real life except in BI certification exams. Linked measure groups also needs linked dimensions, but again, nobody in practice appears to use cross-server linked measure groups. These are cool features “on paper” which could and perhaps should have been popular, but they’ve never caught on enough for various reasons. If you do use them, now it’s time to provide feedback to MS. If you give them a good reason why you need a feature, they will probably reconsider.

Presenting at SharePoint Saturday Atlanta 2014

/0 Comments/in /by

I’ll be presenting “10 Ways to Maximize Your SharePoint BI Investment” at SharePoint Saturday Atlanta 2014 on June 21st.

“Join me to learn how to take the most out of your on-prem or cloud SharePoint investment to promote organizational and self-service BI. We’ll compare features of SharePoint editions and discuss options to visualize data from corporate databases and analytical models. Business users will learn how to share their self-service BI visualizations with teammates. We’ll discuss how Office 365 and Power BI can help you implement a cloud-based environment for ease of sharing, collaboration, and information access.”

I hope you can make it. You can register here.