It is very interesting to see a consistent growth of Microsoft in BI and Analytics environment. Lets see Gartner’s Magic Quadrant BI and Analytics graph so several years.
We will revisit next year to see what-else has changed. By the way, what is your favorite tool.
We may come across this situation when we get a file with out extension or given incorrectly. But, we have options in some operating system that will search and match the program associated with the unknown file type.
I had situation recently, a file with incorrect extension. I was trying to open the file in text edition (binary mode) to identify the file type manually. But, I could not do it simple reading the file content.
Finally found a tool that does what i was trying to do manually. This is light weight tool with easy to update external data dictionary for file finger print matching data.
Utility software details:
is free for personal / non commercial use.
TrID is free for personal / non commercial use.
|Win32||TrID v2.24, 47KB ZIP – (PGP sig)|
|Linux/x86||TrID v2.24, 357KB ZIP – (PGP sig)|
|Linux/x86-64||TrID v2.24, 421KB ZIP – (PGP sig)|
|TrIDUpdate v1.00, 1KB ZIP (Python 2.7.x required)|
|TrIDDefs.TRD package, 982KB ZIP (7647 file types, 15/11/16)|
This is a good news for the SQL Server community for this year. Microsoft has released SQL Server 2016 on June 1st 2016.
You need to sign-up to download the evaluation edition from this web page.
There are many exciting features in this version. Now, SQL Server has moved to the top of the database management servers ladder.
Database is a repository for data of various formats. In the past, we use text files to store huge data and technology revolution has changed everything for what we store and How we store data. Big data is a good example. It happened in different forms as punch card, file system, relational database, graph db, NoSQL, NewSQL and etc.
But, in this blog post I am going to show a simple example for storing (insert) and altering (update) a binary image file stored in a table.
Create a Table
CREATE TABLE [dbo].[Images](
ImageID INT IDENTITY(1,1),
ImageDesc VARCHAR(20) NOT NULL,
[ImageData] VARBINARY(MAX) NOT NULL
) ON [PRIMARY]
Sample Image file – SQL2016.jpg
Insert a record with a Image file
SELECT ‘SQL2016′ AS ImageDesc, * FROM OPENROWSET(BULK N’C:\Test\SQL2016.jpg’, SINGLE_BLOB) AS Img
Update an existing record with Image file
SET ImageDesc = ‘SQLServer’
,ImageData = BulkColumn from Openrowset( Bulk ‘C:\Test\SQLServer.jpg’, Single_Blob) as Img
WHERE ImageID = 1
This blog post is for code reference. You can use this code with not restriction.
Ralph Kimball and the Kimball Group have continued to refine their methods and techniques based on thousands of hours of consulting and training. This Remastered Collection of The Kimball Group Reader represents their final body of knowledge, and is nothing less than a vital reference for anyone involved in the field.
- Learn data warehousing and business intelligence from the field’s pioneers
- Get up to date on best practices and essential design tips
- Gain valuable knowledge on every stage of the project lifecycle
- Dig into the Kimball Group methodology with hands-on guidance
Download this book
In the previous post, I have explained the steps to connect to SQL Server with ODBC DSN.
In this blog post, I am going to connect to SQL Server without ODBC DSN. But, will be using driver name instead.
Step 1: RODBC must be installed in R. Please read (Connect to SQL Server in R for detail)
Step 2: Load the RODBC library
Step 3:Connecting to SQL Server with Driver name
MSConnection <- odbcDriverConnect(“DRIVER=SQL SERVER;SERVER=.;Trusted_Connection=Yes”)
This connection point to the default database initially. But, we can run USE statement to change the database.
sqlQuery(MSConnection, “USE test”)
Step 4: Read the table data to a variable
mydata <- sqlQuery(MSConnection, “Select * from dates”)
Step 5: Display data by just typing the variable name in R command environment
Step 6: Close the connection
R is an OPEN SOURCE software environment for statistical computing and graphics. It is easy to use and create valuable data visualization in a minute. Learn more about R.
This blog post is to explain the steps to connect to SQL Server database in R and display simple graphics.
Over all steps
- Create ODBC Data Source
- Setup R environment to connect to SQL Server
- Query the SQL table, display data and graphics
Step 1: Create ODBC Data Source:
Goto, Control Panel -> Administrative Tools -> Data Sources (ODBC)
Click Add button
select SQL Server Native Client and click Finish
Give data source name and SQL Server name to connect and Click Next
Select an appropriate SQL Server authentication option and give valid credential. In this example, we are using Integrated Windows Authentication. Click next
Select the database to work with and modify the application intent option to Read-only. These settings are for this exercise only. We may use default setting and click next.
Click finish and TEST connection
Step 2: Setup R environment to connect to SQL Server. Please install R software.
Install RODBC package to R:
Load the RODBC library to current environment to use:
Connect to SQL Server using ODBC connection:
- wrpt <-odbcConnect(“weatherdata”)
We have just connected to SQL Server and the R connection variable name is wrpt. We can use this variable to query a table and more.
Step 3: Query the SQL table to display data and graphics
This statement loads the entire table “tblTemp” in to the variable weather.
weather <- sqlFetch(wrpt, “tblTemp”)
This statement displays the entire table
This statement displays the summary of the data-set
This statement displays a graph with daily max temperature for a month
Thanks for reading this post.