Mark Tabladillo delivered a great presentation about using Excel for data mining at our last Atlanta BI SIG meeting. The lack of an Excel 2010 x64 DM add-in came up. There was a question from the audience whether Microsoft has abandoned DM technology since it stopped enhancing it. A concern was also raised that Microsoft might have also neglected the corporate BI vision in favor of self-service BI.
That’s definitely not the case. I managed to get a clarification from the Microsoft Analysis Services team that Corporate BI will be a major focus in “Denali”, which is the code name for the next version of SQL Server – version 11. As far as the long-overdue x64 DM add-in for Excel, Microsoft is working on it and it will be delivered eventually. Meanwhile, the 2007 add-in works with the x32 version of Excel 2010.
For those of you going to SQL PASS (unfortunately, I won’t be one of them), Microsoft will announce important news about Denali and hopefully give a sneak preview about the cool BI stuff that is coming up.
https://prologika.com/wp-content/uploads/2016/01/logo.png00Prologika - Teo Lachevhttps://prologika.com/wp-content/uploads/2016/01/logo.pngPrologika - Teo Lachev2010-10-12 18:03:302016-02-16 12:42:17Where is x64 Excel 2010 Data Mining Add-in?
[Wikipedia] Cerberus, (pronounced /ˈsɜrb(ə)rəs/);[1] Greek form: Κέρβερος, /ˈkerberos/[2] in Greek and Roman mythology, is a multi-headed hound (usually three-headed) which guards the gates of Hades, to prevent those who have crossed the river Styx from ever escaping.
I think Microsoft got the name right although the Windows version of Kerberos has more than three heads for sure. Yet another weekend spent in troubleshooting Kerberos. This one has an interesting setup.
SharePoint Server 2010 on a Web Front End (WFE) box.
SharePoint Server 2010 (core install) + SQL Server and Reporting Services 2008 integrated with SharePoint on a second box.
Analysis Services 2008 R2 on a third box.
The customer wanted this setup to minimize the SQL Server licenses. Microsoft recommends installing Reporting Services on the WFE servers but this requires as many SQL Server licenses as the number of the WFE servers, plus probably two more (for SQL Server to host the SharePoint configuration databases and Analysis Services (if it is on a separate box). By contrast, the above setup requires two SQL Server licenses irrespective of the number of the WFE servers.
We hit issue #1 when we tried to deploy from BIDS and we got a login prompt that just won’t go away. This was related to the fact that the SQL Server setup configures Reporting Services for NTLM authentication. Since this scenario required Kerberos delegation, we have to change the following setting in the rsreportserver.config file:
As you can see, we had to disable NTLM and add Negotiate. This helped us to a point where BIDS can deploy reports to the SharePoint site and we can manage data sources and report properties. However, for some reason, after a few minutes the data source and report definitions somehow would get invalidated and trying to access their properties result in HTTP 401 User unauthorized error. Re-deploying the reports and definitions “fix” the definitions for a few minutes and then 401 error again.
More troubleshooting…and we figured out the issue was related to a misconfigured SPN entry for the SSRS service account on the ServicePoint core (database) server by executing setspn -l <SSRS service account>. The results didn’t include http/<the account used for SQL Server>. Once we got the Active Directory administrator fix this, the issue went away.
I had the pleasure to read the book Professional Microsoft PowerPivot for Excel and SharePoint by Sivakumar Harinath, Ron Pihlgren, and Denny Guang-Yeu Lee. All of the authors are with the Microsoft Analysis Services team. Together with David Wickert, Denny Lee runs the http://powerpivottwins.com/ blog, which is “dedicated to all things PowerPivot”. Siva has written the 2005 and 2008 editions of the Professional SQL Server Analysis Services with MDX. So, the book is straight from the horse’s mouth.
I really liked the book. It’s easy to follow and includes insightful tips. The book targets business users who are interested in creating self-service BI solutions. IT professionals and BI practitioners who will manage PowerPivot applications will find the book useful as well. Professional Microsoft PowerPivot for Excel and SharePoint incudes tutorials that walks the user through the necessary steps to build a PowerPivot-based solution. These steps include importing the data, enriching the data model, creating calculations, and reports. The code samples use a sample database for the healthcare industry – I guess that’s because healthcare is the only industry with a budget for IT applications nowadays, or at least self-service BI apps. Adventure Works is probably waiting for the economy to bounce back.
The authors are quick (page 17) to explain the role of the Corporate BI now that the self-service BI era has dawned on us – something that I was eager to get the official Microsoft opinion about. Their short answer is “not much will change”, and that’s precisely what I think. IMO, self-service BI is a compromise between corporate BI (best) and reality. Ideally, I don’t want business users to do anything but analyze. Why should business users import, relate data, and create calculations that as complexity increases look more bizarre then the equivalent MDX expressions? But we don’t live in a perfect world and PowerPivot comes to fill in the void. Here are some scenarios where PowerPivot might be a good fit:
1. You don’t have a data mart and Excel spreadsheets rule the reporting world. With some help from IT, business users might be able to get the data in the right format and report from it right in Excel.
2. You do have a data mart but you don’t want/don’t have the skills to build a cube. But if you’ve gone that far, why not travel the remaining 20% and have a cool cube that business users trust and love?
3. You have a data mart/cube but not all data is in the data mart/cube or you need to mash up data from various data sources.
4. You suffer from the NIHS (Not Invented Here Syndrome) and you just want to do things your way despite what IT and BI guys like your humble correspondent tell you.
All of these scenarios require compromises in terms of data quality, security, features, and implementation effort. The problems that we aim to solve with corporate BI are not trivial and there are no shortcuts. I know it and you know it. So, be skeptical when you see an impressive presentation that shows how a self-service BI will take upon the corporate BI, no matter if the tool is PowerPivot, QlickView, Tableau, or something else.
Overall, I’d highly recommend this book to anyone interested in PowerPivot and self-service BI. The book does a great job explaining the capabilities and limitations of the tool. I couldn’t stop reading it!
https://prologika.com/wp-content/uploads/2016/01/logo.png00Prologika - Teo Lachevhttps://prologika.com/wp-content/uploads/2016/01/logo.pngPrologika - Teo Lachev2010-07-31 03:28:002016-02-17 04:52:07Professional Microsoft PowerPivot for Excel and SharePoint
Although late to the party, I announce the arrival of a very interesting BI technology – PivotViewer Extension for Reporting Services – CTP1. What’s interesting is that it leverages several BI technologies to provide great data visualization:
The Microsoft LiveLabs Pivot which was developed by Microsoft Research.
You should keep a close eye of this combination (or at least the last four) as it will become increasingly important in the Microsoft BI stack. Robert Bruckner has provided details about the new arrival. Christian Petculescu, a Principal Architect on the SSAS team, is the driving force behind it. Other MVPs have spread the news. But Kasper de Jonge made my day! He has written a GREAT blog about it with step by step instructions to create your own PivotViewer solution. His blog also cleared my confusion about the role of Reporting Services in this architecture. So, Reporting Services has not been extended in any way. It’s just been used an image generator to produce the Pivot images. A better name for the tool could have been a PivotViewer Add-in for SharePoint.
Interestingly, Microsoft Pivot does it work by counting items in collections. It doesn’t support any other aggregation functions. Therefore, PivotViewer is not a replacement for Excel PivotTable. It gives you an additional way to visualize you data.
UPDATE
Amir Netz, the mastermind behind PowerPivot, demonstrated PivotViewer during the second day keynote of the BI conference this year. Forward to 1:21. He also talks about the new features that the next version or PowerPivot (SQL Server 11) will bring in, including KPI support, BIDS development support, etc.
https://prologika.com/wp-content/uploads/2016/01/logo.png00Prologika - Teo Lachevhttps://prologika.com/wp-content/uploads/2016/01/logo.pngPrologika - Teo Lachev2010-07-21 21:07:002021-02-16 03:46:57PivotViewer Extension for Reporting Services – a Cool BI Fusion with Confusing Name
I’ve just updated the Atlanta BI Group home page to announce the topic for our first meeting on August 23th. Given the great interest surrounding Self-service BI, I’ll present “Self-service BI with Microsoft PowerPivot”.
Hope you can make our first meeting!
https://prologika.com/wp-content/uploads/2016/01/logo.png00Prologika - Teo Lachevhttps://prologika.com/wp-content/uploads/2016/01/logo.pngPrologika - Teo Lachev2010-07-17 20:32:022016-02-17 04:58:42Atlanta BI Group First Meeting Topic Announced
Kudos to Greg Galloway (SQL Server MVP) for upgrading his fantastic OLAP PivotTable Extensions for Excel 2010 add-in (now available in both x32 and x64). This add-in is one of the first third party tools I install on a new machine. Since the Excel team seems to be ignoring customer feedback for improving the Excel BI features, OLAP PivotTable provides what’s lacking, including:
Excellent search capabilities with Field List, Dimension Data and attribute options. By contrast, the Excel 2010 search is limited to searching within a hierarchy level only. This is pretty much useless with parent-child hierarchies. If I know what level the member is located, I don’t have to search, right?
Getting the MDX query – Excel refuses to expose the actual MDX.
Calculated members – Another feature that business users keep asking about but Excel doesn’t budge. The Calculations tab of the add-in lets you create calculated members. It will be great if this is more user-friendly. Ideally, the user should get the same Calculation Member dialog that’s included in Reporting Services.
The great search for a meeting place for the Atlanta BI SIG is over! I am happy to report that I found the perfect place. Matrix Resources graciously offered to host and sponsor our meetings in a training room at their premises at 115 Perimeter Center Place #250, Atlanta, GA, 30346. I visited their location and another location today and I think their place is great. The room is capable of accommodating 50 people and has a projector. The location is nice too since I was looking for place around this area to accommodate the traffic concerns of as many people as possible. I personally live in Norcross and it would have been nice to take advantage of the Data Profit’s meetup offer but it would have been too selfish J
I also booked the meeting days for the rest of the year. I suggest we meet up every last Monday of the month from 6:30 – 8:30 PM as follows:
8.23, 9.27, 10.25, 11.22 and 12.27
Our first meeting will on be August 23th at 6:30 PM.
My focus now is to put together the Atlanta BI website that will be hosted on sqlpass.org. I’ll let you know when it’s ready so you could register and stay on top of the latest announcements. Stay tuned!
https://prologika.com/wp-content/uploads/2016/01/logo.png00Prologika - Teo Lachevhttps://prologika.com/wp-content/uploads/2016/01/logo.pngPrologika - Teo Lachev2010-07-01 19:08:002016-02-17 05:04:28Venue for Atlanta.MBI Found
To follow up on my recent blog about forming an Atlanta-based Microsoft Business Intelligence Special Interest Group, I am planning to have our first meeting in August. The effort now is to find a venue where we can meet. If you have a confirmed location, please post its address to the discussion thread. I’ll compile a list and I update this blog and the discussion thread.
Some of you have approach Geoff Hiten and me about forming an Atlanta-based Microsoft Business Intelligence group. After an intense internal discussion and brainstorming involving SQL Pass, Atlanta.MDF, and the local Microsoft office, we agreed that we should form such a group as a special interest group (SIG) within Atlanta.MDF. I volunteered to lead the group. I started a discussion on the Atlanta.MDF discussion list to solicit feedback and gauge interest about this endeavor. What should we do so you can get the most from this group? Please share your opinion by posting to the thread!
https://prologika.com/wp-content/uploads/2016/01/logo.png00Prologika - Teo Lachevhttps://prologika.com/wp-content/uploads/2016/01/logo.pngPrologika - Teo Lachev2010-06-14 02:03:482016-02-17 05:08:53Atlanta Microsoft Business Intelligence Special Interest Group (SIG) Forming
I am back from vacation in Florida and I am all rested despite the intensive sun exposure and the appearance of some tar from the oil spill. I have scheduled the next two runs of my online training classes:
Applied Reporting Services 2008 Online Training Class Date: June 28 – June 30, 2010 Time: Noon – 4:30 pm EDT; 9 am – 1:30 pm PDT
Applied Analysis Services 2008 Online Training Class Date: July 7 – July 9, 2010 Time: Noon – 4:30 pm EDT; 9 am – 1:30 pm PDT
Yes, I am also tossing in an hour of consulting with me to spend it any way you want absolutely FREE! This is your chance to pick up my brain about this nasty requirement your boss wants you to implement. So, sign up while the offer lasts. Don’t forget that you can request custom dates if you enroll several people from your company.
https://prologika.com/wp-content/uploads/2016/01/logo.png00Prologika - Teo Lachevhttps://prologika.com/wp-content/uploads/2016/01/logo.pngPrologika - Teo Lachev2010-06-07 02:45:002016-02-17 05:10:22Attend One BI Training Class – Get One Consulting Hour Free!
rpivottwins.com/
