How to Import Excel in SQL for doing ETL Operation

Today I got request from client, they want to import Excel file into SQL Server by using procedure, because they want to perform incremental loading on daily basis and ETL on it.

When I goggle out, didn’t find any appropriate blog, videos or any document which are relevant to it. So I thought to write about it.

For import the file we have to create the server which is dedicate to the particular file, or we can use SQL query also for getting the data.

By creating the Server :

Step 1: You have to install OLA DB and have to configure it

Link for installation: https://www.microsoft.com/en-us/download/details.aspx?id=13255

Step 2: You have to configure the dedicated server inside the sql server which will call the file

       Step 2.1: Go to Object Explore

       Step 2.2: Go to sever object

                                         

       Step 2.3: Click on the link server

                     

Step 2.4: Select the new linked server, fill the details

·         In the left pane, select the General page, and then follow these steps:

·         In the first text box, type any name for the linked server.

·         Select the Other data source option.

·         In the Provider list, click Microsoft Jet 4.0 OLE DB Provider.

·         In the Product name box, type Excel for the name of the OLE DB data source.

·         In the Data source box, type the full path and file name of the Excel file.

·         In the Provider string box, type Excel 8.0 for an Excel 2002, Excel 2000, or Excel 97 workbook.

·         Click OK to create the new linked server.

Note : In SQL Server Management Studio, you cannot expand the new linked server name to view the list of objects that the server contains.

For Getting the data : SELECT name,age FROM [OLE DB 17]...[Sheet1$]

Here OLE DB 17 is the server name and Sheet1$ is the name of the excel sheet we want to import

By Using the SQL Query:

Step 1: Then we have to activate the sp_CONFIGURE it is stored procedure in it we have to pass the argument 'show advanced options',1 for activating the advance option to call the ad-hoc distribution query

Step 2: Again we have to call the sp_CONFIGURE and this time we will pass the argument 'Ad Hoc Distributed Queries', 1 for activating the query passing.

Step 3:  Then run the script: SELECT * INTO [dbo].[tmpTable1] FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0', 'Excel 8.0;Database=C:\Users \test.xlsx;','SELECT * FROM [Sheet1$]')

Then we can use: select * from dbo.tmptable1 to get data

Using procedure and temporary table we can perform various operation, it is very use for BI and Analytics process

Welcome for the feedback and Thanks for viewing.

Kindly suggest more topics

👍👍👍👱👱👱🌝🌝🌝

WebLogic Admin Server Startup Failed

 Oracle WebLogic Admin Server startup failed

Last Week when I’m starting server, stuck by an issue that database credential password failed.

This is the new issue for me, so I goggle it but there was also not satisfying blog related to it thus I decided to write about it.

Issue:  When you start weblogic.cmd file for starting server then oracle.security.jps. service.policystore.PolicyStoreException : cannot connect with you database “URL”.

This Issue arises because “passwords for few schemas related to Admin Server Repository have been expired”.

For cross checking you can run query:” SELECT username, account_status FROM dba_users”.

Solutions:  

Now the problem is how we can get which schema's name which is related to WebLogic Server, if you are new to WebLogic server then it will difficult to understand the which schema related to weblogic,For getting schemas name we have to run the config.cmd file in %ORACLEHOME%.When we execute the file then chose update for existing server go to JDBC Test File there we can Schema name which password is expired.

Now we have the Schemas Name, just we have to alter the password and provide the unlimited validity for it.

Note:  You have the System Admin Privilege for performing these operations.

• Query for changing the Password of the Schema: Alter user_name identified by password 
• Query for providing the unlimited validity for password: ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;

Now when you perform check operations then

Welcome your valuable feedback and suggestions

Thanks for viewing, Hope it will helpful for you.

Oracle BI Server Online Repository Creation

                           How to create an online repository for Oracle BI Server

Repository Is use for the creating the relationship between the Physical Data Base and the BI Server for doing functionality.
It is divided into three Section

1. Physical layer.
2. Business Model and Mapping Layer.
3. Presentation Layer.

For making Repository we have to creates the OBI Server Data types.

Steps For Creating OBI Server Data Source.

Step 1:

 Open the Control Panel and go to Administrative Tools.

Step 2:

Then Select ODBC Source Type According to your System Configuration.

Step 3:

Then Select System DSN(Data Source Name) and go to Add

• DSN :- A data source name (DSN) is a data structure that contains the information about a specific database that an Open Database Connectivity ( ODBC ) driver needs in order to connect to it 

Step 4:

Select Oracle BI Server  for the Data Source and select Finish.

Step 5:

Enter a Name for the Your Data Source and also write Description about it and Click on next

Step 6:

Enter the User and Corresponding Password that we have set at the time OBI Installation.

• Select Next and then click on Finish.

Note: Port Number Is 9514.

Now we did Configuration of the BI Server DNS.

Part 2

Then we have to create the Data Source For BI and make it communicate with the Physical Data Base

As I told Above Repository Divided into 3 parts, here we describe also with creation

We start with Physical layer

• It defines the data Source to which Oracle Bi Server communicate with the Physical Data Base.
• By Importing (Recommended) and designing manually data source table.

Step 1

Go to the BI installed folder and go to user_projects and then after domains follow by bi-bitools-bin-admintools.cmd

Step 2

Select the File and then select the open and click on the Online.

Enter the user Name and Password  same as we enter in the Data source creation.

Step 3

Right in the Physical Layer Part and select the New Database Creation.

Step 4

Enter a name For you data base and Select the type of your data base.

Step 5

Right click on the Your Database and select the New Object  follow by Connection Pool

Step 6

Enter the Poll Name and then select the interface for the "Oracle 12c- Oracle 10g/11g".

Step 7

Then click on the Connection Poll and the That we have made and select import metadata.

Step 8

Verify the data source and Name (TNS Name) with the user name and password.

Step 9

Select the Sources that you want to import

Step 10

Select the user or the table that we want to explore and import 

Step 11

Right click on the BBM and select the Create the New Business Model

Step 12

Drag and drop the Physical layer data base in the BBM.

Do same Step in Presentation Layer

Now your Data Model is Created for BI server
You can Access in BI Publisher as well as BI Analytics.

------------------------------Welcome For Comment and Suggestion.------------------------------------------

Multitenant Architecture and Pluggable Database

       Conceptual View of Multitenant Architecture and Pluggable Database 

Challenges with Database Consolidation till 11g 

Till 11g we have to create the multiple data base in the one environment than we have to define the schema and individual memory structure (SGA- System Global Area, it is a group of memory areas that is dedicated to oracle), if we can manage to this things then we can achieve the great consolidation density of hosting multiple data base in one system or server. 

And it gives availability to manage many-as-one, but this one the big drawback of the system, because all the system patched in the single database. 

But the problem arises with that is we have to move and do some changes with the core system then it will affect the entire database, because here we are following “Many-to-One” functionality of the data base. 

When we have to create a Database, we have s database file with background Process that manage the functionality with Memory (SGA). And whenever we have to create database we have to replicate the same database architecture. 

The resources are easily consumed. 

• For consolidate hundreds of database in one machine we need PDB. 

Multitenant Architectures 

• So for Overcoming to this database Oracle introduce Pluggable database which  follow Multitenant architecture. 
• We can create number of database on the same sort of the memory and Background process. Pluggable database fully Self Contained Functional Database. 
• Oracle Introduce one more new feature that is call root where we plug the Pluggable database and this root define upon the Container Database. 
• So this Architectural interdependence y CDB+ROOT+PDB are called Multitenant database. 
• Here we can move PDB from one CDB to another CDB this movement is calling Metadata Movement. 
• We can also unplug a PDB from on premises and plug it into Cloud (or vice versa). 
• Query for Unplug: alter pluggable database HCM unplug into ‘HCM.xml location URL’. 
• Query for Plug: create pluggable database MY_pdb using ‘Hcm.xml URL where we unplug the database’. 

What are Goal that achieved by Pluggable Database 

• Earlier before 12c an empty database contains oracle meta dictionaries that stored the information of the row and column mapping by oracle database and relational database concepts 
• Above the meta data , we create user data and it mingled with oracle metadata row, this make plug ability difficult, if we want to separate then we have to deal with row by row. 
• Now with 12c Plugability feature we have Multitenant Architecture that defines Oracle metadata and user metadata separately. 
• But it not totally separate, there is some pointer in the database that link with oracle metadata dictionaries, this concept is call Horizontal Metadata Partition through this definition oracle achieve In-Data Virtualization.    
• Multitenant Scalability : We have to increase only small amount of memory with increasing the Pluggable Database. 
• Multitenant Dynamics: Oracle design the Database like that Pluggable database only look at the SGA that associates to it and process took care of only that pluggable database that plug into it. 
• Capabilities Enable: Manage by many to one, Backup database as one, recovery at pluggable database level. We have to Configure Data Guard we can configure databases for production and standby database, when a new database introduce in it then it will take benefits of it. 
• Multitenant Upgrades: when we have to upgrade the when we set new set of binaries then we just have to upgrade the Container Database , all the Pluggable Database plug to it upgrade in one suites. 
• Multitenant Patching: Multitenant Patching is the functionality where we can do partition between PDB for up-gradations. 
• Multitenant Cluster: When can Specify the specific node open for specific services of the specific PDB. This allow partition of SGA virtually according to cluster.
• Multitenant Cluster: When can Specify the specific node open for specific services of the specific PDB. This allow partition of SGA virtually according to cluster.
• Multitenant Snapshot Cloning :  The coping or making clone of the database from one tenant to another this is call cloning, Same Concepts used by Snapshot cloning but the functionality are little different. 
• It works on the concepts of data Block and Header. 
• In cloning we make clone of the file of the database, file is the collection of the data blocks and headers. 
• In snapshot cloning, it makes clone of header instead of making clone of whole blocks,  
• If we have to make chancing in some file then we have to only copy that file. 
• This one is known as copy on right. 

Multitenant Architecture Advantages 

• Single self-contained PDB for each application. 
• Applications run unchanged. 
• Rapid provisioning (via clones). 
• Portability (via pluggability). 
• Shared memory and background processes. 
• More application per Server. (Scalability) 
• Common Operation Performed at CDS level. 
• Manage many as one (upgrades, backup) 
• Controlling also when required. 

Managing Shared Resources between PDBs 

• Using resources Manager we can control 
• CPU 
• Exadata I/O 
• Sessions 
• Parallel executive servers. 
• Configure a policy that control how resources are utilized. 
• The model is “industry standard” based on two notions; 
• A number of shares is allocated to each PDB. 
• A ‘cap’ (maximum utilization limits) may be applied to each PDB. 
• A CDB resources plan uses ‘shares’ to specify how CPU is distributed between PDBs. 

Thanks

Welcome For Feedback, Suggestion and Comments.

Next Blog About Creation Of Pluggable Database Through SQL Server