Azure IoT

This is a post for people who are exploring Azure IoT for the first time. The idea is to stream the data from code push it into Azure and see it via visualizations in Power BI.

To do this we will need the following Azure resources.
1. IoT Hub
2. Event Hub
3. Stream Analytics
4. Service Bus
5. Logic Apps
6. SQL Server Database

The data flow diagram will be as shown in the image below




Steps -

1. Configure IoT hub
2. Write a code in python which will simulate sending data to IoT Hub
3. Configure Event Hub and add it in the message routing
4. Configure Stream Analytics with Event hub as an input
5. Configure SQL Server
6. Configure Service Bus
7. Configure SQL Server and Service Bus as two outputs for Stream Analytics
8. Create Logic apps based out of SQL Server database tables and create a work order table


Step 1 - Create and Configure IoT Hub -
a) Choose IoT Service in Azure and click on create. Once you do this you will be taken to a screenshot below -



b) After the IoT Hub service is provisioned, you should be able to see something similar to the screenshot below -
  

c) Now we need to create a sample IoT Device as shown in the snapshot below. We will be streaming our data using the connection string of this IoT Device.

     
            

d) Configure the message routing once you create the Event Hub.


       

      
              

Step 2 - Write a code in python which will simulate sending data to IoT Hub:

a) Pick up the connection string into a variable as stated in Step 1 c.
b) init the iot hub connection.
c) Format the string to simulate sensor data.
d) Send the data to iot hub
e) check for the status of reply.
f) Check for the status in iot hub.

import os
import datetime
import time
import random

import iothub_client
from iothub_client import IoTHubClient, IoTHubClientError, IoTHubTransportProvider, IoTHubClientResult
from iothub_client import IoTHubMessage, IoTHubMessageDispositionResult, IoTHubError, DeviceMethodReturnValue




CONNECTION_STRING = '<IoT Hub Connection String>'

sensorlist = [
                
                'Engine RPM'
    ]


AssetList = [
                'Device 1',
                'Device 2'
    ]

EngineVoltage = 233

sensorval_dict={
                
                "Engine RPM":5000
}


PROTOCOL = IoTHubTransportProvider.HTTP
MESSAGE_TIMEOUT = 10000

def iothub_client_init():
    # Create an IoT Hub client
    client = IoTHubClient(CONNECTION_STRING, PROTOCOL)
    return client

def send_confirmation_callback(message, result, user_context):
    print ( "IoT Hub responded to message with status: %s" % (result) )
    
    
def sensorDataParser(SensorData):
    attributes = SensorData.split(',')
    DeviceID = attributes[0]
    Parameter = attributes[1]
    Datetimestamp = attributes[2]
    Datetime = datetime.datetime.fromtimestamp(int(Datetimestamp)/1000)
    Val = attributes[3]
    #TypeofParam= attributes[4] Commented as of today
    MSG_TXT="{\"DeviceID\" : \""+ DeviceID +"\", \"Parameter\" : \""+ Parameter +"\", \"Datetimestamp\" : \""+ str(Datetime) +"\", \"Val\" : \""+ Val +"\"}"
    return MSG_TXT     


# Handle direct method calls from IoT Hub
def device_method_callback(method_name, payload, user_context):
    global INTERVAL
    print ( "\nMethod callback called with:\nmethodName = %s\npayload = %s" % (method_name, payload) )
    device_method_return_value = DeviceMethodReturnValue()
    if method_name == "SetTelemetryInterval":
        try:
            INTERVAL = int(payload)
            # Build and send the acknowledgment.
            device_method_return_value.response = "{ \"Response\": \"Executed direct method %s\" }" % method_name
            device_method_return_value.status = 200
        except ValueError:
            # Build and send an error response.
            device_method_return_value.response = "{ \"Response\": \"Invalid parameter\" }"
            device_method_return_value.status = 400
    else:
        # Build and send an error response.
        device_method_return_value.response = "{ \"Response\": \"Direct method not defined: %s\" }" % method_name
        device_method_return_value.status = 404
    return device_method_return_value


def iothub_client_connector():

    try:
        client = iothub_client_init()
        print ( "Successfully connected with the IoT Hub" )
        #client.set_device_method_callback(device_method_callback, None)
        
        while True:
            # to be changed later when the streaming comes from sensor data generator
            
            #to obtain random values from the list above
            now = datetime.datetime.now()
            datetimeval = int(datetime.datetime.timestamp(now)) * 1000
            Randomasset = random.choice(AssetList)
            #print(Randomasset)
            Randomsensor =random.choice(sensorlist)
            #print(Randomsensor)
            Radnomsensorval =sensorval_dict.get(Randomsensor)
            #print("The value for the sensor "+Randomsensor+" is "+ str(Radnomsensorval))
            senseval = Radnomsensorval + (random.random() * 15)
            
            #formation of the string
            SampleString = Randomasset+","+Randomsensor+","+str(datetimeval)+","+str(senseval)+",Control Analog"
            
            #breaking of the string
            devicereading=sensorDataParser(SampleString)
            print("Able to generate the message "+ devicereading)
            
            #sending the string to IoTHub
            message=IoTHubMessage(devicereading)
            
            #Check for success
            client.send_event_async(message, send_confirmation_callback, None)
            time.sleep(1)
            
    except IoTHubError as iothub_error:
        print ( "Unexpected error %s from IoTHub" % iothub_error )
        return
    except KeyboardInterrupt:
        print ( "IoTHubClient sample stopped" )

    except IoTHubError as iothub_error:
        print ( "Unexpected error %s from IoTHub" % iothub_error )
        return
    except KeyboardInterrupt:
        print ( "IoTHubClient sample stopped" )

                 


Step 3 - Configure Event Hub and add it in the message routing

a) Search for Event hub and click on Add, you will be taken to the screenshot below.
Choose a name, Enable Kafka (Please note this will be available in Standard tier and above. It will not be available in Basic Version)


                 

              


b) Once you create you should be able to see the image below


                      

                   


c) Click on the Event hub and then you can see the details of the event hub. Click on Add Event hub and provide the name, also choose the number of partition and the number of days for which the message should be retained.

Step 4 - Configure Stream Analytics with Event hub as an input

a) Search for Stream Analytics Jobs service in Azure portal and click on Add, you should be looking at a screen as per the screenshot below -

                  

b) Once this is done configure the Event hub as the input of data. You will have other option or Iot Hub but in this case we will be using Event hub as an input.


                                 

                      
    You need to configure the below -
    Input Alias: The name you want to provide for the input.
    You can choose the option: "Select Event Hub from your Subscriptions"  - This will allow you to choose the Event hub which we have created in the previous step.
    Subscription: Choose the subscription under which the Event hub is created.
    Event Hub Namespace: Choose the event hub namespace from the dropdown.
    Event Hub Name: Choose the option of "Use Existing" and choose the name of the event hub you have created in Step 3.
    Event Hub Policy Name: I have choose RootManageShared
    Event Hub Policy Key: This should get autopopulated.
    Event Hub Consumer Group: I will be leaving this blank so that it uses the $Default consumer group of the Event Hub.
    Event Serialization Format: I will be choosing JSON as I have formatted the data as a JSON in Step 2.
    Encoding: I will leave it as default which is UTF8
    Event Compression Type: None (since it is just an example, but you can use gzip or deflate option in case you are compressing large streaming data)
    

c)  Once this is done, we will need to configure the output and subsequently the query to obtain the data from Event hub and store it in SQL Server database.


Step 5 - Configure SQL Server
a) Search for SQL Server databases and then click on +Add which will take you to a screen as shown in the image below:
The first tab will be Basic:
Subscription: Choose the subscription that you want the database to be created.
Resource Group: Choose the Resource group

Database Details:
Database Name: Enter the name of the database
Server: If you do not have SQL Server resource, then click on create new and you should see in the section below:
Once the server name is added, then lets continue to provision Azure SQL database
Want to use SQL elastic pool: Yes (if you feel you will have multiple databases and you want to manage all of them within certain costs.)
Compute + storage: General Purpose

                 

Adding new Server:
Servername: Provide the name of the server of your choice. Please note the .database.windows.net will be suffixed to the name you provide.
Server Admin Login: Provide the login username of your choice
Password: <Strong password>
Confirm Password: <confirm your Strong password>
Location: Which region your database should exist, please ensure it is in the same region else there will be cost for transfer for data across regions.
Allow Azure Services to access server: Check this as we require azure services to access this database.


                 

The second tab is Additional settings:
You can leave everything as default.
Use Exisiting data: None (if you want to start fresh, if you have an existing data and you want to start with that then you can choose backup.)
Database Collation: You can leave it as default unless you have a specific collation type which you use in your PoC or organisation.

                 

Once this is done click on Review + Create and then click on Create.

Open Management Studio v17 and above to connect to clouddatabase with the servername, username and password as provided by you.

Once you are sucessfully able to open the database, create a table using the script below. This is the table which will hold the streaming data from the devices in this PoC.


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[DemoIot](
    [DeviceID] [nvarchar](50) NULL,
    [Parameter] [nvarchar](50) NULL,
    [Datetimestamp] [datetime] NULL,
    [val] [float] NULL,
    [ROWID] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_DemoIot_1] PRIMARY KEY CLUSTERED
(
    [ROWID] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Fact_ExceptionRecords](
    [DeviceID] [nvarchar](100) NULL,
    [Date] [datetime] NULL,
    [Type] [nvarchar](100) NULL,
    [Description] [nvarchar](300) NULL
) ON [PRIMARY]
GO



Step 6 - Configure Service Bus

a) Search for Service Bus service in azure portal and click on +Add, once you do this you will be able to see the screen as per the image below.

Provide the below details:
Name: The name of the service bus.
Pricing Tier: I will be choosing Basic
Subscription: Choose the subscription under which you want the resource to be present.
Resource Group: Choose the resource group under which you want the resource to be present or create a new one.
Location: Choose the region in which the service bus service to be present. Please ensure that the region remains the same as other services you have created, if not it can have cost associated to it because of the transfer of data.


                  

b) Create a queue in the service bus:
click on the service bus name and you should be able to see the screen as per the screenshot below.

Name: The name of the queue.
Max Queue Size: leave it with the default of 1 GB if you are using this for learning purpose, else modify it according to the volume of data that you are expecting.
Message Time to Live: This determines the time frame for which the messages will stay in the queue.
Lock Duration: The duration for which the message is locked so that only the one reciever has access to the data and once the time limit is reached, it will release the lock and the data will be available for other recievers to lock.
Enable Duplicate Detection: This will check if the same message is present in the queue and will not allow the message to be added if it feels it is duplicate.
Enable Sessions: This ensures the first in first out policy for the data as sessions ensure ordering of the messages in the queue.


             

Step 7 - Configure SQL Server and Service Bus as two outputs for Stream Analytics
Go to stream analytics which you have created in Step 4 and click on the resource, then you should be able to see the Outputs. Click on this and you will be able to choose the list of all resources which you can choose as outputs. The various options when this article has been written are -

    1) Event Hub
    2) SQL Database
    3) Table Storage
    4) Service Bus Topic
    5) Service Bus Queue
    6) Cosmos DB
    7) Power BI
    8) Data Lake Store Gen 1
    9) Azure Function

For this post, let us consider SQL Server Database and Service Bus which have been created in Step 5 and 6 respectively.

a) Configure SQL Database as the output.
On click of SQL Server database, you will be shown a configuration screen as per the screenshot below -


                      

Output Alias: The name you want to provide for the SQL Database output in stream analytics jobs
"Select SQL Database from your Subscriptions": Since you have already created the SQL Server database in Step 5.
Subscription: Choose the subscription under which you have created the SQL Server database.
Database: The name of the database which you have created
Username: The username to access the database
Password: The password to access the database
Table: The table in the database for which you have executed the script for.
Merge all input partitions into a single writer: Default, you can leave it as it is.
Max Batch Count: 10000 :You can leave it as default unless you see there are going to more records.

b) Configure Service Bus as the output -
For Service bus, there are two options p-
Service Bus Topic
Service Bus Queue
for this post, I will consider Service Bus Queue.

Below are config details for the service bus as output -
Output Alias: The name of the output for Service Bus Queue in stream analytics jobs.
Select queue from your Subscription: Use this option to ensure that you do not end up creating new service bus queue as we have already created this in Step 6.
Subscription: The name of the subscription you have used for the subscription of the service bus.
Service Bus Namespace: The namespace of the service bus.
Queue Name: Use Exisiting : This is to ensure you use the service bus queue which you have already created.
Queue Policy Name: RootManageSharedAccessKey
Queue Policy Key: This will be autopopulated and is not editable.
Property Columns: If you want some custom values to go to service bus, you can provide the names as a comma separated values. This is not required now and hence we will keep it blank.
Event Serialization Format: JSON
Encoding: UTF8
Format: Line Separated


                                  

c) Once this is done, go the stream analytics jobs, you will see a query window. Click on edit query and add a sample code below -

SELECT
       cast([DeviceID] as nvarchar(MAX)) as DeviceID
      ,cast([Parameter] as nvarchar(MAX)) as Parameter
      ,cast([Datetimestamp] as datetime) as Datetimestamp
      ,cast([val] as float) as val
INTO
    [SQLOutput]
FROM
    [eventhubinput]


SELECT
       cast([DeviceID] as nvarchar(MAX)) as DeviceID
      ,cast([Parameter] as nvarchar(MAX)) as Parameter
      ,cast([Datetimestamp] as datetime) as Datetimestamp
      ,cast([val] as float) as val
INTO
    [eventhubinput]
FROM
    [safracpump]
WHERE cast([val] as float) > 246.0

                                  

As you can see there are two outputs and in one single query. To test if it works fine you can upload sample data and then check. To do this,

a) Add the below records into a text file
{"DeviceID" : "DEVICE1", "Parameter" : "Engine Voltage", "Datetimestamp" : "2019-06-19 11:53:26.629000", "Val" : "233.825983439"}
{"DeviceID" : "DEVICE1", "Parameter" : "Engine Voltage", "Datetimestamp" : "2019-06-19 11:53:26.629000", "Val" : "233.825983439"}
{"DeviceID" : "DEVICE1", "Parameter" : "Engine Voltage", "Datetimestamp" : "2019-06-19 11:53:26.629000", "Val" : "233.825983439"}
{"DeviceID" : "DEVICE1", "Parameter" : "Engine Voltage", "Datetimestamp" : "2019-06-19 11:53:26.629000", "Val" : "233.825983439"}
{"DeviceID" : "DEVICE1", "Parameter" : "Engine Voltage", "Datetimestamp" : "2019-06-19 11:53:26.629000", "Val" : "253.825983439"}
{"DeviceID" : "DEVICE1", "Parameter" : "Engine Voltage", "Datetimestamp" : "2019-06-19 11:53:26.629000", "Val" : "233.825983439"}
{"DeviceID" : "DEVICE1", "Parameter" : "Engine Voltage", "Datetimestamp" : "2019-06-19 11:53:26.629000", "Val" : "253.825983439"}
{"DeviceID" : "DEVICE1", "Parameter" : "Engine Voltage", "Datetimestamp" : "2019-06-19 11:53:26.629000", "Val" : "233.825983439"}
{"DeviceID" : "DEVICE1", "Parameter" : "Engine Voltage", "Datetimestamp" : "2019-06-19 11:53:26.629000", "Val" : "253.825983439"}


b) Once it is done click on the input and choose "Upload sample data from file"


                                     

                                                                       

c) Once it is done click on test and you should be able to see two output tabs one for SQL and other for Service bus as shown in the image below -

                                             
                                          
                                      

Step 8 - Logic Apps to check the data in SQL Server table and create a record if the value is greater than treshold

a) Creation of Logic apps:
Search for Logic apps service in azure and click you should be able to see the image below -
                            

Name: The name of the logic app
Subscription: The name of the subscription you want
Resource Group: Use Exisiting
Location: The region in which you want the resource to be present.
Log Analytics: In case you want to monitor the workflows you can have it on, else it can be off.

Logic to be implemented -
Once a record is inserted into a table in the Azure SQL Database
Check if the value is greater than the treshold value
If the condition is true then insert the value into a new table.


b) Creation of flow:
Click on the Logic app designer and you should be taken to a screen where in you should be able to add the flows and implement your logic.

Once you do this you should be able to see the list of all inputs. We will choose SQL Server as an input.
              

                    
Once you select SQL Database it shows you two triggers for starting the flow. (Please note you can only choose trigger as a first step, you will not be allowed to use action)
The two options being -
1) When an item is created : This means the table should have a column with an auto increment feature. If the table does not have this, then the table will not appear in the dropdown.
2) When an item is modified : This means the table should have a column with rowversion so that it can understand if there is any modification to the record which will initiate the work flow.

                                                             

In our case the trigger will be when a new record is inserted into the table as there is no scenario where in we will be updating a record. Once you choose this you will be able to see the below screens -
Change the interval from 3 minutes to 1 second, This denotes the interval at which the service will check for the events.


                                                            

The connection in the snapshot is already present, if you want to add it click on new connection in your case and you should be able to see the screen as shown in the snapshot below -


                                                          

Enter the credentials for the database and choose connect via On Premise Gateway if your database is not in Azure environment or is a virtual machine inside Azure.
                                                   
Once it is done choose the name of the table from the dropdown. If you want to add any parameter which will help in filter / ordering / if you want to obtain specific columns from the table you can mention.

                            

                                                      

Once it is done, click on New Step and you will be provided with lot of options and suggestions. Click on Condition based on the logic which we are planning to implement.


                                                       

On click of this, you should be able to see a condition box comes up. Leave the condition to be And by default. Place the cursor on the "Choose a value"  you should be able to see a pop up comes up with dynamic content, it would have the list of all the columns which you have in the table. Choose the column "val" with "is greater than" in this case and enter a number of 246.

                                                       
                                         

Go to true condition below and click on "add an action", choose SQL SErver and you will be able to see a list of options. Choose "Insert row", you will be able to see a drop down populated with tables based on the database connection string. Choose the table "Fact_ExceptionRecords".

                              

                                                            

                                                

In the add new parameter choose the columns in the table as shown in the snashot below.


                                                       

Once you choose the columns, click on the columns one by one and you should be able to see the input columns in the dynamic content. Use the appropriate column names and for description few words "This has been flagged because the val is " Val (column) as shown in the image below.

 
                                                   

                            

Once this is done, click on save.

                                           


Once you start running you should be able to see the records in Runs history as shown in the snapshot below. It should appear as succeeded. If there is any error it will return failed. If the condition is not met it will return it as skipped.


                                                

Now if you run the code which generates the data you should be able to see the spikes in the messages on IoT Hub, Even Hub, Stream Analytics. You should be able to see the data in both the tables of SQL Server database.
Do let me know what you think about this blog, if it has helped you or if you feel anything needs to be improved.

I will try to add more services of azure in future blogs.

Dynamic Security in SSAS in Detail

We all have applications which have security implemented at the application access level and at data level. Now if we have cube over this for reporting we would like the data level security to propagate to this layer and subsequently into the cube.
So how do we propagate this?

Lets do this by considering a simple example of a sales data model as shown in the image below.

Given below are the dimensions-
1) Product - Stores all the products

2) Date - Stores all the dates valid in this case I am using only months

3) Employee - Stores the list of all users who can access the application.

Fact table -
1) Sales - Stores all the sales data.
 

Bridge Table -
1) Employee to Product Mapping - Stores the list of product an employee can see.


For employee in the login make sure you add the proper username which should be in <domain name>\<username> format. If you do not know open command prompt and enter the command whoami to find it out.

Now lets build the cube over this. All the tables mentioned above should be present in the data source view as shown in the image below.

Lets build the cube with 2 measure group.

1) Fact Sales - Contains the sales data
2) Emp Prod Bridge - Contains the count of mapping between users and products which will be used in the dynamic security.

You need to have the below dimensions -
1) Product
2) Employee
3) Date

Once it is done, click on the cube and then click on the dimension usage tab. The usage should be as shown in the image below. If it is not there add the dimension and make sure the connection between dimension and fact is present.
Once this is done process the cube. Then open the cube in SSMS and execute the below MDX

SELECT {} on 0,
NonEmpty (
[MST PROD].[PK PROD ID].[PK PROD ID].Members,
(
[MST EMP].[ATTR LOGIN].&[<username which you login>],
[Measures].[EMP PROD BRIDGE Count]
)
) on 1
from [Dynamic Security]

You can see only the products which you have mapped to user who has logged in is being displayed.

Now this is through MDX but how do we enforce this on the cube? For this follow the below Steps -

1) Make sure the users who access do not have administrator rights over the SSAS else this wont be effective since it will take the higher level privilege.
2) Click on Roles as shown in the image below 

3) Right Click on roles to add a new Role. Give the role some name and allow only Read Permission.
4) Go to the Membership and add the users or usergroup to this place. The best practise is to use usergroup since the permission to this cube can be handled while creating the user in the AD by admins. This will prevent frequent visits to the SSAS to provide access.

5) Go to Datasources and provide read permission.
6) Go to Cubes and provide Read and if needed drillthrough feature.


7) Now go to dimension data, you can see the list of all the dimensions present in the cube.

 
Click on Product dimension, go to the Advanced tab and add the code below
NonEmpty (
[MST PROD].[PK PROD ID].[PK PROD ID].Members,
(
StrToMember ("[MST EMP].[ATTR LOGIN].&[" + UserName () + "]"),
[Measures].[EMP PROD BRIDGE Count]
)
)

StrToMember ("[MST EMP].[ATTR LOGIN].&[" + UserName () + "]") is the code which will filter out the dimension data for the user who is accessing the cube.
If you want to allow the user to access over application or powerbi \ excel pivot \ power pivot use UserName(). If you are using sharepoint over this cube you need to use CustomData().

The permission will look like the image below -
 

Once you are done with the above step click on ok.

Now you are ready with the permission model. You can check this by clicking on the cube and then on the browse button. Once this is done you will see the user icon as shown in the image below.




Click on the user icon and then click on the user. Provide the username and click ok.

 














You can then browse the cube where in you can see only those records to which the user has access to.

The cube created above with the permission model can be used via Power BI or Power Pivot or Excel Pivot by the end users for Ad Hoc reporting as well.

Deploying SSAS on IIS

This post is about deploying SSAS cube on IIS



Deploying cube in IIS

Step 1 - Go to the root folder and create a folder <foldername>. In this I have created a folder called SSAS. Go to <SQL Server Installation path>\<SSAS Folder>\OLAP\bin\isapi and copy the content to the folder which you have created in the root folder.

Step 2 - Open inetmgr and create an application pool on .NET Framework V2.0 and Managed pipeline mode of classic as shown in the image below.

       
Step 3 - Once this is done, create a new web site with the physical path pointing to the folder which you have created in step 1.

                                                                              
Step 4 - Click on website name and then under IIS double click on Authentication.

           

Step 5 - Once it is done right click on Anonymous authentication and click on disable and then enable windows authentication in the same way.

       


Step 6 - Double click on Handler Mappings and on the actions click on Script map same as in step 4.

Step 7 - For Request Path enter *.dll, Executable - point it to the msmdpump.dll in the folder of the report and name it.       


Step 8 - Go to the website folder and you can see a web config file. You need to add the server name <ServerName> as shown below
<ConfigurationSettings>
    <ServerName>fluturavm\fluturahd</ServerName>
    <SessionTimeout>3600</SessionTimeout>
    <ConnectionPoolSize>100</ConnectionPoolSize>
</ConfigurationSettings>
           

Step 8 - Open SQL Server Management Studio and connect to Analysis Service. Once it is done for the server name type the url of the website http://localhost:9999/msmdpump.dll and click on connect.

Business Intelligence Site in Sharepoint 2010

Now that we have SharePoint platform up and running in our system, lets go ahead and create a Business Intelligence site. There are various types of sites which you can create from the site collections available in the sharePoint portal, the details of the same is given here (http://technet.microsoft.com/en-us/library/cc262410%28v=office.14%29.aspx).

So what is a Business Intelligence Site?
        A Business Intelligence site is used for storing reports and its data connections which will then be consumed in web parts and displayed on the portal. It also has a special feature called performance point services using which you can create interesting reports which is different from your conventional SSRS reports which I will take up in the next post.

    Features of Business Intelligence Sites are
    1) Excel Services
    2) Power Pivot
    3) Reporting Services
    4) Performance Point Services

So how do I set up a Business Intelligence Site in SharePoint 2010

Step 1: Ensuring the required services are up and running-

    Ensure that Performance point Services feature has started on the system, to check this go to "Application Management" and click on "Manage services on server"
as shown in the image below

You can now see all the services present in SharePoint and also check if the services have started or not. Now scroll down and you can see the Services PerformancePoint Service and Secure Store Service and make sure its Started, if it is stopped Start the service by clicking on Start on the Action Column.

Step 2: Ensuring the services are configured properly-

a) Ensuring Performance Point Services is configured properly
    Now that the services are running, we will check if the services have been configured properly. Go to Central Administration and click on Manage Service Application as shown below

Once you do this you can see the list of services as shown below

go to Performance Point Service and click on it will take you to the manage screen, click on the PerformancePoint Service Application Settings

In the Settings screenm you can see if the user name has been configured or not. For now we will keep the default, except for the User Name (you need to add a user name if there is no user name in the Unattended Service Account).

b) Ensuring Secure Store Service is configured properly

    Secure Store Service is a service which helps in single sign on for its users, this service needs to work properly for you to go ahead and create any site. Sometimes the key might have generated properly or it might be missing hence we need to verify if this is done properly.
    Go back to the page where we saw all the services and click on "Secure Store Service" as shown in the image below

Once you have done this, check to see if there is a key generated for PerformancePoint Service application, if its not generated or if there is some problem go ahead and delete if there are any existing keys for PerformancePoint Service and click on Generate New Key as shown in the image below.

Step 3: Creating the Business Intelligence Site

    To create a new site, Click on Central Administration and click on Create Site Collections as shown in the image below

    It will now take you to a Create Site Collection Screen where in you can configure all the site properties like,

Web Application      - Defines the site under which the site collection should be created.
Site Name       - The name of the Site.
Web Site Address  - Configuring the URL through which you can access the new site.
Template Selection- There are various templates available in SharePoint as mentioned at the start of this post, since we are creating a Business Intelligence Site             click on Enterprise Tab and then click on Business Intelligence Center.
Primary Site Collection Administrator - User Name of the Admin has to be specified.
Secondary Site Collection Administrator - User Name of the secondary Admin has to be specified.
Qutoa Template - No Quota

The configuration will be as shown below

Once done, click on OK and the Site Collection will be created and once it is done you will get the screen shown in the image below.

Click on the link and the you will be taken to the screen where you will deploy, view reports.

Sharepoint2010 Installation

          Now that you have downloaded the installable lets go ahead and install the platform on the system.

Step 1-

Before you start installation download the update given in the link →  

http://www.microsoft.com/en-us/download/details.aspx?id=8165 

Step 2-

Once you install the updates click on the exe file to start the installation and you will see a splash screen of the SharePoint, 

when you click on Install Sharepoint Server you will see an error message as given below.

This error appears because SharePoint is by default set to be installed on the server and not on the normal OS. But there are 2 workaround for this by modifying the config.xml file.

a) When we started with the installation by clicking on the exe file it would have extracted all the files to a location on the machine. So if you go to Process Explorer while the splash screen is still present then go to the sharepoint process and check the Image properties in the Properties window then you can see the location where it has extracted the files.

b) if you dont want to do all this then just execute the following command in the command prompt

<path of the executable file> /extract:<path of the extracted files>

Once this is done then go to the config.xml in the setup folder and add the below tag into it

<Setting Id=”AllowWindowsClientInstall” Value=”True”/> . This setting will allow you to install the file on normal Windows OS as well. The config file now should look like the code given below.

<Configuration>
        <Package Id="sts">
                <Setting Id="LAUNCHEDFROMSETUPSTS" Value="Yes"/>
        </Package>
        <DATADIR Value="%CommonProgramFiles%\Microsoft Shared\Web 

ServerExtensions\14\Data" />
        <Package Id="spswfe">
                <Setting Id="SETUPCALLED" Value="1"/>
        </Package>

        <Logging Type="verbose" Path="%temp%" Template=

"SharePoint Server Setup(*).log"/>
        <!--<PIDKEY Value="Enter Product Key Here" />-->
        <Setting Id="SERVERROLE" Value="SINGLESERVER"/>
        <Setting Id="USINGUIINSTALLMODE" Value="1"/>
        <Setting Id="SETUPTYPE" Value="CLEAN_INSTALL"/>
        <Setting Id="SETUP_REBOOT" Value="Never"/>
        <Setting Id="AllowWindowsClientInstall" Value="True"/>
</Configuration>

 

Step 3 –

Now click on the executable file from the folder which has the extracted files and you will see the splash screen again. Now install all the prerequisites required for SharePoint.

a) Microsoft Sync Framework
b) SQL Server Native Client
c) Windows Identity Foundation (Windows6.1-KB974405-x64.msu)

 

Step 4 –

Once these are done go ahead start of with the installation, It will ask you for a product key even though its a trial version.

 So use the key VK7BD-VBKWR-6FHD9-Q3HM9-6PKMX which is also given in the details section in the page where you downloaded SharePoint. 

Step 5-

Next it will ask you to choose between StandAlone and FarmMode, I will choose Farm Mode since some of the features like PowerView and Excel Services require the Sharepoint to be Farm Mode. The installation starts and will take sometime depending on the hardware configuration on your system.

Step 6-

Once you complete the installation you need to configure the SharePoint installation. The Farm Mode requires you to have Domain\Account, if you are using your personal system which does not have a domain controller the UI wont allow you to install in the farm mode, but there is a workaround for this as well,

Go to Sharepoint Management Shell located in the start menu (given below is the image for the same)

Right Click on the Sharepoint 2010 Management Shell and click on 'Run as Administrator' A command prompt opens up, here you need to execute a SP which helps you configure Farm Mode by using your system account itself rather than using a Domain Account.

SP Name :New-SPConfigurationDatabase

DatabaseName :<your Database Name>

DatabaseServer:<Database Server Name>

FarmCredentials: it will open up a pop up window where you need to enter your credentials. For Username please use <domain>\<username> (if you dont have name, your machine name itself is the Domain).

PassPhrase : Password for the sharepoint.

Once this is done run the configuration, there will be a screen where you can choose to create a new Server Farm or connect to an existing farm. Since we already have created farm we will choose connect to an existing farm.

Step 7 -

it will then ask you if you want to configure the port for Central Administration and also its security settings. We use the default setting which port 16915, NTLM as Security Setting and click on next.

Step 8 -

You will notice that it has already picked up the farm database and other details. Click next and it will configure the sharepoint 2010. Once done you will get a image which is shown below.

Step 9 -

Once this is done click on the SharePoint 2010 Central Administration in the start menu as shown below.

This should open up a web page as shown below.

You have now successfully installed and configured SharePoint 2010. Please let me know if you have any trouble while installing the same.

The next post will be about configuring services and accounts on the SharePoint portal.

Sharepoint Introduction

What is SharePoint?
    SharePoint is a platform which traditionally was used for content and document management but now Microsoft is trying hard to change this perception. The SharePoint now has the capability to host Intranet portals, extranets, websites, discussion boards and Business intelligence; with the latest version of SharePoint 2013 they have also provided the social network integration.


    The first release of SharePoint was in 2001, the various versions of SharePoint released from 2001 till 2013 are given below -

1) Microsoft SharePoint Portal Server 2001
2) Microsoft SharePoint Team Services 2002
3) Windows SharePoint Services 2003
4) Windows SharePoint Services 2007
5) Microsoft SharePoint Services 2010
6) Microsoft SharePoint Services 2013

In this blog I will be taking you through various features of Sharepoint, but it will be concentrated more on the Business Intelligence part of it, I will be considering SharePoint 2010 and may be in future will write about SharePoint 2013 :) .

There are 2 versions of SharePoint 2010

1) Standard Edition
2) Enterprise Edition
The Enterprise Edition will have Access, Excel, Visio, Performance Point services which is not present in Standard Edition. In this blog I will be using Enterprise Edition for all demo purpose.

You can download the SharePoint software from the Microsoft WebSite, the link for which is given below.












http://www.microsoft.com/en-us/download/details.aspx?id=16631