Access Google Analytics API 3.0 using Oauth2.0 in SSIS

Access Google Analytics API 3.0 using Oauth2.0 in SSIS

Steps to using a service account to access the API, as example

Generate a service account key in your Google API Console:

  1. Go to the Google API Console.
  2. Click on the “API Access” tab.
  3. Click on “Create client ID…”

img1

    1. Select “Service account” as the “Application type” and click on “Create client ID”.

img2

    1. Download your private key and store it in a safe place that only you can access.

img3

    1. A new section called “Service account” should appear under your API Access information. Ensure this page is handy as you will need to copy the “Email address” later on.

img4

Using service accounts and assertion flow with Google OAuth 2.0 services require that you have your own domain registered with Google Apps. This is because user impersonation is controlled at the domain level with no finer granularity of access control. In other words, all users of a domain using a service account that has been white listed with the power to impersonate, can impersonate any user in the domain. For example, this is why Gmail accounts cannot be impersonated with service accounts.

Security concerns

Because of Google Apps domain-level control, it is important that you protect the *.p12 key file that allows a service account to access the Google services for which it has been authorized. This is especially the case since we are going to be granting that service account the ability to impersonate any user in the domain. It is also a good practice to allow service accounts to only access one Google API each (using the “scope” field).

Steps to login in Accounts:

Screenshots: (http://www.google.co.in/analytics/)

Login to above mentioned URL:

img5


Need to add Client Id-Email as a user for each account in
Users Section with all the required permission like Administrator.

SSIS Code to Access Google Analytics API 3.0

Below is the code to access Google Analytics in SSIS using script task:

We need to create below variable to store information about AccountID and AccountName with respect to google analytics accounts.

e.g; AccountID : ga:306787, AccountName : http://www.abc.com

  1. User::var_client_password : This will store the .p12 key path.
  2. User::var_client_username : This will store the email address which has been created in Service account.
  3. User::var_Ed_date : We can pass any date filter.
  4. User::var_src_file: Path of the source where we need to export csv files from Google Analytics.
  5. User::var_st_date : We can pass any date filter.
  6. User::var_table_id : It will store AccountID like ga:12222

We need to add below version-3 dll(s) references in script task to access Google Analytics API:

  1. DotNetOpenAuth
  2. Google.Apis
  3. Google.Apis.Analytics.v3
  4. Google.Apis.Authentication.OAuth2
  5. Newtonsoft.Json.Net35
  6. System.Core

 Steps:

Step 1: Access the Google Analytics data

Step 2: Export the data into csv files based on AccountID(s).

Step 3: We can use CSV files as source to load in database using SSIS.

Below is the source code:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data;

using DotNetOpenAuth.OAuth2;

using Google.Apis.Analytics.v3;

using Google.Apis.Analytics.v3.Data;

using Google.Apis.Authentication.OAuth2;

using Google.Apis.Authentication.OAuth2.DotNetOpenAuth;

using Google.Apis.Services;

using Google.Apis.Util;

using System.Security.Cryptography.X509Certificates;

using System.IO;

using Google.Apis.Json;

String Client_ID = string.Empty;

String Key_Path = string.Empty;

String Account_ID = string.Empty;

String strstartdate = string.Empty;

String strenddate = string.Empty;

String strsrcfilepath = string.Empty;

publicvoid Main()

{

strstartdate = Convert.ToDateTime(Dts.Variables[“var_st_date”].Value).ToString(“yyyy-MM-dd”);

strenddate = Convert.ToDateTime(Dts.Variables[“var_Ed_date”].Value).ToString(“yyyy-MM-dd”);

Client_ID = Dts.Variables[“var_client_username”].Value.ToString();

Key_Path = Dts.Variables[“var_client_password”].Value.ToString();

Account_ID = Dts.Variables[“var_table_id”].Value.ToString();

strsrcfilepath = Dts.Variables[“var_src_file”].Value.ToString();

//This is the API url which we’re storing to a string

string scope = AnalyticsService.Scopes.AnalyticsReadonly.GetStringValue();

//It is the email address

//that you have added as a user to your Analytics account

string client_Email = Client_ID;

//This is the physical path to the file we downloaded earlier

//To demonstrate this, I’ve kept the full path to my key file.

string key_file = Key_Path;

//The password Google gives you, probably the same as the one below

string key_pass = “notasecret”;

//Store the authentication description

AuthorizationServerDescription desc = GoogleAuthenticationServer.Description;

//Create a certificate object to use when authenticating

X509Certificate2 key = newX509Certificate2(key_file, key_pass, X509KeyStorageFlags.Exportable);

//Now, we will log in and authenticate, passing in the description

//and key from above, then setting the accountId and scope

AssertionFlowClient client = new AssertionFlowClient(desc, key) { ServiceAccountId = client_Email, Scope = scope };

//Finally, complete the authentication process

OAuth2Authenticator<AssertionFlowClient> auth = new OAuth2Authenticator<AssertionFlowClient>(client, AssertionFlowClient.GetState);

//First, create a new service object

AnalyticsService gas = new AnalyticsService(new BaseClientService.Initializer() { Authenticator = auth });

//Create our query

//The Data.Ga.Get needs the parameters:

//Analytics account id, starting with ga:

//Start date in format YYYY-MM-DD

//End date in format YYYY-MM-DD

//A string specifying the metrics

DataResource.GaResource.GetRequest r = gas.Data.Ga.Get(Account_ID, strstartdate, strenddate, “ga:Visitors,ga:visits”);

//Specify some addition query parameters

r.Dimensions = “ga:date”;

r.MaxResults = 10000;

//Execute and fetch the results of our query

GaData d = r.Fetch();

DataTable dt = newDataTable();

dt.Columns.Add(“ga:date”);

dt.Columns.Add(“ga:visitors”);

dt.Columns.Add(“ga:visits”);

if (d.TotalResults > 0)

{

foreach (var row in d.Rows)

{

DataRow dr = dt.NewRow();

dr[“ga:date”] = row[0];

dr[“ga:visitors”] = row[1];

dr[“ga:visits”] = row[2];

dt.Rows.Add(dr);

}

}

WriteToCSV(dt);

Dts.TaskResult = (int)ScriptResults.Success;

}

publicvoid WriteToCSV(DataTable dt)

{

// Create the CSV file to which grid data will be exported.

StreamWriter sw = newStreamWriter(strsrcfilepath, false);

// First we will write the headers.

//DataTable dt = m_dsProducts.Tables[0];

int iColCount = dt.Columns.Count;

for (int i = 0; i < iColCount; i++)

{

sw.Write(dt.Columns[i]);

if (i < iColCount – 1)

{

sw.Write(“~”);

}

}

sw.Write(sw.NewLine);

// Now write all the rows.

foreach (DataRow dr in dt.Rows)

{

for (int i = 0; i < iColCount; i++)

{

if (!Convert.IsDBNull(dr[i]))

{

sw.Write(dr[i].ToString());

}

if (i < iColCount – 1)

{

sw.Write(“~”);

}

}

sw.Write(sw.NewLine);

}

sw.Close();

}

Advertisements
Posted in SSIS | 13 Comments

How to count every 15 Mins duration group by employees(http://forums.asp.net/t/1808316.aspx/1?How+to+count+every+15+Mins+duration+group+by+employees)

I have seen such type of scenario. so i am providing the easiest way to handle it.

Table format:-

EmpID Name TransactionDate
101 Abdul 2012-05-2012 08:40:30 0000
101 Abdul 2012-05-2012 09:40:30 0000
101 Abdul 2012-05-2012 12:20:30 0000
102 Raju 2012-05-2012 08:15:30 0000
102 Raju 2012-05-2012 12:40:30 0000
102 Raju 2012-05-2012 16:20:30 0000
103 Albert 2012-05-2012 08:40:30 0000
103 Albert 2012-05-2012 10:40:30 0000
103 Albert 2012-05-2012 12:20:30 0000
103 Albert 2012-05-2012 14:40:30 0000
103 Albert 2012-05-2012 15:40:30 0000
103 Albert 2012-05-2012 17:10:30 0000
103 Albert 2012-05-2012 17:15:30 0000
104 Raghu 2012-05-2012 08:00:30 0000
104 Raghu 2012-05-2012 09:40:30 0000
104 Raghu 2012-05-2012 12:20:30 0000
102 Johnson 2012-05-2012 08:00:30 0000
102 Johnson 2012-05-2012 12:40:30 0000
102 Johnson 2012-05-2012 16:20:30 0000

Expected Output
——————-

Sl.No TransDate StartTime End Time Count (No. Of Transaction)
Group by employee
1 2012-05-2012 8:00 8:15 2 Raghu and Johnson
2 2012-05-2012 8:15 8:30 1 Raju
3 2012-05-2012 8:30 8:45 2 Albert and Abdul

Solution:-

CREATE Table TranTbl (EmployeeID Int,[Name] Varchar(100),[Date] DateTime)

Insert Into TranTbl (EmployeeID, [Name], [Date])
Select 101, ‘Abdul’, ‘2012-05-12 08:40:30’ Union
Select 101, ‘Abdul’, ‘2012-05-12 09:40:30’ Union
Select 101, ‘Abdul’, ‘2012-05-12 12:20:30’ Union
Select 102, ‘Raju’, ‘2012-05-12 08:15:30’ Union
Select 102, ‘Raju’, ‘2012-05-12 12:40:30’ Union
Select 102, ‘Raju’, ‘2012-05-12 16:20:30’ Union
Select 103, ‘Albert’, ‘2012-05-12 08:40:30’ Union
Select 103, ‘Albert’, ‘2012-05-12 10:40:30’ Union
Select 103, ‘Albert’, ‘2012-05-12 12:20:30’ Union
Select 103, ‘Albert’, ‘2012-05-12 14:40:30’ Union
Select 103, ‘Albert’, ‘2012-05-12 15:40:30’ Union
Select 103, ‘Albert’, ‘2012-05-12 17:10:30’ Union
Select 103, ‘Albert’, ‘2012-05-12 17:15:30’ Union
Select 104, ‘Raghu’, ‘2012-05-12 08:00:30’ Union
Select 104, ‘Raghu’, ‘2012-05-12 09:40:30’ Union
Select 104, ‘Raghu’, ‘2012-05-12 12:20:30’ Union
Select 102, ‘Johnson’, ‘2012-05-12 08:00:30’ Union
Select 102, ‘Johnson’, ‘2012-05-12 12:40:30’ Union
Select 102, ‘Johnson’, ‘2012-05-12 16:20:30’

GO

CREATE PROCEDURE GetExpectedOutput
AS
BEGIN

DECLARE @startTime DATETIME = ‘8:00’,
@endTime DATETIME = ‘7:15’

IF @endTime< @startTime
BEGIN
SELECT @endTime= DATEADD(DAY, 1, @endTime)
END

; WITH rcte AS
(
SELECT StartTime = DATEADD(MINUTE, CEILING(DATEDIFF(MINUTE, 0, @startTime) / 15.0) * 15, 0)
,EndTime = DATEADD(MINUTE, CEILING(DATEDIFF(MINUTE, 0, @startTime) / 15.0) * 15, 0)

UNION ALL

SELECT StartTime = DATEADD(MINUTE, 0, EndTime)
,EndTime = DATEADD(MINUTE, 15, EndTime)
FROM rcte
WHERE DATEADD(MINUTE, 15, EndTime) <= @endTime
)

SELECT ROW_NUMBER() OVER(ORDER BY StartTime) AS Rno
,CONVERT(TIME,StartTime) StartTime
,CONVERT(TIME,EndTime) EndTime INTO #tmp
FROM rcte
WHERE StartTime<>EndTime
SELECT H1.*
,H2.Name INTO #tmp2 FROM
(SELECT * FROM #tmp A CROSS JOIN ( SELECT DISTINCT CONVERT(DATE,[date])TransDate FROM TranTbl) B )H1
JOIN
(SELECT * FROM TranTbl) H2
ON H1.TransDate=CONVERT(DATE,H2.[Date]) AND CONVERT(TIME,H2.[Date]) BETWEEN H1.StartTime AND H1.EndTime
ORDER BY H1.StartTime,H1.EndTime
SELECT ROW_NUMBER() OVER(ORDER BY StartTime) AS [Sl.No]
,TransDate
,CONVERT(VARCHAR(5), StartTime, 108)StartTime
,CONVERT(VARCHAR(5), EndTime, 108)EndTime
,COUNT(name) AS [No. Of Transaction]
,STUFF((SELECT ‘ and ‘ + CAST(t2.Name AS VARCHAR(MAX))
FROM #tmp2 t2 WHERE t1.Rno = t2.Rno AND t1.TransDate = t2.TransDate AND t1.StartTime = t2.StartTime AND t1.EndTime = t2.EndTime
FOR XML PATH(”)),1,5,”) [Group by employee]
FROM #tmp2 t1
GROUP BY Rno,TransDate,StartTime,EndTime
END
–EXEC GetExpectedOutput

Posted in SQL SERVER | Leave a comment

Create reports from xml data source and xml source from Sql Server table.

Create reports from xml data source and xml source from Sql Server table.

Summary: This is  an example of creating reports from XML data source in SSRS.

Business Scenario:  In some situation we have to create reports in SSRS but data source should be in XML format & some times we have to generate XML from Sql Server tables.

Solution:   Firstly we have to create  report in SSRS and we can see the table which we want to convert into xml. so just open the new query window.

select * from empsal

and write the command for generating the xml file in sql server.In result window SSMS shows the query output in XML format.

select * from empsal for xml path(‘no’),root(‘ename’)

Click on that XML it will open in other query window.Now you can see the XML format of selected table.

Now we have to go to Report in BIDS and select the Report Data, click on new then select Data Source… and in data source we have to put the name of Data Source and in Embedded connection we have to select XML type.

Then we have to select another tab Credentials and here we have to select “use windows authentication (integrated Security) and click Ok.

Now Data Source created , we have to create dataset so right click on DataSource which you created earlier and select Add DataSet.

Then go to query designer. here you have to copy the xml file which you already generated in T-SQL and you have to add few things along with XML query and then click on run command it will show the output then click Ok.

<Query>
<XmlData>
<Root>

——-Here you have to paste the xml format created by T-sql.———

</Root>
</XmlData>
</Query>

Data set is ready and we have to create reports. We can create any type of reports such as tabular, matrix, bar charts etc. so here i am creating different type of report in one page.

If you click on the preview tab then you can see the data and charts.

So that’s all about the procedure how we have to create reports from XML data source.

Posted in SSRS | Leave a comment

Split a flat(text) file into multiple flat(text) files using ssis

Split a flat(text) file into multiple flat(text) files using ssis

Summary: This is in an example of splitting a flat file into multiple files based on business scenario.

Business Scenario:  In our situation we want to splitting a flat file which contain around 2 gb data for e.g: 2000 records and we want to split into multiple files and each file should contain 50-50 record.

Solution:  Create a new ssis package.

i created  3 variable:

no  variablename    datatype    values

1.       counter                 int32           0

2.       countrows          int32            0

3.       pathname            string          C:\Users\jeevesh.fuloria\Desktop\Exel Sheets\may task\split folder\j

here we have to create some dataflow task and control flow task. so for this scenario i am taking some control flow task such as sequence container,dataflow task,for loop container, script task and some dataflow task such as row count, conditional split etc. see the picture below:

In top dataflow task name count total rows here i am counting the total rows in a flat file how many rows are there in flat file.

next one is for loop container. in for loop contain i am using 3 variable which i have created earlier.This is basically for looping purpose till reach the counter to  total rows in flat file.

here i am increasing the variable value @counter=@counter+50. every times it will increment by 50 to till total rows.and @counter<@countrows means it check the condition whether @counter value is less then @countrows or not.when @counter value reach the @countrows then it will exit from the loop.and firstly i put 0 value in @counter.

now here this is second data flow task naming splitting flat file.i am taking flat file source and flat file destination for splitting purpose. and also using data conversion and conditional split task. data conversion is basically for converting the datatype of column.here i am converting the datatype from varchar to numeric of id column.

 then using condition split task.here applying condition that is value should be high than the value of @counter (0) and is value should be less then @counter+50,because  every time @counter value change or increment.

[Data Conversion].id > @[User::counter] && [Data Conversion].id <= @[User::counter] + 50

then go to flat file destination and select the path, and right click on flat file connection manager for destination, go to properties and click expression,then take connection string and put the pathname variable value ,click ok.

now go to script task.it is very imporant. we are using script task for giving dynamicallly file names when file going to create. here select all variable in readwritevariables.

the click on edit script for writing some script for giving dynamic name.

now click on ok. every thing is complete in package for doing the task. firstly we check the folder where we want to splitting the files.

folder is empty. so time to execute the package .i am executing the package.

it is still executing the task. yellow means executing, and green means sucessfully executed.

so package successfully executed. now we have to check in folder where we want to split the files.

so here we have multiple files,each file contain 50-50 rows.we can check the files data.

 like this file each file contain 50-50 records. so finally task completed. and we can also take the source as old db table instead of flat file.

Posted in SSIS | 3 Comments

How to solve SSAS cube browser error and doing some drag and drop.

How to solve SSAS cube browser error and doing some drag and drop.

Summary: This is in an example of correcting the error in a Cube in SSAS.

Problem: In our situation there was a problem when we browser a cube and drag any field it show some error.

The query could not be processed: 0 XML for Analysis parser: The LocaleIdentifier property is not overwritable and cannot be assigned a new value.

Solution:

If we click on browser in a cube it show above error this is because of your regional settings.

Regional settings have different settings between the “Regional Options” tab and the “Advanced” tab in the “Regional and Language” tab in control panel.

So for solving this error go to start menu then go into the “control panel” and click on “Clock, Language, and Region”.

Open “Regional and Language Options”.

Click on the “Formats” tab; select English (United States) from format.

Click ok.

Now select the cube from server explorer. Go to browser. Now see there is no error in browser.
Now we can drag and drop the fields.

Posted in SSAS | 1 Comment

How to Process and deploy a SSAS Cube.

How to Process and deploy a SSAS Cube.

Summary: This is in an example of deploying and processing a Cube in SSAS.

Problem: In our situation there was a problem when we deploying and processing a cube it show some error.

Errors and Warnings from Response

Internal error: The operation terminated unsuccessfully.

OLE DB error: OLE DB or ODBC error: Login failed for user ‘NT AUTHORITY\NETWORK SERVICE’.; 28000; Cannot open database “target” requested by the login. The login failed.; 42000.

Errors in the high-level relational engine. A connection could not be made to the data source with the DataSourceID of ‘Target’, Name of ‘Target’.

Errors in the OLAP storage engine: An error occurred while the dimension, with the ID of ‘Dim Hos Code’, Name of ‘Dim Hos Code’ was being processed.

Errors in the OLAP storage engine: An error occurred while the ‘Hosp Code’ attribute of the ‘Dim Hos Code’ dimension from the ‘SSAStask’ database was being processed.

Server: The operation has been cancelled.

Solution:

Deploying a cube is very simple. So for removing this error I am doing some process.

First select the database name or project name from solution explorer, right click the click on process.

After clicking on process it first ask “Would you like to build and deploy the project first?” so if this is a first time to deploying or processing a cube then click yes otherwise no.

I am clicking yes. Now the process has started. Here it is showing that deployment completed successfully. This means in your analysis service database, the structure of database is created, but you can’t access your cube, because the process is not complete. It shows the next wizard.

Now click on run. See here it is showing some error “process failed”

So for removing this error go to solution explorer. Double click on .ds file.

Now here go to Impersonation Information tab and select “use a specific windows user name and password”

And put your username and password here. Now click ok

Again select the database name or project name from solution explorer, right click and then click on process. Here is also see deployment completed successfully. Now click on run, this time it is showing process succeeded.

So cube is ready, you can access cube either from BIDS or from SSMS by connecting the analysis service.

Here we can drag and drop column based on requirements.


Posted in SSAS | 3 Comments

Get data into report from more than one dataset.

Get data into report from more than one dataset.

Summary: This is in an example of using reporting services for creating a report by using two dataset. Remember this for doing this we should have at least one match column in both data set.

Solution:

I am selecting two tables from AdventureWorks database.

SELECT *  FROM [AdventureWorks].[Production].[ProductCategory]

SELECT *  FROM [AdventureWorks].[Production].[ProductSubcategory]

Here I have one match column ProductCategoryID in both tables.

Go to report and create data source and two dataset.

Here I created dataset1 and now creating 2nd dataset.

So now I have 2 dataset here in report.

Here I am taking category name in new column, clicking right click on textbox, go to value then write expression. I am using lookup.

Lookup (

Matched column value from first data source,

Matched column value from second data source,

The value of column which you want to put from second data source,

”second dataset name”)

Here I am putting value in 5th column as category modified date, also applying same lookup function for next column.

Report is ready.

Click on preview you can see here column from both data set.

Here product subcategory id, subcategory name, subcategory modified date fields are from dataset2 and category name and category modified date fields are from dataset1.

Posted in SSRS | 4 Comments