tag:blogger.com,1999:blog-82102952887027066202024-03-26T23:37:54.891-07:00BI SummitAll About implementing Business Intelligence solutionsNishil Jainhttp://www.blogger.com/profile/04987547678651181303noreply@blogger.comBlogger9125tag:blogger.com,1999:blog-8210295288702706620.post-37270596543464623732019-08-01T14:31:00.000-07:002019-08-01T14:31:10.374-07:00Stock Prices ETL :TD Ameritrade API<div dir="ltr" style="text-align: left;" trbidi="on">
I was think few days back about how BI could work with Stock Prediction.<br />
To do it first I needed regular stock price data from the internet.<br />
So to do it I wrote a quick code in Python to get stock prices from TD Ameritrade API.<br />
So once I have the data feeding in regulary I will write the next post of prediction algorithms.<br />
<br />
<br />
Here is the code:<br />
<span style="color: red; font-size: large;"><b>SQL Table:</b></span><br />
CREATE TABLE [dbo].[Stocks_Price](<br />
<span style="white-space: pre;"> </span>[Symbol] [varchar](10) NOT NULL,<br />
<span style="white-space: pre;"> </span>[Open] [float] NULL,<br />
<span style="white-space: pre;"> </span>[High] [float] NULL,<br />
<span style="white-space: pre;"> </span>[Low] [float] NULL,<br />
<span style="white-space: pre;"> </span>[Close] [float] NULL,<br />
<span style="white-space: pre;"> </span>[volume] [float] NULL,<br />
<span style="white-space: pre;"> </span>[TransDate] [date] NOT NULL<br />
)<br />
<br />
The python code has a Start and End date which can be modfied the way you want.<br />
The symbols for which we get prices for is in the file Symbols.txt.<br />
Symbols.txt has only text as shown below for all the stocks<br />
AAPL<br />
AABA<br />
TSLA<br />
<br />
The list of all Symbols on Nasdaq can be found here<br />
<a href="ftp://ftp.nasdaqtrader.com/symboldirectory/">ftp://ftp.nasdaqtrader.com/symboldirectory/</a><br />
nasdaqlisted.txt<br />
<br />
<br />
You can run the following Python code and make changes for your APIKey, SQL Server adddress. SYmbols file path.<br />
<span style="color: red; font-size: large;"><b>Python Code:</b></span><br />
import requests<br />
import json<br />
import pyodbc<br />
import datetime<br />
import time<br />
<br />
start_date =int(datetime.datetime.timestamp(datetime.datetime.strptime('07 20 2019 1:33PM', '%m %d %Y %I:%M%p')) * 1000)<br />
end_date =int(datetime.datetime.timestamp(datetime.datetime.strptime('08 02 2019 1:33PM', '%m %d %Y %I:%M%p')) * 1000)<br />
<br />
file= open(r'''<span style="color: red;"><b>C:\Stocks\Symbols.txt</b></span>''',"r")<br />
<br />
for x in file:<br />
symbol_name= x.strip() <br />
endpoint=r"https://api.tdameritrade.com/v1/marketdata/{}/pricehistory".format(symbol_name)<br />
<br />
#payload= { 'apikey':'Your API KEY',<br />
# 'periodType':'year',<br />
# 'frequencyType':'daily' <br />
# }<br />
<br />
payload= { 'apikey':'<b><span style="color: red;">Your API KEY</span></b>',<br />
'periodType':'year',<br />
'frequencyType':'daily' ,<br />
'startDate':start_date,<br />
'endDate':end_date<br />
}<br />
print(symbol_name)<br />
<br />
content= requests.get(url=endpoint,params=payload)<br />
data=content.json() <br />
<br />
sqlconn=pyodbc.connect('Driver={SQL Server};'<br />
'Server=<span style="color: red;"><b>.</b></span>;'<br />
'Database=<span style="color: red;"><b>NishDB</b></span>;'<br />
'Trusted_Connection=yes;')<br />
cursor= sqlconn.cursor()<br />
<br />
for row in data['candles']:<br />
<br />
currenttime = datetime.datetime.fromtimestamp(row['datetime']/1000).strftime('%Y-%m-%d')<br />
<br />
cursor.execute('''Insert into <b><span style="color: red;">NishDb</span></b>.dbo.Stocks_Price(Symbol,[Open],High,Low,[Close],volume,TransDate) values(?,?,?,?,?,?,?)''',<br />
(symbol_name,row['open'],row['high'],row['low'],row['close'],row['volume'],currenttime))<br />
sqlconn.commit()</div>
Nishil Jainhttp://www.blogger.com/profile/04987547678651181303noreply@blogger.com1tag:blogger.com,1999:blog-8210295288702706620.post-62110100224895263822018-11-05T01:40:00.000-08:002018-11-05T01:40:04.205-08:00SSIS Error: Execute Sql Task failed<div dir="ltr" style="text-align: left;" trbidi="on">
Error:<br />
<span style="color: red;">SQL SPLoadxxx:Error: Executing the query "EXEC SPLoadxxx ?" failed with the following error: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.</span><br />
<span style="color: red;"><br /></span>
When I executed the Stored procedure it executed perfectly.<br />
So seems SSIS was not able to execute it properly and I found out that<br />
"The Execute SQL Task" in SSIS "Parameter Mapping" had an extra parameter which was not been used by the Stored procedure.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjZ0owosT8vhuNTPOh9IIL7cv4CqYuRgwqOwj5a9vWbdM8zr_z8WxRkOEX0oMTcb8ud7YGXq54gAKIcFFBC_lgPZbXa37nezz6VnkO5x2mRHdlhImkd8UX-ev9iPPUqyw2EqYmBgukr9ei8/s1600/Skjermbilde.PNG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="282" data-original-width="737" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjZ0owosT8vhuNTPOh9IIL7cv4CqYuRgwqOwj5a9vWbdM8zr_z8WxRkOEX0oMTcb8ud7YGXq54gAKIcFFBC_lgPZbXa37nezz6VnkO5x2mRHdlhImkd8UX-ev9iPPUqyw2EqYmBgukr9ei8/s1600/Skjermbilde.PNG" /></a></div>
<br /></div>
Nishil Jainhttp://www.blogger.com/profile/04987547678651181303noreply@blogger.com0tag:blogger.com,1999:blog-8210295288702706620.post-50122450249939360602016-09-07T05:04:00.000-07:002016-09-07T05:04:03.001-07:00SSAS 2012 Tabular Cube:Attributes(columns) not visible<div dir="ltr" style="text-align: left;" trbidi="on">
Issue is where your table in the model diagram has both Measure and Attributes and you add a new column to the table in model diagram.<br />
After Cube deployment it only shows the Measures in the Client tool like Excel.<br />
It did not show the new attribute which was added to the model table later.<br />
<br />
Solution is to set the "Hidden" property on the properties windows of the newly added Attribute(Column) to "True" and click save and then change it to "False" again.<br />
<br />
And now if you deploy then the new columns will show up as dimensions.<br />
There is some bug so it doesnot take up the correct way when column added .<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiZ3O5vFdgXMn-PBEkfmULnbotdGmLcLj-OUSAVnUj5UbLUmibXe1Pu2iay8EDOYODTdzFDsGc8agdHijxmVYguCuFNBvvA6OlB67MBUGBYTE7H0E3gJ1iZbMVtZMF0zgdkbRtGnEaWw9hI/s1600/Tabular_Att_Visible.JPG" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="568" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiZ3O5vFdgXMn-PBEkfmULnbotdGmLcLj-OUSAVnUj5UbLUmibXe1Pu2iay8EDOYODTdzFDsGc8agdHijxmVYguCuFNBvvA6OlB67MBUGBYTE7H0E3gJ1iZbMVtZMF0zgdkbRtGnEaWw9hI/s640/Tabular_Att_Visible.JPG" width="640" /></a></div>
<br />
<br />
<br /></div>
Nishil Jainhttp://www.blogger.com/profile/04987547678651181303noreply@blogger.com0tag:blogger.com,1999:blog-8210295288702706620.post-89713603849003803272016-05-27T04:39:00.000-07:002016-11-15T11:50:06.829-08:00Convert Views to Tables<div dir="ltr" style="text-align: left;" trbidi="on">
Recently I wanted to create a StoredProcedure to convert all the views which read millions of records to tables in a database.<br />
<br />
The Reason being when we process a cube its always better if it reads from a simple view or a table.<br />
So the ETL(SSIS) job will execute the view which is complicated and has many joins and look ups to a table which can be easily converted to a table and it can be read into the cube.<br />
<br />
Here is code to convert all views ending with "_ToTable" to table with same name.<br />
Example : dbo.SalesOrder_ToTable view will be converted to dbo.SalesOrder so having the same schema name (.dbo) as the view .<br />
<br />
GO<br />
DECLARE @View_Name varchar(255)<br />
<br />
DECLARE MY_CURSOR CURSOR<br />
LOCAL STATIC READ_ONLY FORWARD_ONLY<br />
FOR<br />
SELECT SCHEMA_NAME(schema_id) +'.'+name AS view_name<br />
FROM sys.views where name like '%_ToTable%'<br />
<br />
OPEN MY_CURSOR<br />
FETCH NEXT FROM MY_CURSOR INTO @View_Name<br />
WHILE @@FETCH_STATUS = 0<br />
BEGIN<br />
DECLARE @Tablename_Name varchar(255)<br />
Set @Tablename_Name = Replace(@View_Name,'_ToTable','') <br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>IF OBJECT_ID(@Tablename_Name, 'U') IS NOT NULL Exec('DROP TABLE '+@Tablename_Name)<br />
<span class="Apple-tab-span" style="white-space: pre;"> </span>Exec('Select * into '+@Tablename_Name+' from '+@View_Name )<br />
FETCH NEXT FROM MY_CURSOR INTO @View_Name<br />
END<br />
CLOSE MY_CURSOR<br />
DEALLOCATE MY_CURSOR</div>
Nishil Jainhttp://www.blogger.com/profile/04987547678651181303noreply@blogger.com0tag:blogger.com,1999:blog-8210295288702706620.post-29316548870799848192016-02-29T03:47:00.001-08:002016-02-29T03:47:19.141-08:00SSAS: Dimension Parent Child Hierarchy recursive<div dir="ltr" style="text-align: left;" trbidi="on">
In the last post I showed you know to build a Parent-Child Hierarchy. This time we implement the same Parent-Child Hierarchy in a different way.
Building a Parent Child Hierarchy looks complicated but is easy if all the rules are followed correctly. I will here list down few steps which will help us building it better.<br />
<br />
Scenario: <br />
As you see below we have a simple table which you can build using SSIS and map the corresponding Parent and Child values.<br />
Portfolio: <br />
Primary Column With unique IDs.<br />
ParentPortfolio:<br />
Portfolio Id of the corresponding Parent.<br />
RollUp:<br />
Operator used while aggregrating values(Auto Sum) from Child to Parent node.<br />
You can read more on RollUps here<br />
<a href="https://msdn.microsoft.com/en-us/library/ms175417.aspx">https://msdn.microsoft.com/en-us/library/ms175417.aspx</a><br />
<br />
Example: P0120 has Parent P0200 and P0200 has Parent as P0500<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjiAHsWGodtAcKl_7UUS-LtOavFtNMwwsDrN-3RUiODr0c5rQSnNVhecsu65_iPvOpaOXgboWBKoCVUjFjWEKAdCJazgQWxzZ_Djgx6Qfo4o8OaB8noSLnV-5kNZWJGfzoQrfdGgksRKyDV/s1600/BI_Dim_Hierarchy_1.png" imageanchor="1" ><img border="0" height="587" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjiAHsWGodtAcKl_7UUS-LtOavFtNMwwsDrN-3RUiODr0c5rQSnNVhecsu65_iPvOpaOXgboWBKoCVUjFjWEKAdCJazgQWxzZ_Djgx6Qfo4o8OaB8noSLnV-5kNZWJGfzoQrfdGgksRKyDV/s640/BI_Dim_Hierarchy_1.png" width="640" /></a>
<br />
<br />
<strong>Step1:</strong><br />
You can create a New SSAS Project and create the DataSourceView.<br />
After importing the table in the DataSource View we need to set the primary key.<br />
Here Portfolio is set as Primary key.<br />
After that a link is created between Portfolio and ParentPortfolio as shown below.<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiDxNZAo_N3avruLOwlfZFBxjVSKCUCuo77UhCaNusLhO6mRxShLDaIpSd544662GPP1dOKPRbjVMM5ZdnDVArH3Wmw6QKBRmMCxFC6BxYHWq6dX37rVbMo_IrnlWhD9flDPf9Eh7J7g3F4/s1600/BI_Dim_Hierarchy_2.png" imageanchor="1" ><img border="0" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiDxNZAo_N3avruLOwlfZFBxjVSKCUCuo77UhCaNusLhO6mRxShLDaIpSd544662GPP1dOKPRbjVMM5ZdnDVArH3Wmw6QKBRmMCxFC6BxYHWq6dX37rVbMo_IrnlWhD9flDPf9Eh7J7g3F4/s400/BI_Dim_Hierarchy_2.png" width="284" /></a>
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhvG7IkqBheKT_J_ekxYUrU9EAIYCtW256ktGoNRDtPMdhkMrw-pZVEvukZ5uLE3T77En317x4ezxV5FP4I6EYgeIE2t8zV_vaoo4Mi5xrr4YHjJexp3hZDymdkj84HKg80wevW-7iqCL-_/s1600/BI_Dim_Hierarchy_3.png" imageanchor="1" ><img border="0" height="342" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhvG7IkqBheKT_J_ekxYUrU9EAIYCtW256ktGoNRDtPMdhkMrw-pZVEvukZ5uLE3T77En317x4ezxV5FP4I6EYgeIE2t8zV_vaoo4Mi5xrr4YHjJexp3hZDymdkj84HKg80wevW-7iqCL-_/s640/BI_Dim_Hierarchy_3.png" width="640" /></a>
<br />
<br />
<strong>Step2:</strong><br />
Right Click "Dimension" folder in the Project in Visual Studio and Click "New Dimension".<br />
The Dimesnion wizard opens up as shown below.<br />
Select Portfolio Column as Key Column and for display we show the ID+Name combination which is already there in the Description Column.<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgXOMQ0mXJrr6KsEfuoeuurSW2rL-SGGlT5plM9n7OpWhmi2caFfvr6ymU-ktVTXhJIzRUapYcroclCzfw99u2X7gjb1C4uL7ZWT6UsbxXkDZAdfI6rdE4hnMXjnEVcUdFNTex83sJ79Djp/s1600/BI_Dim_Hierarchy_4.png" imageanchor="1">
<img border="0" height="317" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgXOMQ0mXJrr6KsEfuoeuurSW2rL-SGGlT5plM9n7OpWhmi2caFfvr6ymU-ktVTXhJIzRUapYcroclCzfw99u2X7gjb1C4uL7ZWT6UsbxXkDZAdfI6rdE4hnMXjnEVcUdFNTex83sJ79Djp/s400/BI_Dim_Hierarchy_4.png" width="400" />
</a>
<br /><br />
<strong>Step3:</strong><br />
Select Portfolio and ParentPortfolio columns. You can add more columns like Name later on.<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhFfmNAN5XB-BMoErHiQ-s7jPh8nevMXwZujX8mXRlq0C36rsvCSFYtSXXtDVa-rp1SbypLcZsXdjltaiWXc382QrjfXolElY0MlrOvtn4ZMKqwnq6ThL5p0-v8_xH5KBBgVShuRT_4oLud/s1600/BI_Dim_Hierarchy_5.png" imageanchor="1"> <img border="0" height="512" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhFfmNAN5XB-BMoErHiQ-s7jPh8nevMXwZujX8mXRlq0C36rsvCSFYtSXXtDVa-rp1SbypLcZsXdjltaiWXc382QrjfXolElY0MlrOvtn4ZMKqwnq6ThL5p0-v8_xH5KBBgVShuRT_4oLud/s640/BI_Dim_Hierarchy_5.png" width="640" /></a>
<br />
<br />
<strong>Step4:</strong><br />
Name it "Portfolio".<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhTc6UEYVUpW4_DDN8wasoJfFIyTEsE1t_TdE2cg2HGTGqg9SU8cOQTuU-yBydxVxQewgqFdU7jFYLZnz3XXLEmr0RCAbYBnG4iT2fldkuoW054Fv-yOaSzKhNVV15XruCtmZPpB-cBsqGX/s1600/BI_Dim_Hierarchy_6.png" imageanchor="1"> <img border="0" height="505" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhTc6UEYVUpW4_DDN8wasoJfFIyTEsE1t_TdE2cg2HGTGqg9SU8cOQTuU-yBydxVxQewgqFdU7jFYLZnz3XXLEmr0RCAbYBnG4iT2fldkuoW054Fv-yOaSzKhNVV15XruCtmZPpB-cBsqGX/s640/BI_Dim_Hierarchy_6.png" width="640" /></a>
<br />
<br />
<strong>Step5:</strong><br />
Process the Dimension. The output is shown below.<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhJAP760SEVsoF3xTVYhmMvudxSZqmz3Q1Qf_6th23wIWw1L1xd27jlMpIHYFsYTlSivkSJgixWKemVi0SZ19u9G4b4-1aHcXVJ-40FeB8W6eORYE3YyNcevFyTV5MA7K57qxwXqZFjlNE2/s1600/BI_Dim_Hierarchy_7.png" imageanchor="1" > <img border="0" height="207" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhJAP760SEVsoF3xTVYhmMvudxSZqmz3Q1Qf_6th23wIWw1L1xd27jlMpIHYFsYTlSivkSJgixWKemVi0SZ19u9G4b4-1aHcXVJ-40FeB8W6eORYE3YyNcevFyTV5MA7K57qxwXqZFjlNE2/s400/BI_Dim_Hierarchy_7.png" width="400" /></a>
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjqaFxdsddzAAGy_vafpia5v9cDnU1exi8eYKCl1LJITb8idV4if2stn6VTM4_tTdsUBa6WZdE-Hwt-sele2O2yqGCny1BiOl-LREBBm-DvMHvv8QJjNKCHek8hx-i7B-eledfqlrXvKrNY/s1600/BI_Dim_Hierarchy_8.png" imageanchor="1" > <img border="0" height="528" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjqaFxdsddzAAGy_vafpia5v9cDnU1exi8eYKCl1LJITb8idV4if2stn6VTM4_tTdsUBa6WZdE-Hwt-sele2O2yqGCny1BiOl-LREBBm-DvMHvv8QJjNKCHek8hx-i7B-eledfqlrXvKrNY/s640/BI_Dim_Hierarchy_8.png" width="640" /></a>
<br />
<br />
<strong>Step6:</strong><br />
Now we need to get rid of the Unknown member and set Rollup operator.
For the Portfolio Dimension set the following Properties:<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi9U1B2nZTpNpYrNK6Hvq4bIxfnW1v5l-ldSlYBdluP8tgbnBD7P6ZzniF7br8_K6M-UCapWXWFcW7ifSKBhfvE0ObltPTbRM3AqVHDGmndXq26mPagbretrVyN_EkFb1LVg7EcXsKDZ3SX/s1600/BI_Dim_Hierarchy_9.png" imageanchor="1" > <img border="0" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi9U1B2nZTpNpYrNK6Hvq4bIxfnW1v5l-ldSlYBdluP8tgbnBD7P6ZzniF7br8_K6M-UCapWXWFcW7ifSKBhfvE0ObltPTbRM3AqVHDGmndXq26mPagbretrVyN_EkFb1LVg7EcXsKDZ3SX/s640/BI_Dim_Hierarchy_9.png" width="522" /></a>
<br /><br />
<strong>Step7:</strong><br />
For Parent Portfolio Attribute set the following Properties:<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj39CH6Bj7ckI8DBSAtBy5hN0ha0LkmgVfgj5K2jXcXelWrzMrYPrwdeLO-0OhCy2eiISIk4dEI4L9XsgQQDxBgFR8X7MwJJ2owUb921E8ewCdCa0YT0ngbGCX9e6fwh7Cq9xUbPsXJ8pHM/s1600/BI_Dim_Hierarchy_10.png" imageanchor="1" > <img border="0" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj39CH6Bj7ckI8DBSAtBy5hN0ha0LkmgVfgj5K2jXcXelWrzMrYPrwdeLO-0OhCy2eiISIk4dEI4L9XsgQQDxBgFR8X7MwJJ2owUb921E8ewCdCa0YT0ngbGCX9e6fwh7Cq9xUbPsXJ8pHM/s640/BI_Dim_Hierarchy_10.png" width="328" /></a>
<br />
<br />
<strong>Step 8:</strong><br />
Process the Dimension:<br />
The Unknown Level will disappear and all the Levels will have the Unary Rollup sign.<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEigP-DkcsNTgJkKNTYctmkfj7s1_gGKOT8qSriDT8RR7eD3lB-LuE48e0qZ206lzPObL1A4xq2lQ6rpxAfeTDU0mG6NN-dvcCREKhshM8pq73Kf89DvoQypn7HcOSmhBVJ5Kg4x-ncjxD40/s1600/BI_Dim_Hierarchy_11.png" imageanchor="1" ><img border="0" height="510" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEigP-DkcsNTgJkKNTYctmkfj7s1_gGKOT8qSriDT8RR7eD3lB-LuE48e0qZ206lzPObL1A4xq2lQ6rpxAfeTDU0mG6NN-dvcCREKhshM8pq73Kf89DvoQypn7HcOSmhBVJ5Kg4x-ncjxD40/s640/BI_Dim_Hierarchy_11.png" width="640" /></a>
<br /><br />
<strong>Step 9</strong>:<br />
Rename "Portfolio" attribute to "Portfolio Flat" and "Parent Portfolio" to Portfolio.<br />
The reason we do is because I want to name my hierarchy attribute as "Portfolio".<br />
Its helps a lot and is very user friendly for the EndUser.<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhWY6MLtRDyOKvVG5K_w4zvWHgBsRutp80BuKvWmGud_miRrBtB21p_tCqbwaHlim3_cSinM2Pi-ctXvlIjuewsZyJN_zki-2ZqpGHcj7RK07dh_kNW3wGM3sPgAHxPtFjc2M-qxox_MDSE/s1600/BI_Dim_Hierarchy_12.png" imageanchor="1" ><img border="0" height="306" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhWY6MLtRDyOKvVG5K_w4zvWHgBsRutp80BuKvWmGud_miRrBtB21p_tCqbwaHlim3_cSinM2Pi-ctXvlIjuewsZyJN_zki-2ZqpGHcj7RK07dh_kNW3wGM3sPgAHxPtFjc2M-qxox_MDSE/s640/BI_Dim_Hierarchy_12.png" width="640" /></a>
<br />
<br />
This is how it should look like:<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj0ru2KjBhiIANu8gIXLA3o_E-J0rJz0qUoCFoZtAAN6H3Xo9Yexc0sFIKbOUZi0HSlB-56CcEnADKBSlevIj866mOMg0LL7Djcmk5Bv-qD7Q-th_k0ws0kO_JfWel2CWuP0jVVBYXdTADz/s1600/BI_Dim_Hierarchy_13.png" imageanchor="1" ><img border="0" height="506" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj0ru2KjBhiIANu8gIXLA3o_E-J0rJz0qUoCFoZtAAN6H3Xo9Yexc0sFIKbOUZi0HSlB-56CcEnADKBSlevIj866mOMg0LL7Djcmk5Bv-qD7Q-th_k0ws0kO_JfWel2CWuP0jVVBYXdTADz/s640/BI_Dim_Hierarchy_13.png" width="640" /></a>
<br /></div>
Nishil Jainhttp://www.blogger.com/profile/04987547678651181303noreply@blogger.com0tag:blogger.com,1999:blog-8210295288702706620.post-18576433141570760522016-02-11T02:10:00.000-08:002016-02-29T00:11:49.635-08:00SSAS Dimension: Parent Child Hierarchy <div dir="ltr" style="text-align: left;" trbidi="on">
Building a Parent Child Hierarchy looks complicated but is easy if all the rules are followed correctly. I will here list down few steps which will help us building it better.<br />
<br />
Scenario: <br />
As you see below we have a simple table which you can build using SSIS and map the corresponding Parent and Child values.<br />
<br />
Account: <br />
Primary Column With unique IDs.<br />
ParentAccount:<br />
Account Id of the corresponding Parent.<br />
RollUp: Operator used while aggregrating values(Auto Sum) from Child to Parent node.<br />
You can read more on RollUps here<br />
<a href="https://msdn.microsoft.com/en-us/library/ms175417.aspx">https://msdn.microsoft.com/en-us/library/ms175417.aspx</a><br />
<br />
Example: 101010 has Parent 109999 and 109999 has Parent as 139999<br />
<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjE9cctyr3FivBraHA-CKnKUoswu_zzupxkDcT157FL00V0zWvSLbNXHwUwAwCQ-1QjAfYG-A-msa7TcjITy5QNXAnLJpTK_EotgNsSlnQJOqykiyoo_zQt43pcicQ7OIe7mXbloQutQbss/s1600/SSAS_Parent_Child_1.png" imageanchor="1">
<img border="0" height="547" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjE9cctyr3FivBraHA-CKnKUoswu_zzupxkDcT157FL00V0zWvSLbNXHwUwAwCQ-1QjAfYG-A-msa7TcjITy5QNXAnLJpTK_EotgNsSlnQJOqykiyoo_zQt43pcicQ7OIe7mXbloQutQbss/s640/SSAS_Parent_Child_1.png" width="640" /></a>
<br />
<br />
<strong> Step1:</strong><br />
You can create a New SSAS Project and create the DataSourceView.<br />
After importing the table in the DataSource View we start creating the Dimension.<br />
Right Click "Dimension" folder in the Project in Visual Studio and Click "New Dimension".<br />
The Dimesnion wizard opens up as shown below.<br />
<br />
Select Account Column as Key Column and for display we show the ID+Name combination which is already there in the Description Column.<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg5w2WD071i11TUeEzd3_B7MiEcF-eqyaICcQUawgPScpKrbPcj_-ToPXkyC5La2RNw15qyrcWPaTHPw8uob-dZJNyrMyi38PImJEgZ6ChPunq13qxNkRakCIt_tYrv3X0TcAVAuUOfjpjS/s1600/SSAS_Parent_Child_2.png" imageanchor="1">
<img border="0" height="516" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg5w2WD071i11TUeEzd3_B7MiEcF-eqyaICcQUawgPScpKrbPcj_-ToPXkyC5La2RNw15qyrcWPaTHPw8uob-dZJNyrMyi38PImJEgZ6ChPunq13qxNkRakCIt_tYrv3X0TcAVAuUOfjpjS/s640/SSAS_Parent_Child_2.png" width="640" /></a><br />
<br />
<strong>Step2:</strong><br />
Select Account and ParentAccount columns. You can add more columns like Name later on.<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjErrvrevKf8m9zgin6QvAF8rsZ0LMD456ZVBl4pK4MW-bpSo7tlWVCcxRmlymiefIev0MAb64bzdMf23GHwyYJ9rFR4atQVyZPL3k8YoqCIUcDZ9vYOacpHKXtBBiIc5DqtJQfIrUbY2-7/s1600/SSAS_Parent_Child_3.png" imageanchor="1"><img border="0" height="504" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjErrvrevKf8m9zgin6QvAF8rsZ0LMD456ZVBl4pK4MW-bpSo7tlWVCcxRmlymiefIev0MAb64bzdMf23GHwyYJ9rFR4atQVyZPL3k8YoqCIUcDZ9vYOacpHKXtBBiIc5DqtJQfIrUbY2-7/s640/SSAS_Parent_Child_3.png" width="640" /></a><br />
<br />
<strong>Step3:</strong><br />
Name it "Accounts".<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOz8TtsrLu8fXSphhxpCIEoiq44BSdTpD0vJzVzlXjHQwLQml8phewxOViTGDo06ZZp3tDbinD5h83pIiyvNc_hOY4zdMAj6P3Rfn0NwMNReF29SA7YCY8dv62bHSWh6RwdCgc7t0ebuho/s1600/SSAS_Parent_Child_4.png" imageanchor="1"><img border="0" height="508" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjOz8TtsrLu8fXSphhxpCIEoiq44BSdTpD0vJzVzlXjHQwLQml8phewxOViTGDo06ZZp3tDbinD5h83pIiyvNc_hOY4zdMAj6P3Rfn0NwMNReF29SA7YCY8dv62bHSWh6RwdCgc7t0ebuho/s640/SSAS_Parent_Child_4.png" width="640" /></a><br />
<br />
<strong>Step4:</strong><br />
Rename "Account" attribute to "Account Flat" and "Parent Account" to Account.<br />
So going forward Account attribute is "Parent Account".<br />
The reason we do is because I want to name my hierarchy attribute as "Account".<br />
Its a lot User friendly for the EndUser.<br />
<img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjFHXVYUNxBOk8vEKsu6KDUMNxROIueVHcvHHRihxEdvmoyah8qabAwhhmsXRNVC8MNyZriNjOM2Jk6a0f8uHbNwrVUqgoNJ2Ih2qSW27u_jfCv9KYwiaKKaaJ71TxsoZWMmm5_LtX0cJ6X/s400/SSAS_Parent_Child_5.png" /><br />
<br />
This is how it should look like:<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjojQRQOJRKi_8WV0RYWO6LtrFY0fPNceAq-77eCAanIwKr2GZQSYr4SfKpm_uStEiOPrfwsG6UXA4KxNgJ77H4KOcMebATx1cWfzwQjnq_ZDT9bvZaBYyXhM_RdNPrZNoBtnQu-P1DTlU0/s1600/SSAS_Parent_Child_6.png" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjojQRQOJRKi_8WV0RYWO6LtrFY0fPNceAq-77eCAanIwKr2GZQSYr4SfKpm_uStEiOPrfwsG6UXA4KxNgJ77H4KOcMebATx1cWfzwQjnq_ZDT9bvZaBYyXhM_RdNPrZNoBtnQu-P1DTlU0/s400/SSAS_Parent_Child_6.png" /></a><br />
<br />
<strong>Step5:</strong><br />
For the Account(orginally which was Parent Account) attribute set the following Properties:<br />
usage:Parent.<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg3QUv-JcYsa06R3O_3fLk-ArFBeldW3VtgPKljgRuQJhcDW8Nu87COGUozOji3q5qp766QBQ-6F29R31FM327GQ15KjCmZc0HiAtGRwfrd9GNslUqXhlT8N_oPF9VfWDbzFAXycGBwx0cG/s1600/SSAS_Parent_Child_7.png" imageanchor="1"><img border="0" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg3QUv-JcYsa06R3O_3fLk-ArFBeldW3VtgPKljgRuQJhcDW8Nu87COGUozOji3q5qp766QBQ-6F29R31FM327GQ15KjCmZc0HiAtGRwfrd9GNslUqXhlT8N_oPF9VfWDbzFAXycGBwx0cG/s640/SSAS_Parent_Child_7.png" width="304" /></a><br />
<br />
<strong>Step6:</strong><br />
In the attribute relationships Windows delete the first relation shown below.<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiwMW_UMJv6gJZ6pLSDeTLlJeAkY_vUsoER1Npa7gVqsPODQ2PxoT4WqH_-XrmKz9_VSENJ9riPzgk1tUbsa5XjmoQFPVnxnWCGjMLJt834SVJJz-Z_5XwT71OKr7R6Dn-CusP0uxtB2zK8/s1600/SSAS_Parent_Child_8.png" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiwMW_UMJv6gJZ6pLSDeTLlJeAkY_vUsoER1Npa7gVqsPODQ2PxoT4WqH_-XrmKz9_VSENJ9riPzgk1tUbsa5XjmoQFPVnxnWCGjMLJt834SVJJz-Z_5XwT71OKr7R6Dn-CusP0uxtB2zK8/s400/SSAS_Parent_Child_8.png" /></a><br />
<br />
This is how it should look like:<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh8_8_ZBsXe-lR6ZwOVitzmZ9EfMFV7fFg3JovW0HxI14P-w6kD91H940KP8hZIbEQC5j_aM3Pd_DIREoj29M0JcqstldNA_oHRm4KWV8Y_EnBWWkCRH-HjN1uM-mMCJcX6b6cv7pRwgr4z/s1600/SSAS_Parent_Child_9.png" imageanchor="1"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh8_8_ZBsXe-lR6ZwOVitzmZ9EfMFV7fFg3JovW0HxI14P-w6kD91H940KP8hZIbEQC5j_aM3Pd_DIREoj29M0JcqstldNA_oHRm4KWV8Y_EnBWWkCRH-HjN1uM-mMCJcX6b6cv7pRwgr4z/s400/SSAS_Parent_Child_9.png" /></a><br />
<br />
<strong>Step7:</strong><br />
Set the "Account Flat" attribute as <strong>Key Attribute</strong>.<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEilsY082txVS392wAgXNnn-VViV_wq-QDnXMIGcqi1SjUKazI38d3V2J1IdMMspeJLBUKyV38RNW5zdlAWd8laF7UUt9pCmPSD0cPnQRoBbQrMl7Bw163MQz8ZET4PpPZrsmUb5q3BYhq43/s1600/SSAS_Parent_Child_10.png" imageanchor="1"><img border="0" height="506" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEilsY082txVS392wAgXNnn-VViV_wq-QDnXMIGcqi1SjUKazI38d3V2J1IdMMspeJLBUKyV38RNW5zdlAWd8laF7UUt9pCmPSD0cPnQRoBbQrMl7Bw163MQz8ZET4PpPZrsmUb5q3BYhq43/s640/SSAS_Parent_Child_10.png" width="640" /></a><br />
<br />
<strong>Step7:</strong><br />
Process the Dimension so we see how the hierarchy looks like now.<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEilnUQrYbvhrBBTxcxaa576zty2NDbODjrjmZqeazmwwQbFKai4ttsfGphIwlPsnetLqEvjhJQdnfUey0EYlwJHVj697ch3QQioE2HMaa2Xb7dsRU8j663WhSafjCfAXmLryRtiytEyIRNK/s1600/SSAS_Parent_Child_11.png" imageanchor="1"><img border="0" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEilnUQrYbvhrBBTxcxaa576zty2NDbODjrjmZqeazmwwQbFKai4ttsfGphIwlPsnetLqEvjhJQdnfUey0EYlwJHVj697ch3QQioE2HMaa2Xb7dsRU8j663WhSafjCfAXmLryRtiytEyIRNK/s640/SSAS_Parent_Child_11.png" width="504" /></a><br />
<strong></strong><br />
<strong>Step8:</strong><br />
Set following Properties:<br />
MembersWithData:<strong>NonLeafDataHidden</strong>.<br />
UnaryOperatorColumn:<strong>Account.Rollup</strong><br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiiODpYS3GCM507dqsQPP2HYr5L5FXRWjVS-tD6NabuG35sJtebKO5QEknpbt6TV8_f0CGX1bozNkVIQQA0iJKUUbB9LsxcX7x1ODTnmkfAThH3MUie89NoB7FQ-LGEDA2e852uOcU5dx5K/s1600/SSAS_Parent_Child_12.png" imageanchor="1"><img border="0" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiiODpYS3GCM507dqsQPP2HYr5L5FXRWjVS-tD6NabuG35sJtebKO5QEknpbt6TV8_f0CGX1bozNkVIQQA0iJKUUbB9LsxcX7x1ODTnmkfAThH3MUie89NoB7FQ-LGEDA2e852uOcU5dx5K/s640/SSAS_Parent_Child_12.png" width="248" /></a><br />
<br />
<strong>Step9:</strong><br />
Process the Dimension again and you would see the following structure which you can use it in your BI reports.<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjp-Ww7R40vKoD0V93bD1vyC0blLIrVSk90ILYfvS1IZvcA7VEdyHdXl23Ci6A3KuZiYLV4V4SqJxZMA9P2zR9pYuZtqpkKN-9Pwe_gXx-tZBJUJanc8GGsSmzprmrWCdv7BxwH7GSP79We/s1600/SSAS_Parent_Child_13.png" imageanchor="1"><img border="0" height="594" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjp-Ww7R40vKoD0V93bD1vyC0blLIrVSk90ILYfvS1IZvcA7VEdyHdXl23Ci6A3KuZiYLV4V4SqJxZMA9P2zR9pYuZtqpkKN-9Pwe_gXx-tZBJUJanc8GGsSmzprmrWCdv7BxwH7GSP79We/s640/SSAS_Parent_Child_13.png" width="640" /></a><br />
<br /></div>
Nishil Jainhttp://www.blogger.com/profile/04987547678651181303noreply@blogger.com0tag:blogger.com,1999:blog-8210295288702706620.post-15197700544919673112015-06-11T04:40:00.000-07:002015-06-11T04:41:05.230-07:00Bug: SQL 2014 SP1 truncates decimal values.<div dir="ltr" style="text-align: left;" trbidi="on">
Hi ,<br />
I noticed a wierd thing with SQL Server ServicePack 1 SQLServer2014SP1-KB3058865-x64-ENU.exe from<br />
https://www.microsoft.com/en-us/download/details.aspx?id=46694<br />
<br />
After installation when I executed a <b>SQL query</b> against a <b>Linked Oracle Server</b> I got the decimal values truncated and it only returned the value before the decimal seperator..it was not rounding off either. So a value like -2035.7654 was returned as -2035<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgyK65VNBAcDW-rbrmpDNlcnmzMlANraGMDfgfKfn1P4rUBeN20TXyTYZHAXdqNWlMbt0aUTwMovfRcKevvbFmvCT0-4TgIAOUOwFnzBE45ShbcYNoNJyncCN_OUflyJXOnq5XCFkiC0vkK/s1600/Untitled.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="205" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgyK65VNBAcDW-rbrmpDNlcnmzMlANraGMDfgfKfn1P4rUBeN20TXyTYZHAXdqNWlMbt0aUTwMovfRcKevvbFmvCT0-4TgIAOUOwFnzBE45ShbcYNoNJyncCN_OUflyJXOnq5XCFkiC0vkK/s400/Untitled.png" width="400" /></a></div>
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
After uninstalling the Service Pack1 I re-ran the same SQL Query and it started returning numbers with decimal seperator -2035.7654.<br />
<br />
I hope Microsoft fixes it soon.<br />
<br /></div>
Nishil Jainhttp://www.blogger.com/profile/04987547678651181303noreply@blogger.com0tag:blogger.com,1999:blog-8210295288702706620.post-30901418013265048232012-11-27T05:45:00.000-08:002012-11-27T05:45:55.413-08:00UnExpected System Error:New DataDource in DashBoad Designer<div dir="ltr" style="text-align: left;" trbidi="on">
Hi,<br />
You might see this Error when you try to create your First Business Intelligence DashBoard using DashBoard Designer.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjvWmocYzZymlCHCA5HqIojdtzfjzKPdgPX5E8I1A92Poma5fNgQh1iwrfQEi2IYp-s3Mph1fgZ5pZ6gF_iEaGCfTwU2Z3sBxl30WpXEyydv2zaROuLXECKbgTRt0CM33c7S9kPPVlEc5-0/s1600/Error.jpg" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" height="374" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjvWmocYzZymlCHCA5HqIojdtzfjzKPdgPX5E8I1A92Poma5fNgQh1iwrfQEi2IYp-s3Mph1fgZ5pZ6gF_iEaGCfTwU2Z3sBxl30WpXEyydv2zaROuLXECKbgTRt0CM33c7S9kPPVlEc5-0/s640/Error.jpg" width="640" /></a></div>
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<div>
So how to resolve this issue.
<br />
Check all these Steps if you have performed correctly<br />
<ol style="text-align: left;">
<li>Do the following Central Administration >> Application Management >> Manage Service Application, and after that you will need to double click Secure Store Application followed by Generate New Key.</li>
<li>Also do the following Central Administration >> Application Management >> Manage Service Application >> PerformancePoint Service Application >> Manage, and then choose Update the PerformancePoint Unattended Service Account which is the first option.</li>
<li>This step has to be successful, and in the event that it fails you will have to get rid of the PerformancePoint Service Application and after that you will have recreate it.Do the following:Central Administration >> Application Management | Manage Service Application >> PerformancePoint Service Application >> Administrator, and then Add the Unattended Service Account as well as Mark Full Control.</li>
<li>PerformancePoint Services Application.Check if you see the performance point service application listed in the secure store service screen.</li>
<li>Ensure that the account running performance point services in Step3 has access to the site collection where you are trying to deploy the dashboards.</li>
<li>The credentials that you use to connect to the site using dashboard designer needs to be a site collection admin on the site collection.</li>
<li>The Account in Step3 has access to the Content Database of the BI Site Collection.</li>
</ol>
<br />
<br /></div>
</div>
Nishil Jainhttp://www.blogger.com/profile/04987547678651181303noreply@blogger.com0tag:blogger.com,1999:blog-8210295288702706620.post-89709542410721593472012-11-21T04:56:00.000-08:002015-04-26T10:22:54.072-07:00Installing Oracle 11g Client on Windows 2008 R2 64 bit to use in SSIS<div dir="ltr" style="text-align: left;" trbidi="on">
<div dir="ltr" style="text-align: left;" trbidi="on">
<b>Step 1:</b> Download the 32 Bit and 64 Bit Client from the following location.
http://www.oracle.com/technetwork/database/enterprise-edition/downloads/112010-win32soft-098987.html<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiXuFcfF2H3t9ELcw954KXYH5XIFPxXFn4BNpOP8RuTFyoEfy5eF3j0ze4xANmRZAMR8JPOSjctbsWvy8b3e_HuDB7iYp2apipxxpJmZEpg351tDg-YA_sqSqGK3OY1k9aIo6td5VWqjZES/s1600/image001.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiXuFcfF2H3t9ELcw954KXYH5XIFPxXFn4BNpOP8RuTFyoEfy5eF3j0ze4xANmRZAMR8JPOSjctbsWvy8b3e_HuDB7iYp2apipxxpJmZEpg351tDg-YA_sqSqGK3OY1k9aIo6td5VWqjZES/s640/image001.png" height="426" width="640" /></a></div>
<br />
<br />
<div class="separator" style="clear: both; text-align: left;">
<b><br /></b></div>
<div class="separator" style="clear: both; text-align: left;">
<b><br /></b></div>
<div class="separator" style="clear: both; text-align: left;">
<b>Step 2:</b> <b>Install 64Bit Version of the Client first</b> and repeat the same Steps for 32 Bit Client.
</div>
Here I am only showing screenshots for 32 Bit installation.Select Custom as shown in the ScreenShot below.<br />
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhkAAlaWeZtVOLMKk0mlmt_v9GBkLW9iu_PSR-ICoDf19mefXtYMHA1G5jGIihay6DvuOGGSL8ZFDQNtsiVJD-pnhvar7H4LROhux5ygSrOvZkxlorx0u0jV7-aXsk2Tva_ollTpRpBtiu_/s1600/image002.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhkAAlaWeZtVOLMKk0mlmt_v9GBkLW9iu_PSR-ICoDf19mefXtYMHA1G5jGIihay6DvuOGGSL8ZFDQNtsiVJD-pnhvar7H4LROhux5ygSrOvZkxlorx0u0jV7-aXsk2Tva_ollTpRpBtiu_/s640/image002.png" height="482" width="640" /></a><br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: left;">
<b>Step 3:</b> Select the language you can to use in the Client tools.</div>
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiYNhjrTpJmVjalg7VYLib8KRLJBD3AJjhZHGBLdnUb9sq4yXinYWMphDqnpGNdRDduPuSIHIzFNc51xMN8vKtktSS887GEINs77Ch_SKlz7JKptz-5FB9HMAck_RvJ-CuTw5-pEsnL8rjA/s1600/image003.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiYNhjrTpJmVjalg7VYLib8KRLJBD3AJjhZHGBLdnUb9sq4yXinYWMphDqnpGNdRDduPuSIHIzFNc51xMN8vKtktSS887GEINs77Ch_SKlz7JKptz-5FB9HMAck_RvJ-CuTw5-pEsnL8rjA/s640/image003.png" height="480" width="640" /></a><br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: left;">
<b>Step 4:</b> Specify the path where you want to Install the Oracle Client. Here I have selected <b>D:\Oracle . </b>You should use same path for 64 Bit Client Installation also. I have shown a summary for 64 Bit Client in Step 7 so its easy to understand on how to configure 64 Bit Client.</div>
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgZ2tB0NqX9rK41CIXuW4VtLfmdlo2a91gIjLgJYpI4_In0eNyF96halxb3Aqef41FdUBtMUrPaWckw5cfkUZi7E5aQG5bJAfCdS4T9vo4uDJ6uX9CkkGJzdd5Ucv3PI44vNmKw9xj56g8_/s1600/image004.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgZ2tB0NqX9rK41CIXuW4VtLfmdlo2a91gIjLgJYpI4_In0eNyF96halxb3Aqef41FdUBtMUrPaWckw5cfkUZi7E5aQG5bJAfCdS4T9vo4uDJ6uX9CkkGJzdd5Ucv3PI44vNmKw9xj56g8_/s640/image004.png" height="480" width="640" /></a><br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: left;">
<b>Step 5:</b> Select all componenets expect Oracle Scheduler Agent.</div>
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjpjosbOXyzPjt1SLjwLqB6Muu0FLPg29gX8nfpEavwPEKjZLFARAa_QsgeM6va3dUV7-V_1o7qaNxMNgVeQ6-l_g684e5A50PG7O_Nliq1oCDrp4yiNRZV1bKqtv1xejbTBVUbB1vUMMAX/s1600/image005.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjpjosbOXyzPjt1SLjwLqB6Muu0FLPg29gX8nfpEavwPEKjZLFARAa_QsgeM6va3dUV7-V_1o7qaNxMNgVeQ6-l_g684e5A50PG7O_Nliq1oCDrp4yiNRZV1bKqtv1xejbTBVUbB1vUMMAX/s640/image005.png" height="480" width="640" /></a><br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: left;">
<b>Step 6:</b> Port No by default is <b>2030</b>. Keep it the default one. I selected <b>2031 </b>because I had installed wrong version of Oracle Client before which used up the Port 2030 :) .</div>
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiCoy19OQXdAZ2Fj3gKwvUfeCUGDylB0YxjOrTX_GICJD4W9yaxgeCrWXIqeZsslTzQhGgQg_-p7DQkkYsT_3VXJn0Rmm2QCzstAD903f097M5Nt0T37yKzPo5khW4hdP6p-Gg6n1UvqZjV/s1600/image006.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiCoy19OQXdAZ2Fj3gKwvUfeCUGDylB0YxjOrTX_GICJD4W9yaxgeCrWXIqeZsslTzQhGgQg_-p7DQkkYsT_3VXJn0Rmm2QCzstAD903f097M5Nt0T37yKzPo5khW4hdP6p-Gg6n1UvqZjV/s640/image006.png" height="478" width="640" /></a><br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: left;">
<b>Step 7:</b> Here is the Summary of 64 Bit Client Installation.<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjWexCSBsR3cQLYPJTHoU3EOa3mqsO5A_989KrUG478eBuCJfCP5plZMWvvf5Xyc2dqnweqLM2TGKBc0cjl0Gr4Whlm98qGYc94sJV11FNITB3eH1aVtUlbhGlHBVw-3eelo-jD6rGmsw8t/s1600/image011.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjWexCSBsR3cQLYPJTHoU3EOa3mqsO5A_989KrUG478eBuCJfCP5plZMWvvf5Xyc2dqnweqLM2TGKBc0cjl0Gr4Whlm98qGYc94sJV11FNITB3eH1aVtUlbhGlHBVw-3eelo-jD6rGmsw8t/s640/image011.png" height="476" width="640" /></a></div>
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: left;">
<b>Step 8:</b> If you have already installed 64 Bit Client it will give an Error/Warning saying the Service already exist. Just Click Continue because this service is already created by the 64 Bit Client Installation.<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEispLbXPUsMq-KmkAp5ZtTqEdQBEzHsOtyfWLKTSS0WFPIyBO0K7QyfAtSBypgBGNX3EAq5ggEW1h3Rk5PkXhV7gLNHPalcdA1cxhej5pMKyUIHdatXUYHuS8b_4ykF0SZjcv6ME2TP61wE/s1600/image012.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEispLbXPUsMq-KmkAp5ZtTqEdQBEzHsOtyfWLKTSS0WFPIyBO0K7QyfAtSBypgBGNX3EAq5ggEW1h3Rk5PkXhV7gLNHPalcdA1cxhej5pMKyUIHdatXUYHuS8b_4ykF0SZjcv6ME2TP61wE/s640/image012.png" height="480" width="640" /></a></div>
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<div class="separator" style="clear: both; text-align: left;">
<b>Step 9: </b>For Listener just click Perform typical configuration and click Next.<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj_EB6v128QiFjdAYcBgRPFdVGAqFqErEbRBJMg9HXwPbk6FmCfkLBuwcBggs2tIysxszZNtkwhY8VnI5Y13Mu2InoWVMPz2ic3xpakbFaJn_e52WlnCTDMzqcAIG5bS0SCuG3gM9uJve7-/s1600/image007.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj_EB6v128QiFjdAYcBgRPFdVGAqFqErEbRBJMg9HXwPbk6FmCfkLBuwcBggs2tIysxszZNtkwhY8VnI5Y13Mu2InoWVMPz2ic3xpakbFaJn_e52WlnCTDMzqcAIG5bS0SCuG3gM9uJve7-/s640/image007.png" height="478" width="640" /></a></div>
<br />
<div class="separator" style="clear: both; text-align: left;">
<b>Step 10: </b>Finally it will finish and just Click End/Finish and the Oracle Client is Installed successfully.<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiszZzZgtCC2JhuTOTu9T__aqaNHHQRmQ27UXfuapSdRnW75EZjAtfpeLh8z6OBGHWDRM9zo0dzVoVhWwBWagfSGVQ0yShxFcwRrfVEeC8m6um4iC1dKT3rKMpSYmwaduQ_-X5tyaCL3AjN/s1600/image008.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiszZzZgtCC2JhuTOTu9T__aqaNHHQRmQ27UXfuapSdRnW75EZjAtfpeLh8z6OBGHWDRM9zo0dzVoVhWwBWagfSGVQ0yShxFcwRrfVEeC8m6um4iC1dKT3rKMpSYmwaduQ_-X5tyaCL3AjN/s640/image008.png" height="478" width="640" /></a></div>
<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<br />
<b>Step 11: </b><span style="color: #202020; font-family: Arial, sans-serif;"><span style="font-size: 15.428571701049805px; line-height: 19px;">To complete either the 32-bit or 64-bit Oracle client installation processes make the following registry changes:</span></span><br />
<span style="color: #202020; font-family: Arial, sans-serif;"><span style="font-size: 15.428571701049805px; line-height: 19px;">1. For both 32-bit and 64-bit installations, open up REGEDIT and make the following registry changes:</span></span><br />
<span style="color: #202020; font-family: Arial, sans-serif;"><span style="font-size: 15.428571701049805px; line-height: 19px;">HKEY_LOCAL_MACHINE\Software\Microsoft\MSDTC\MTxOCI</span></span><br />
<span style="color: #202020; font-family: Arial, sans-serif;"><span style="font-size: 15.428571701049805px; line-height: 19px;">OracleOciLib contains the value oci.dll</span></span><br />
<span style="color: #202020; font-family: Arial, sans-serif;"><span style="font-size: 15.428571701049805px; line-height: 19px;">OracleSqlLib contains the value orasql11.dll (old value is SQLLib80.dll)</span></span><br />
<span style="color: #202020; font-family: Arial, sans-serif;"><span style="font-size: 15.428571701049805px; line-height: 19px;">OracleXaLib contains the value oraclient11.dll (old value is xa80.dll)</span></span><br />
<span style="color: #202020; font-family: Arial, sans-serif;"><span style="font-size: 15.428571701049805px; line-height: 19px;">2. For 64-bit installations:</span></span><br />
<span style="color: #202020; font-family: Arial, sans-serif;"><span style="font-size: 15.428571701049805px; line-height: 19px;">HKEY_LOCAL_MACHINE\Software\Wow6432Node\Microsoft\MSDTC\MTxOCI</span></span><br />
<span style="color: #202020; font-family: Arial, sans-serif;"><span style="font-size: 15.428571701049805px; line-height: 19px;">OracleOciLib contains the value oci.dll</span></span><br />
<span style="color: #202020; font-family: Arial, sans-serif;"><span style="font-size: 15.428571701049805px; line-height: 19px;">OracleSqlLib contains the value orasql11.dll (old value is SQLLib80.dll)</span></span><br />
<span style="color: #202020; font-family: Arial, sans-serif;"><span style="font-size: 15.428571701049805px; line-height: 19px;">OracleXaLib contains the value oraclient11.dll (old value is xa80.dll)</span></span><br />
<div>
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh-vC84HVhk41ETJwIdq9LPprUnZFmTdF0Z8sItSfKS8VK6aw8Mng1WlTZOG488jP2-Nx8acKQLJYK2Cl1iQnCrqDOuo4KbOzJquu_-nhYhTYGfYy9EXNxCEN8vHBB_5UaON4jcf8P9W7PE/s1600/image014.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh-vC84HVhk41ETJwIdq9LPprUnZFmTdF0Z8sItSfKS8VK6aw8Mng1WlTZOG488jP2-Nx8acKQLJYK2Cl1iQnCrqDOuo4KbOzJquu_-nhYhTYGfYy9EXNxCEN8vHBB_5UaON4jcf8P9W7PE/s640/image014.png" height="482" width="640" /></a></div>
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
Step 12: <span lang="EN-US" style="font-family: Verdana, sans-serif; font-size: 10pt;">Configure the Oracle clients (both 32 and 64 bits) by
editing the files </span><span style="font-family: Verdana, sans-serif; font-size: 10pt;"><a href="http://www.orafaq.com/wiki/Tnsnames.ora"><span lang="EN-US" style="color: #02469b; mso-ansi-language: EN-US;">tnsnames.ora</span></a></span><span lang="EN-US" style="font-family: Verdana, sans-serif; font-size: 10pt;"> and </span><span style="font-family: Verdana, sans-serif; font-size: 10pt;"><a href="http://www.orafaq.com/wiki/Sqlnet.ora"><span lang="EN-US" style="color: #02469b; mso-ansi-language: EN-US;">sqlnet.ora</span></a></span><span lang="EN-US" style="font-family: Verdana, sans-serif; font-size: 10pt;">. </span></div>
<div class="separator" style="clear: both; text-align: left;">
<span lang="EN-US" style="font-family: Verdana, sans-serif; font-size: 10pt;"><br /></span></div>
<div class="separator" style="clear: both; text-align: left;">
<span lang="EN-US" style="font-family: Verdana, sans-serif; font-size: 10pt;">Both files should be placed in the following Folders</span></div>
<div class="separator" style="clear: both; text-align: left;">
<span lang="EN-US" style="font-family: Verdana, sans-serif; font-size: 10pt;"></span></div>
<div style="margin: 0cm 0cm 0.0001pt;">
<span lang="EN-US" style="font-family: Calibri, sans-serif; font-size: 11pt;">D:\oracle\product\11.1.0\client_64\network\admin<o:p></o:p></span></div>
<div style="margin: 0cm 0cm 0.0001pt;">
<span lang="EN-US" style="font-family: Calibri, sans-serif; font-size: 11pt;">D:\oracle\product\11.1.0\client_32\network\admin<o:p></o:p></span></div>
<div>
<br /></div>
<div style="margin: 0cm 0cm 0.0001pt;">
<span style="font-family: Calibri, sans-serif;"><span style="font-size: 14.857142448425293px;">Tnsnames.ora file should contain similar connection string as follow</span></span></div>
<div style="font-family: Verdana, sans-serif; font-size: 10pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">AB10P =<o:p></o:p></span></div>
<div style="font-family: Verdana, sans-serif; font-size: 10pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;"> </span><span lang="EN-US" style="font-family: "Calibri","sans-serif"; font-size: 11.0pt; mso-ansi-language: EN-US;">(DESCRIPTION =<o:p></o:p></span></div>
<div style="font-family: Verdana, sans-serif; font-size: 10pt; margin: 0cm 0cm 0.0001pt;">
<span lang="EN-US" style="font-family: "Calibri","sans-serif"; font-size: 11.0pt; mso-ansi-language: EN-US;">
(ADDRESS_LIST =<o:p></o:p></span></div>
<div style="font-family: Verdana, sans-serif; font-size: 10pt; margin: 0cm 0cm 0.0001pt;">
<span lang="EN-US" style="font-family: "Calibri","sans-serif"; font-size: 11.0pt; mso-ansi-language: EN-US;">
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.190.12)(PORT = 1527))<o:p></o:p></span></div>
<div style="font-family: Verdana, sans-serif; font-size: 10pt; margin: 0cm 0cm 0.0001pt;">
<span lang="EN-US" style="font-family: "Calibri","sans-serif"; font-size: 11.0pt; mso-ansi-language: EN-US;">
)<o:p></o:p></span></div>
<div style="font-family: Verdana, sans-serif; font-size: 10pt; margin: 0cm 0cm 0.0001pt;">
<span lang="EN-US" style="font-family: "Calibri","sans-serif"; font-size: 11.0pt; mso-ansi-language: EN-US;">
(CONNECT_DATA =<o:p></o:p></span></div>
<div style="font-family: Verdana, sans-serif; font-size: 10pt; margin: 0cm 0cm 0.0001pt;">
<span lang="EN-US" style="font-family: "Calibri","sans-serif"; font-size: 11.0pt; mso-ansi-language: EN-US;">
(SERVICE_NAME = AB10P .test.com)<o:p></o:p></span></div>
<div style="font-family: Verdana, sans-serif; font-size: 10pt; margin: 0cm 0cm 0.0001pt;">
<span lang="EN-US" style="font-family: "Calibri","sans-serif"; font-size: 11.0pt; mso-ansi-language: EN-US;">
</span><span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;">)<o:p></o:p></span></div>
<div style="font-family: Verdana, sans-serif; font-size: 10pt; margin: 0cm 0cm 0.0001pt;">
<span style="font-family: "Calibri","sans-serif"; font-size: 11.0pt;"> )<o:p></o:p></span><br />
<span style="font-family: "Calibri","sans-serif"; font-size: 11pt;"><br /></span>
<span style="font-family: "Calibri","sans-serif"; font-size: 11pt;"><b>Step 13:</b> </span><span style="background-color: white; font-size: 10pt;">Use the "</span><b style="background-color: white; font-size: 10pt;">Oracle provider for OLE DB</b><span style="background-color: white; font-size: 10pt;">" from SSIS,
don't use the "Microsoft Provider for Oracle" because a 64 bit
version of it does not exist.</span><br />
<br />
<div style="font-size: 10pt; margin: 0cm 0cm 0.0001pt;">
<span lang="EN-US" style="font-family: "Calibri","sans-serif"; font-size: 11pt;">Sample Connectionstring : </span></div>
<div style="font-size: 10pt; margin: 0cm 0cm 0.0001pt;">
<span lang="EN-US" style="font-family: "Calibri","sans-serif"; font-size: 11pt;">Provider=OraOLEDB.Oracle;Password=testPassword;Persist Security Info=True;User ID=User1;Data Source=<o:p></o:p></span><span style="font-family: Calibri, sans-serif; font-size: 14.857142448425293px;">AB10P</span></div>
<div style="font-size: 10pt; margin: 0cm 0cm 0.0001pt;">
<span lang="EN-US" style="font-family: "Calibri","sans-serif"; font-size: 11pt;">Tableprefix: Test1"."<o:p></o:p></span></div>
<div style="font-size: 10pt; margin: 0cm 0cm 0.0001pt;">
<span lang="EN-US" style="font-family: "Calibri","sans-serif"; font-size: 11pt;">Provider: Oracle Provider for OLE DB</span></div>
</div>
</div>
Nishil Jainhttp://www.blogger.com/profile/04987547678651181303noreply@blogger.com49Nydalen, Oslo, Norway59.95232 10.7691459.9503325 10.7642045 59.9543075 10.7740755