Showing posts with label PowerShell. Show all posts
Showing posts with label PowerShell. Show all posts

Saturday, March 30, 2013

SharePoint Online BI and Connecting to On-Premise Data

Refreshing Data with PowerPivot and Excel Services

I came across this really good whitepaper on how to refresh data in PowerPivot in Excel Services in SharePoint Online. The most important part is “in SharePoint Online”.

If you know much about SharePoint Online Business Intelligence, may know we provide Business Intelligences features in SharePoint Online with limitations. The most important being something like Excel Services cannot make a live connection to a database. If the data resides in the Excel spreadsheet itself no problem Excel Services works great with that data. The challenge has always been how to get the data refreshed into the spreadsheet if the data needs to be pulled from a line of business databases?

The solution described in the spreadsheet takes advantage of several new capabilities of SharePoint Online. It takes advantage of the new SharePoint Online PowerShell commands, the Sync capability of SkyDrive Pro and the new PowerPivot solution now supported in SharePoint Online. The script is very simple. All you do is schedule it, it will check out the Excel workbook, refresh the data, check it in, and then sync the file right back into SharePoint Online. Nice!

Before this new release of SharePoint Online, organizations would have to manually do this action. If an organization got crafty they could use web services to accomplish the same thing but it would be a cumbersome solution to manage and maintain.

This new solution is just so easy and straight forward.

Refreshing Excel workbooks with embedded PowerPivot models from SharePoint Online (white paper) - http://technet.microsoft.com/en-us/library/jj992650.aspx

SQL Reporting Services

If you have read the new SharePoint Online Service Description on Insights (http://technet.microsoft.com/en-us/library/jj819252.aspx) you will have noticed several new capabilities. One of these new capabilities is SQL Reporting Services reporting web parts are now supported. SSRS reports running out of a document library in the cloud can utilize data in a SharePoint lists. You could use a similar solution using the new SharePoint Online Client Side Object model to refresh data in a SharePoint list that the SSRS report would use. The only limitation to consider is the amount of data you are moving into the cloud. I am not suggesting that a SharePoint List cannot handle the data however we all know that SharePoint Lists are not meant to replace something like a SQL Server database. I would recommend that you do as much processing and aggregation of the data before sending up into SharePoint lists. It may even make sense to de-normalize the data a little.

Still you should be able to create some really nice reports that can be centrally managed and access through SharePoint Online.

Visio Services

Visio Services has always been available in SharePoint Online. I have in the past successfully used it to connect to data in SharePoint lists and then create both reports and dashboards. As usual the limitation was how to get the data on-premise.

The way I see it, you have two approaches for getting Visio Services in SharePoint Online to work with a database on-premise.

First, you can do what I just described above where you use the Client Side Object Model to get data into SharePoint Online lists.

Second is leverage BCS! This is new. If you have read what is new with Visio Services (http://msdn.microsoft.com/en-us/library/jj164027.aspx) you will see that SharePoint Online supports BCS. Additionally if you have SharePoint 2013 on-premise, it is now possible to implement a SharePoint Hybrid solution (http://technet.microsoft.com/en-us/library/jj838715.aspx). In this case you can have live on-premise data securely connected to SharePoint Online which Visio Services can build reports off of.

Conclusions

As you are seeing with the introduction of SharePoint Online PowerShell, Client Object Model, SharePoint Hybrid, SSRS, PowerView and SkyDrive Pro along with new improvements to BCS, Excel Services and Visio Services we now have some better options for BI in SharePoint Online. I see the gap starting to close and hope with time, more gaps to be closed.

Saturday, May 15, 2010

New SharePoint 2010 Features Part 6 – Administrative Features

Administration Features

This is the last part of my new series on SharePoint 2010. In this blog I will focus on some of the new administrative features of SharePoint 2010 and why they are important for solving your challenges with managing SharePoint.

Monitoring and Health Analyzer (Reference)

We now have a new rules based infrastructure that monitors and maintains health called the SharePoint Health Analyzer. It can automatically check and monitor rules for configuration, performance and security and then notify farm administrators. There are several rules that come out of the box, however there is an extensible API that can be used to write custom ones and then deployed to the farm.

As well, there are tools like:

  • Unified Logging Service (ULS) logs – robust logging infrastructure of SharePoint.
  • New Health Reports provides insight into the overall farm performance and can aggregate reports across server farm environments. These reports can be leveraged to proactively manage issues that can arise at the site, web or even page level.
  • New Usage Infrastructure which provides for streamlined logging of usage data via an API. Data can be stored in log file which will subsequently picked up by a timer job and committed to a SQL database so the usage data can be aggregated for reporting.
  • Improved SharePoint Web Analytics Service which provides a lot of new reports than were not available in SharePoint 2007, the ability to filter data, report scheduling, customize reports using Excel,
  • A new developer dashboard that provides developers and administrators data to investigate the cause of run time errors with performance, call stack and exception information without having to dig into logs.
  • SPMonitoredScope is a function available to developers to allow them to identify the source of performance and resource usage on server side code. It will monitor execution time, number of SharePoint requests, number of SQL requests and will write this data out to ULS Logs.
  • Microsoft Systems Center Operations Manager Pack that provides real time notification of issues, aggregates data from both SharePoint ULS Logs and Windows Event Logs, and leverages the logging database and Health Analyzer reporting data.

Monitoring and Health Analyzer Thoughts

What have seen with the high adoption of SharePoint 2007 is SharePoint environments grow at a rate that was not anticipated. Organizations and companies now have to make investments in personnel to administer these environments. I think anything in this area to assist with configuration management of SharePoint 2010 was badly needed and it is great to see that Microsoft has incorporated these rich features into SharePoint 2010. Many of these new features are going to greatly empower SharePoint administrators that ability to monitor and manage their SharePoint environments.

Performance Monitoring and Request Throttling (Reference)

Microsoft has introduced new features to allow them to better handle performance. Specifically SharePoint has introduced a new programmable system for throttling HTTP requests when WFEs become too busy to handle all the requests. You have the ability to:

  • Identify Windows Server 2008 performance counters that will be used to determine which WFEs are too busy.
  • Define threshold values for counters that cause the worked thread to begin throttling.
  • Ability to define which type of HTTP requests is throttled.
  • Define HTTP requests that are exempt from throttling.

Performance Monitoring and Request Throttling Thoughts

This may seem not that interesting for a business user to understand but it great that this has been introduced. What this can allow us to do is better manage the types of HTTP requests that are being requested from our SharePoint WFEs. For instance you may have known Excel Service reports that require a lot of resources to run and can potentially lock out users from doing standard views on basic content pages on the main homepage. In this scenario, we now have the ability to better manage performance such that the homepage is given the highest priority.

Sandboxed Solutions (Reference)

Sandboxed solutions are a new concept to SharePoint 2010. What this basically does is allow site collection users the ability to upload their own custom code solutions into SharePoint. Sandboxed solutions can only use a subset of the Microsoft.SharePoint API. Sandboxed solutions are stored in a solution gallery and there facilities that allow farm administrators to monitor solutions that have been deployed. Performance can as well be monitored for CPU usage, memory consumption and database query time, abnormal termination, exceptions and data marshaling size.

Sandboxed Solutions Thoughts

Previous versions of SharePoint required us to be administrators to deploy solutions however this is no longer the case. Sandboxed Solutions are great in that they provide a solution to allow for custom solutions to be deployed into SharePoint without requiring administrative support. Even with a subset of functionality, this could still be very powerful and allow business to be more agile in meeting real-time demands. Plus having the administrative features ensures that bad custom solutions can be better monitored.

Security Enhancements (Reference)

There are some changes associated to security that you should be aware of for SharePoint 2010.

One of the first and foremost is support for Claims-Based authentications. At a high-level claims-based aware systems will take a claim (i.e. a username, email address, etc.) from a user and then an external identity system will provide your system with all the information needed about a user. Using a claims-based authentication, single sign-on becomes easier to achieve because SharePoint will no longer be responsible for authenticating users, storing usernames/passwords, calling user directories to look up identity details and need to integrate with identity systems on other platforms.

With SharePoint 2010 there are five supported sign-in modes supported:

  • Windows Classic-Mode Sign-In
  • Windows Claims-Mode Sign-In
  • SAML Passive Sign-in Mode
  • ASP.NET Membership and Role Passive Sign-In
  • Anonymous Access

Some other new features of SharePoint 2010 Security are:

  • Managed Accounts – This is a new feature that allows administrators to identify in one place the account (username/password) that would be used in the configuration of SharePoint (for instance the web application pools in IIS). Then when configuring Central Admin, users only have to select a managed account and do not need to know the password which system administrators may only know. As well, when a password needs to change, the administrator only has to change it one place and it will be replicated throughout.
  • Automatic Password Change – In correlation with Managed Accounts, there is a new automatic password change service that will update passwords without having to perform manual password update tasks across multiple accounts, applications or services. This feature can also be used to determine if a password is about to expire and to reset the password using a random string.
  • Check User/Group Permissions – There are some new features available to users and administrators to manage permissions. It is now very is to view site collection permission for a group and check permissions for a particular user/group on a web/list/item using the new ribbon. As well there is a feature the will give a visual indicator that content and been uniquely secured.
  • Secure Store Service – This replaces SharePoint 2007 SSO Feature.

Security Enhancement Thoughts

Obviously seeing that Claims Based Authentication has been adopted into SharePoint 2010 is a good thing which will help with over security. In my experience the managed accounts, automatic password change and check permissions features are way more exciting because these have been major pain in the side of administrators and developers when managing a SharePoint environment.

ULS Logging (Reference)

There have been several new improvements to ULS Logs for SharePoint 2010:

  • SPMonitoredScope is a code attribute that when decorated on a class or method will write logging information to the SharePoint usage database.
  • Request Usage Database captures every SharePoint resource request that is made when a page is rendered.
  • Developer Dashboard is a new utility for developers that can be enabled on any SharePoint web page. It is similar in concept to the ASP.net tracing pages from a long time ago.
  • API Improvements of SharePoint 2010 have made it easier for developers to write custom trace logs.

ULS Logging Thoughts

Anything that is provided to make developers lives easier to debug runtime, production issues was greatly needed. Having to dig through the ULS logs to find a specific error is very time consuming for SharePoint developers. These changes will be well received by development teams and allow them to more quickly support production environments.

Windows PowerShell for SharePoint (Reference)

PowerShell for SharePoint is a command line interface, tool and scripting language for SharePoint 2010. This replaces Stsadm.exe which was used for SharePoint 2007.

Windows PowerShell for SharePoint Thoughts

This is one of the hottest topics with SharePoint administrators and developers as this is significantly more powerful to the tools we have had in the past. PowerShell for SharePoint is a game changer for administration of SharePoint. I cannot say how many times we have had to create custom command line applications to do administrative tasks. Now system administrators, with no coding experience, have the ability to write robust administrative jobs to manage their environments.