File Connect In Salesforce -Integrating Office 365 Sharepoint within chatter Files

I remember couple of years back when I had to integrate Sharepoint with Salesforce ,only option I could think of is to visit appexchange and pay some bugs to get this up and running.There was a free app "Chatter For Sharepoint".Configuring this was again scanning through lot of documents .

File Connect lets you connect Microsoft SharePoint 2010, SharePoint 2013 or SharePoint Online account, and everything is indexed and made searchable within your Salesforce environment.File connect is not just about sharepoint and in general its about sharing salesforce files outside salesforce organization.

In this blogpost I will walk through simple steps to connect Microsoft 365 Sharepoint to Salesforce Files and bring Files located in sharepoint to SFDC.
It won't be too detailed as salesforce documentation around this is awesome but will add necessary Screenshot to help folks who want to set quick demo for clients to showcase.

Step 1-Sign Up for a trial account from microsoft for online sharepoint .Office 365 trial edition will work well .

Step 2-Select a sample site for sharepoint and try accessing this site .Note URL as this URL we will be using lot throughout configuration.

Also note microsoft domain as this will be used as well for further configuration.The below shows how your admin console look and you can navigate and select and activate a sharepoint site .

Step 3-Enabling file connect in Salesforce .

Choose Reference if you want only view else prefer copy option

Step 4-Creating permission set for Microsoft access 365 online

Assign this to Users

https://help.salesforce.com/HTViewHelpDoc?id=admin_files_connect_perm.htm&language=en_US

Step 5-Create an Authentication Provider for SharePoint Online or OneDrive for Business

This is well documented in below article from salesforce.

https://help.salesforce.com/HTViewHelpDoc?id=admin_files_connect_sp_online_auth.htm&language=en_US

I am attaching screenshot from both sharepoint and salesforce end on how this should look

The sharepoint App configured final screen should resemble something like below

Step 6-Creating external data source 

https://help.salesforce.com/HTViewHelpDoc?id=admin_files_connect_sp_online_xds.htm&language=en_US

On successful authentication click validate and sync button .

Step 7-Associate External data source with Permission set

Debugging tips

1)If oauth is failing double check auth provider configuration and domains entered in sharepoint settings .

2)If permission issues check permission sets and users are assigned .Also chatter for sharepoint is enabled for user or at profile level user is assigned valid permission.

Our final finished integration will look as below

Great thing is global search will search from sharepoint as well as chatter files

Hope you enjoyed and Let me know if anyone is encountering any issue in getting this to work .

Analytics Cloud-Creating My First Report

Analytics cloud is a new Business Intelligence platform for creating interactive reports and dashboards .

This product has been launched and for more info around this product you can refer below link
Analytics Cloud Overview Video

As an Enterprise customer you need to be aware of product limitations and products offerings and I don't work for salesforce and hold no responsibility in this regard and want to set expectation that whatever I am documenting in blog series is my personal take and thoughts  .

Understanding analytics cloud and Training Programs Offered From Salesforce

Remember that its simple to learn and understand the tool if you come from Business Intelligence world since you are already familiar with lot of concepts and theory needed for building charts and graphs .If you are not familiar there are lot of fundamental level trainings from Salesforce on this.Remember you can access this training if you are partner organization to salesforce through your organization partner portal 

1)Analytics Cloud Whitebelt Training

This training is delivered in form of webinar of very short duration that covers product offerings and release plans and an overview of technology under the hood .This is good to go through before we go ahead and take green belt .

2)Analytics Cloud Green-Belt Training

This is comprehensive training and covers lot of theoretical concepts of BI world.Some of the topics you will get familiar with this is listed below

• How analytics cloud differ from Salesforce 1 native reporting
• Traditional BI products Pros and Cons
• Who will use this analytics cloud (Concept of Explorer and Builder)
• Role of ETL in Traditional BI 
• OLTP and OLAP concepts
• Analytics cloud architecture
• Facts and Dimensions 
• Datasets
• Concept of Cardinality
• It has examples around creating reports and dashboards from Datasets

 3)Brown Belt Training

This is in person classroom training from the salesforce Product Team and Instructors who walk through the product offerings and provides opportunity for hands on exercises around the tool.

Please note all this are offered if you are a Partner Organization to salesforce.

Creating My First Analytics Cloud Report

My experience on creating the reports using the tool was really amazing .I never thought with so much of huge data rows you can draw reports and dashboards so quickly with minimal pain .

Since I have a sample training org with me I will walk you on how to create a simple report from a dataset .

Remember that a dataset is not only salesforce data ,but it can consist data from different sources .I will blog separately on how developers or builders can create datasets for business consultants .So we assume we already have dataset created from developers and data team for you as a business consultant to start drawing report .Also I assume you have org with analytics cloud enabled and analytics cloud license assigned through permission set.

The above is the first screen you will see once you select Analytics app from the salesforce set up.

Lets select Datasets and draw a simple report from opportunity dataset .The Search text box is a search that will list various options 

Click on the opportunity Cube or dataset and you should see a horizontal line showing no of rows of data in the dataset collection as below

The interface has three Items to pay attention and once we master those we are really good to go

1)Measure :This will include for facts the ability to count while for dimensions the ability to SUM,AVERAGE ,MIN or MAX 

2)Group:The data can be grouped using this and including multiple will further subgroup

 3)Filter:This is used to filter the result set and one can include multiple filters with various fields of datasets

Let say we need sum of Amount for each opportunity  ,we will just need to group by opportunity name and in the measure column its SUM of AMOUNT .

                                                                                                                    

You can use extreme right corner arrow menu to save to a private app folder or in your public folder and share this in salesforce chatter.

I hope you  this threw some light about analytics cloud . There is lot to explore and this is just the starting .

Again please note this is my personal opinion and anything you found interesting contact salesforce executives to get more info.

                   

Lightning Connect and SQL Integration Real Time Using Windows Azure Cloud Services-Part 2

This is a continuation from blog 1 where we had set up sample SQL server on the cloud using windows azure platform .

This article we will be doing below things 

1)Writing a Visual C# program to expose the SQL table and data as a Odata webservice(Using WCF template) 
2)Deploy the code on windows azure website
3)Accessing the SQL data using lightning connect using azure website URL

You will need below things as a prerequisite 

1)Install Visual studio from the here
2)Open online windows azure management portal that you had set up if you followed last article
3)Salesforce developer instance 

1)Create an OData Web Service

You now have a SQL Database in Windows Azure with tables and data, but now you need a simple and unified way to query and manage that data from all of the applications that you will build. Web services provide an abstraction over your data storage technology and with the broad adoption of web technologies and HTTP, RESTful web services are accessible from pretty much every platform and device. As a result, in this section you will create an OData web service using ASP.NET, an ADO.NET Entity Framework Data Model and a WCF Data Service.

To begin, open Visual Studio.

From the File menu, hover over New and select Project.

In the New Project dialog, browse to Installed > Templates > Visual C# and select the ASP.NET Web Application project template.

Enter WebApplication5 for the Project Name.
Enter WebApplication5  for the Solution name.

Click the OK button to continue creating your new Visual Studio project and solution. You will now be presented with the New ASP.NET Project dialog. For this OData project select the Empty ASP.NET template and click OK to continue.

Your new Visual Studio solution and project should now be displayed in the Visual Studio Solution Explorer.

In the Solution Explorer, right click on the WebApplication5 project and select Add > New Item…

Browse to Installed > Visual C# > Data and select ADO.NET Entity Data Model.

Enter ShoppingListModel in the name for ADO.NET Entity Data Model and click Add.

You will now be presented with the Entity Data Model Wizard. In the Entity Data Model Wizard, select Generate from database and click Next.

On the Choose Your Data Connection screen of the Entity Data Model Wizard, click on the New Connection button to display the Connection Properties dialog and configure the connection to your Windows Azure SQL Database.

In your web browser, navigate to the Windows Azure Management Portal at http://manage.windowsazure.comand browse to your MyShoppingList SQL Database home screen.

On this screen you will see your SQL Database server name at the bottom. Select your server name and copy it to your clipboard. Your SQL Database server name will look like yourserver.database.windows.net,1433.

Switch back to the Connection Properties dialog in the Visual Studio Entity Data Model Wizard and paste your SQL Database server name into the Server name text box.

Select Use SQL Server Authentication and enter your SQL Database User name and Password.

Expand the Select or enter a database name drop down and select your MyShoppingList database.

Click the OK button to finish configuring your SQL Database connection and return to the Entity Data Model Wizard.

Select the Yes, include the sensitive data in the connection string option and name your entity connectionShoppingListEntities in the text box at the bottom.

Click the Next button to advance to the Choose Your Database Objects and Settings screen.

In the Choose Your Database Objects and Settings screen, check the Tables check box to include all of the tables in the data model.

Enter ShoppingListModel in the Model Namespace text box and click the Finish button.

Now that you have added an ADO.NET Entity Data Model to your Visual Studio project, you need to add a WCF Data Service.

Right click again on the WebApplication5  project in the Solution Explorer and select Add > New Item…

In the Add New Item dialog, browse to Installed > Visual C# > Web and select WCF Data Service 5.6. Name the new WCF Data Service ShoppingListService and click Add.

WCF Data Services make it easy to create an OData service using an ADO.NET Entity Data Model by providing a generic DataService class that is constructed using an ADO.NET Entity Data Model.

Open the ShoppingListService.svc and locate the first TODO comment in the ShoppingListService class definition.

Change /* TODO: put your data source class name here */ to ShoppingListEntities. This change will setup your OData service. The only remaining thing to do is grant access to the entities exposed in the OData service.

Locate and uncomment the following line of code in the ShoppingListService.InitializeService method and change MyEntityset to *. This will grant read permissions to all entities in your OData service.

// config.SetEntitySetAccessRule("MyEntityset", EntitySetRights.AllRead);

Now you can test your OData service. Right click on ShoppingListService.svc in the Visual Studio Solution Explorer and select View in Browser.

You should now see the following AtomPub feed in your browser. This is the metadata listing the entities that are available in your OData service using the AtomPub web standard.

Click for larger image

Your OData service is now ready to be published to Windows Azure!

2)Publishing Your OData Service to Windows Azure

In your browser, navigate to the Windows Azure Management Portal and click on Web Sites in the list of services on the left-hand side.

Click the New button in the toolbar at the bottom. In the New menu that is displayed, select Compute > Web Site > Quick Create.

Enter a URL for your OData service in the URL text box. You should choose a URL that is relevant and descriptive. Since you are creating a web site for your OData service, you might want to append Api or Serviceto the URL.

Click the Create Web Site checkmark to create your website.

Note: To avoid bandwidth charges and added latency for the transfer between data centers, it would be a good practice to create your web site in the same region as your SQL Database.

After your website has been successfully created, select your web site and download the publishing profile by clicking on the Download the publish profile link.

After your publish profile is downloaded, switch back to Visual Studio and right click on yourCodeGuru.eSeminar.OData project and select Publish…

The Publish Web wizard will be displayed and you can begin publishing your OData service to Windows Azure.

On the Profile screen of the Publish Web wizard, click the Import… button to display the Import Publish Profiledialog.

On the  Import Publish Profile dialog, select the Import from a publish profile file option and click the Browse button. Browse  to the Publish Profile you downloaded from your Web Site in the Windows Azure Management Portal, select it and click OK to return to the Publish Web wizard.

You will notice that the Publish Web wizard has populated all of the Web Deploy configuration using the Publish Profile file that you downloaded from the Windows Azure Management Portal. Click the Publish button and your OData service will be published to your Windows Azure Web Site.

When your Publish has successfully completed, browse to your OData service at http://YourODataSite.azurewebsites.net/ShoppingListService.svc to verify that your service is accessible and returning the AtomPub feed that with your entities.

Below is the code sample in C# that generated the Odata webservice 

3)Configuring Lightning Connect to pull data into salesforce

I wont be elaborating much as it is highlighted on how to configure in below blog

http://reidcarlberg.com/2014/11/20/simple-salesforce-lightning-connect-example-external-objects/

Let me put a simple Video demoing this real time data sync

Lightning Connect and SQL Integration Real Time Using Windows Azure Cloud Services-Part 1

Back Office Real Time Integration is need for any Enterprise Applications residing on salesforce platform .One of Key challenges of Salesforce Enterprise customers have been how to show data in salesforce 1 platform from back office or third party systems  without actually storing or consuming the storage space .

Salesforce External Object and Lightning Connect solves this .If you are not so familiar with External Objects ,I would recommend below links for further understanding

http://www.slideshare.net/developerforce/df13-using-external-objects-for-integration-mt
http://www.tquila.com/blog/2014/11/11/what-salesforce-lightning-connect-and-external-objects
http://www.tquila.com/blog/2014/11/17/setting-lightning-connect-and-your-first-external-object
http://reidcarlberg.com/2014/11/20/simple-salesforce-lightning-connect-example-external-objects/
https://blog.heroku.com/archives/2014/11/13/heroku-external-objects

All of the above blog links are really good and they explain Odata in particular .Lets take some time now in exploring whats Odata

OData

The Open Data Protocol (OData) is a data access protocol built on core protocols like HTTP and commonly accepted methodologies like REST for the web.

Standardization of OData has been going on for years (they are working on version  4), but it has suffered from a bit of a chicken-egg problem. Applications haven’t put a large priority on supporting the consumption of OData because there haven’t been enough OData providers, and data providers haven’t prioritized making their data available through OData because there haven't been enough consumers.

With the above theory and concepts now we are set to use windows azure cloud services to set up a SQL server and expose data and metadata in SQL as Odata service .we will use salesforce lightning connect to connect to this web service and see the data in SFDC .We will create Tab in SFDC and view the data .Sounds Interesting and exciting as well .Lets carry on !!!

Creating a SQL table and adding data using windows azure cloud services

I started my career with salesforce and cloud so I love to be working with only cloud platforms .windows azure is a microsoft cloud platform.Azure is the only major cloud platform ranked by Gartner as an industry leader for both infrastructure-as-a-service (IaaS) and platform-as-a-service (PaaS). This powerful combination of managed and unmanaged services lets you build, deploy, and manage applications any way you like for unmatched productivity.

I signed up for free trial with the help of below link

http://azure.microsoft.com/en-in/pricing/free-trial/

If you don't have windows account ,you will need to create one to complete the sign up.Please note it does ask for credit card info but you are not charged any amount from that.

Once you sign up the windows azure you will be able to explore and create various services .Below picture shows management portal of azure platform

The New button exists throughout the Windows Azure Management Portal and will enable you to provision new services within your Windows Azure subscription. This button is also context aware, so if your focus is currently within the SQL Database service, clicking this button will default the selections for creating a new Windows Azure SQL Database.

Click on the New button. You should now be presented with a menu to create a new Windows Azure SQL Database. Notice that the menu has a list of the Windows Azure service categories on the left, followed by the services within the selected category, and options for creating your new service in the third column. ClickCustom Create to begin setting up your Windows Azure SQL Database.

You should now see the New SQL Database – Custom Create wizard.

In the Name text box enter Websites.

In the Subscription drop down, select the Windows Azure subscription where you would like to create your SQL Database.

1)In the Server drop down, select the Windows Azure SQL Database server that you would like to use for your new database. If you have created a Windows Azure SQL Database server previously, you can select that server, otherwise, select New SQL database server.

2)The remaining fields you can leave set to their defaults.

3)Click the checkmark in the lower right corner to advance to the next step in the wizard.

4)If you chose to create a New SQL database server, you should now see the SQL Database server settings screen.

5)In the Login Name text box, enter the user name you would like to use to login to your SQL Database.

6)In the Login Password and Confirm Password text boxes, enter the password you would like to use to login to your SQL Database.

7)In the Region drop down, select the Windows Azure data center where you would like your new SQL Database server provisioned. For optimal latency and cost of data transfer between data centers, it is recommended that you provision all of the services for an application that are dependent on one another in the same data center.

8)Lastly, leave the Allow Windows Azure Services to Access The Server check box checked. This will allow other services in Windows Azure, like the Windows Azure Web Site that we will create in future steps to access your SQL Database.

After you have provided all of the details for your new SQL Database server, click the checkmark in the lower right hand corner to finish the wizard and create your database.

 You will see progress in the toolbar at the bottom, and once your database has been created, you should see a success message like the following. 

Click the OK button in the notification bar to dismiss the message.

Once your database has been successfully created, go ahead and click on the Name in the list of databases, which will display information about your database and additional options and configuration.

The next thing we need to do is configure access to your SQL Database. By default, Windows Azure SQL Database firewalls all IP address from connecting to your server. As a result, you need to grant access to connect and manage your server. To grant access to your IP, click on Set up Windows Azure firewall rules for this IP address.

Your database is now ready! So now you are ready to add some tables and data.

Creating Tables in Your SQL Database

While on the home screen of your SQL Database, click on the Manage button in the toolbar at the bottom.

This will display the SQL Database Management Portal login screen. Enter the username and password that you used when creating your SQL Database and click the Log on button.

Select your newly created Table1 database and click on the Design button in the menu on the left.

You should now see options to design the schema of your database including Tables, Views and Stored Procedures.

Click on the New table button in the middle of the screen.

1)Enter Table1 in the Table Name text box.

2)On the ID column that was added by default, check the Is Identity? check box.

3)On the Column1 column that was added by default, enter FirstName under Column, leave the defaults forType set to nvarchar and the Length to 50.

4)On the Column2 column that was added by default, enter LastName under Column, leave the default for Typeset to nvarchar and set the Length to 50.

Click the Save button in the toolbar at the top to save the Person table.

Adding Data to Your Database

Now that you have the structure of our database setup, it is time to enter data into your tables. The same web-based SQL Database Management Portal that you used to setup the structure and schema of your database, you can use to add data to your database.

To begin, you will add data as shown

Now that your table is selected, click on the Data tab at the top and a list of rows from the Table1 table will be displayed. At this point, the list of rows should be empty, because you should have an empty table.

Click the Add row button to add a new row to the Person table.

The ID column is setup as an identity column and it will get automatically incremented and assigned when a record is added, which means you should leave this column blank.

Enter some text in the FirstName column.

Enter some text in the LastName column.

Repeat these steps two more times to add fields to the  table. 

Once finished adding rows to the table, click the Save button in the toolbar at the top to save your new rows to the table.

Now that you have fields  in your table named websites, you need to add Items to their Shopping Lists.

Click on the Item table in the menu on the left.

Click on the Data tab at the top and an empty list of rows from the Item table will be displayed.

Click the Add row button to add a new row to the Item table.

Once finished adding rows to the Item table, click the Save button in the toolbar at the top to save your new rows to the table.

Congratulations! You have now built a database and stored data in the cloud!

Now that you have people in your Person table and shopping list items in your Item table, you are done setting up your database and can now close the SQL Database Management Portal and move on to building your web service and application.

Summary

If you followed along with this article, you just:

1. Created a database in the Cloud using Windows Azure SQL Database

2. Set Up a table and defined your database’s schema

3. Added data to your database using the web-based SQL Database Management Portal

Next blog will demonstrate on how to expose this SQL table as an ATOM feed and then use lightning connect to display data .