August 27, 2015

Tabbed View Report using SSRS

Today, I published my first Tech Net article about creating Tabbed View Report using SSRS. SQL Server Reporting Services is a powerful BI tool by Microsoft.
Soon, I will publish articles about SSRS and its integration with SharePoint. Read my article from the following link and share your valuable comments:
Scenario Explained in the article:
Suppose take an example employee database which stores their professional summary, salary information, personal information, etc. The end user will search for an Employee by Emp ID as the parameter only one time and the result will be displayed in a tabbed view like professional summary in one tab, salary in another tab and personal information in another tab. Each tab should be clickable and when a Tab1 is clicked, other Tab result should be hidden. In the similar way, show and hide data should work for all the other tabs.
Don't forgot to share this post if this helps you!

Update 22-Sep-2015
I am happy to share with you all that my first TechNet article is awarded for SQL BI and Power BI Technical Guru - August, 2015.
Gold Winner!!!!

August 26, 2015

Deprecated Features in SharePoint 2016 Preview

SharePoint Foundation
1. SharePoint Foundation 2013 remains available for use
2. Previous releases of SharePoint Server included SharePoint Foundation, a free edition of SharePoint that included most of the core functionality and architecture provided by the commercial editions of SharePoint. SharePoint Foundation is no longer available in the SharePoint Server 2016 release.
Standalone Install mode
SharePoint Server 2016 IT Preview doesn't support the standalone install option, so it is no longer available in the setup program. Use the MinRole during installation and choose one of the available install options. The Single Server Farm option where everything is installed on the same computer is supported for dev/test/demo purposes. When you use this option, you must install SQL Server yourself and then run the SharePoint Server 2016 farm configuration wizard.. For more information, see "MinRole farm topology" in New and improved features in SharePoint Server 2016 IT Preview.
ForeFront Identity Manager client (FIM)
Earlier versions of SharePoint used ForeFront Identity Manager client (FIM) to synchronize between Active Directory and SharePoint. SharePoint Server 2016 IT Preview no longer uses FIM as the synchronization client. The default process is Active Directory Import. You can also use any synchronization tool such as Microsoft Identity Manager 2016, or any third-party tool. We'll soon release tools to help you deploy and configure Microsoft Identity Manager 2016 to work with SharePoint Server 2016 IT Preview for identity synchronization.
Excel Services in SharePoint
Excel Services and its associated business intelligence capabilities are no longer available in SharePoint Server. Excel Services functionality is now part of Excel Online in Office Online Server Preview, and SharePoint users can use the services from there.
The following Excel Services functionality has been deprecated:
  • Trusted data providers
  • Trusted file locations
  • Trusted data connection libraries
  • Unattended service account
  • Excel Services Windows PowerShell cmdlets
  • Opening of Excel workbooks from SharePoint Central Administration site
The following Excel Services functionality requires Excel Online in Office Online Server Preview:
  • Viewing and editing Excel workbooks in a browser (with or without the Data Model)
  • Excel Web Access web part for SharePoint
  • ODC file support (no longer requires Data Connection Librariees)
  • Programmability features such as JavaScript OM, User Defined Function Assemblies, SOAP and REST protocol support
SharePoint BI capabilities
SharePoint Server 2016 requires updated versions that will ship later this year. The SQL Server 2014 Power Pivot and Power View add-ins for SharePoint 2013 cannot be deployed or used in SharePoint Server 2016 IT Preview. The following business intelligence features won't be available until then:
  • Power Pivot Gallery
  • Scheduled Data Refresh
  • Using another workbook's Data Model as a data source
  • Power View reports (standalone or embedded in Excel workbooks)
  • Power View Subscriptions and Report Alerting
Power Pivot Management Dashboard
  • BISM Link support
Tags and Notes
The Tags and Notes feature is no longer available in SharePoint Server 2016 IT Preview. Users can no longer create new tags and notes or access existing ones.

August 25, 2015

SharePoint 2016 Preview Arrived

I am excited to share the news with my blog readers that SharePoint 2016 Preview is arrived and it is available for download in the below link:

SharePoint 2016 Forum Discussions:
Customer Feedback Link:

Soon will explore on SharePoint 2016 and post you all about the new features and updates :)

August 16, 2015

Power BI in SharePoint Online

I am very excited after Microsoft released Power BI GA (general availability) at the end of July. It comes with list of impressive new features which is explained in detail in this MSDN article.
I want my blog readers to know what is Power BI and how it can be used with SharePoint Online to leverage the BI capabilities of SharePoint. Let's learn the basics in this article.
Articles in SharePoint Business Intelligence (BI) Series:
Power BI in SharePoint Online
Creating reports using OData Feed in Power BI
Power View report in SharePoint
Display Power BI reports in SharePoint Online
Microsoft came with a new tool for BI for the following reasons:
  1. Use the known tool  - Excel
  2. Discover More  - Power Query
  3. Analyze More – Power Pivot
  4. Visualize  - Power View / Power Map
  5. Combine with Office 365 – share with any one
  6. Power BI sites – collaborate and share the data sets with your team
  7. Dive into Q & A – ask a question and get answers visually
  8. Stay Connected – Mobile Apps
  9. Schedule data refresh and create security settings easily
So what is Power BI?
  1. Cloud based service works with excel to provide users a complete self-service BI solution
  2. Available in SharePoint & Excel 2013 Online (available only in E3 / E4 subscription)
  3. With Power BI, you can easily deploy a cloud based BI environment for users to share insights, collaborate and access reports from anywhere on any device
  4. With Power BI for Office 365, you can easily setup an online gallery for users to share insights
  5. Mobile apps available in Apple store, Windows store & Google play store
How does Power BI differ from SharePoint BI?
Power BI
SharePoint BI
Content Viewing
It displays all document libraries with a preview to reports / dashboards
It displays only pivot gallery document library
Natural Language
Q & A provides the end user to search for data with your questions and it displays the results in visualization
No such option available
Workbook as a data source
Power BI the data model and the reports (created with Excel pivot tables + charts, or Power View for Excel) everything must be contained within a single .xlsx file
In SharePoint, we can upload a Power Pivot model to the Power Pivot Gallery and other reports can connect to it using a URL-based connection string. This works for Power View for SharePoint, Reporting Services, PerformancePoint, and Excel
Data Refresh Option
It is simpler for end users to manage the data refresh
Lot of options available for data refresh and it’s controlled by Admin
Data Management Gateway
It is a hybrid environment (partially cloud based and partially on premises)
No data management gateway available
Data rendering option
HTML 5 (hence cross platform support)
Silverlight for data preview
Maximum workbook size
Max is 250MB
2 GB
Sign in with Organization ID (O365 ID)
AD security mechanism
Enterprise Search for data
Search query is the important feature and it can search public and data within organization
Mobile Application
Available in Microsoft, Apple and Google Play store
Where can I download the Power BI and related products?
  1. Sign up for Power BI
  2. Power BI for desktop
  3. Power BI Analysis Services Connector
  4. Power BI Business Gateway
  5. Power BI Apps for mobile (apps for Apple, Microsoft and Android mobile)
  6. Power BI pricing
Hope this article, gives an idea of Power BI and how it differs from SharePoint BI. I will explain about connecting data from different data sources, creating dashboards, reports, etc. with Power BI in the coming articles.
Hope this helps you! Please share you comments since it helps me write more. Also, share this post to your friends via the networks below.  

August 8, 2015

Power Query and Power Map in Excel 2013

As you all know, Microsoft is improving the BI capabilities with a lot of new features with exciting releases in the year 2015. Microsoft has introduced new add-ins called Power Query and Power Map for Excel 2013.
Believe me guys, this is really awesome add-ins which taken the Microsoft's BI capabilities one step ahead of all other BI competitors!
Let's try to create a power map by searching a public data using power query in Excel 2013 in this article.
Before trying a sample, let's understand what is power query and power map for excel 2013 and it's download links:
Power Query:
  • Microsoft Power Query for Excel (a data analysis tool) is an Excel add-in that enhances the self-service Business Intelligence experience in Excel by simplifying data discovery, combine, refine, access and collaborate.
  • Power Query supports importing data from a wide range of Web sources, including scrapping tables from HTML pages, as well as importing data from Web APIs. Power Query supports XML, JSON and OData formats
  • It includes intuitive discovery features, a powerful query language, and a built-in ability to share your discoveries with others

Power Map:
  • Power Map provides interactive, 3D visualization with geospatial and temporal data
  • It enables information workers to discover and share new insights from data through
    • rich, 3D visualization on a globe with fluidic appearance & guided cinematic tours
    • seeing time-stamped data change over time (which you might not have seen in traditional 2D tables and charts) and to share it with others
    • you can create a video tour easily in a single click
    • Geo flow is another feature which shows how the
    • data changed over a period of time

Excel Add-in Links:
  1. Power Query for Excel 2013
  2. Power Map for Excel 2013
Creating a Power Map with GeoFlow:
Let's add a public data from internet by using Power Query - connecting data from the existing excel file.
I have downloaded the U.S. Operable Generating Units in the United States by State and Energy Source, 2011 from the link. Thank you for the data!
Open the excel 2013 -> Power Query tab -> Get External Data -> From Excel

This will open the query editor where you can select the data sets (this downloaded excel sheet has various tabs with different power sources). You can combine the data using Power Query data editor as well.
Let's select only "Coal" worksheet, delete the unwanted columns and create a date column using initial month and year (which is required for showing the growth of coal energy plants across U.S by county in power map) as shown below:

Now, click Insert Map -> Power Map option in the excel file -> Power Map will load the data source and it should automatically detect the geo-location fields from the dataset.
I have created two types of visualizations, one which is heat map visualization which will show the coal power generated in U.S. by state as shown below:
 The other one is coal power generated in U.S. by county with a time by using Initial Month of Operation column as shown below:

Power Map gives the options of creating video tour in a single click from the menu. Here is the video, I created from this sample:
Cool, isn't it?
Thanks for reading! Don't forgot to share this post and post your valuable comments which will make me write more.