Convert unix time to normal time from while copying data from mysql data files to sql server 2005

Follow the steps mention in posting,

“Sunday, March 23, 2008
Copy data from mysql data files to sql server 2005 “

Until “Again next and write sql query to get data. (table by table)”

This step when you select data you can use “FROM_UNIXTIME” and convert unix time to normal time.

For Eg:

Without considering the unix time here is the SQL script,

select`id`,`time`,`userid`,`ip`,`course`,`module`,`cmid`,`action`,`url`,`info`

from mdl_log_sem1

Considering the unix time here is the SQL script,

select `id`,FROM_UNIXTIME(`time`),`userid`,`ip`,`course`,`module`,`cmid` ,`action`,`url`,`info`

from mdl_log_sem1

Here simply use as FROM_UNIXTIME(`time`)

time- name of the field

then other steps are same as in the previous posting.

convert unix_timestamp to normal date value

mysql>

SELECT FROM_UNIXTIME(875996580);

Result=>
FROM_UNIXTIME(875996580)
1997-10-04 13:23:00

SELECT FROM_UNIXTIME(875996580) + 0;

Result=>
FROM_UNIXTIME(875996580) + 0
19971004132300.000000

SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(),'%Y %D %M %h:%i:%s %x');

Result=>
FROM_UNIXTIME(UNIX_TIMESTAMP(),'%Y %D %M %h:%i:%s %x')
2007 22nd July 07:46:11 2007

Copy data from mysql data files to sql server 2005

Download “mysql-connector-odbc”

Then open ODBC data source administrator in Administrative Tools.

Select user DNS tab and Add button.

Then select mysql odbc driver and finish.

Then,

DNS as export

Server as localhost if it’s same machine

User as user name of mysql server and password of it.

Then you can select the appropriate database from the drop down.

You will successfully create connection with mysql database.

Then open sql server 2005 and create new database.

Right click on that select tasks and then select import data.

Click next then select data source as .Net framework data provider for odbc

Type Dsn as export and driver as myodbc

Click next and select destination database(default it will select no need to do any changes simply click next)

Again next and write sql query to get data. (table by table)

Like “select * from grades”

Then next, next and next and finished.

You will see that particular table will copy to sql server database..

BI applications

BI applications include the activities of decision support systems, query and reporting, online analytical processing (OLAP), statistical analysis, forecasting, and data mining.

Business intelligence applications can be:

• Mission-critical and integral to an enterprise's operations or occasional to meet a special requirement
• Enterprise-wide or local to one division, department, or project
• Centrally initiated or driven by user demand

BI with Microsoft products

Introduction

Microsoft® SQL Server™ 2005 is a complete business intelligence (BI) platform that provides the features, tools, and functionality to build both classic and innovative kinds of analytical applications. This paper provides an introduction to the tools that you will use to build an analytical application, and highlights new functionality that makes it easier than ever to build and manage complex BI systems.

The SQL Server 2005 Business Intelligence toolset delivers end-to-end BI application integration,

Design:
Business Intelligence Development Studio is the first integrated development environment designed for the business intelligence system developer. Built on Visual Studio® 2005, the Business Intelligence Development Studio delivers a rich, integrated, professional development platform for BI system developers. Debugging, source control, and script and code development are available for all components of the BI application.


Integrate:
Integration Services has been rewritten to perform complex data integration, transformation, and synthesis at high speed for very large data volumes. The Business Intelligence Development Studio makes building and debugging packages positively fun. Integration Services, Analysis Services, and Reporting Services work together to present a seamless view of data from heterogeneous sources.


Store:
SQL Server 2005 blurs the lines between relational and multidimensional databases. You can store data in the relational database, in the multidimensional database, or use the new Proactive Cache feature to get the best of both worlds.


Analyze:
Data Mining in SQL Server has always been easy to use. Now it's even better with the addition of important new algorithms, including Association Rules, Time Series, Regression Trees, Sequence Clustering, Neural Nets, and Naïve Bayes. Important new analytical capabilities have been added to Analysis Services cubes as well: Key Performance Indicator framework, MDX scripts, and other built-in advanced business analytics. The Reporting Services report delivery and management framework enables easy distribution of complex analytics to the widest possible audience.


Report:
Reporting Services extends the Microsoft Business Intelligence platform to reach the business user who needs to consume the analysis. Reporting Services is an enterprise managed reporting environment, embedded and managed using web services. Reports can be personalized and delivered in a variety of formats and with a range of interactivity and printing options. Complex analyses can reach a broad audience through the distribution of reports as a data source for downstream business intelligence. New in SQL Server 2005 is Report Builder. Report Builder provides for self service ad hoc reporting by the end user. Ad hoc query and analysis tools from Microsoft and their partners will continue to be a popular choice for accessing data in Analysis Services and relational databases.


Manage:
The SQL Server Management Studio integrates the management of all SQL Server 2005 components. Through Management Studio, BI platform components gain enhanced scalability, reliability, availability, and programmability. These enhancements provide significant benefits to the business intelligence practitioner.

Business Intelligence and Data Warehousing in SQL Server 2005

Microsoft^® SQL Server™ 2005 is a complete business intelligence (BI) platform that provides the features, tools, and functionality to build both classic and innovative kinds of analytical applications. This paper provides an introduction to the tools that you will use to build an analytical application, and highlights new functionality that makes it easier than ever to build and manage complex BI systems.

The following table presents an overview of the components of a business intelligence system, and the corresponding Microsoft SQL Server 2000 and SQL Server 2005 components.

Component	SQL Server 2000	SQL Server 2005
Extract, transformation, and load	Data Transformation Services (DTS)	SQL Server 2005 Integration Services
Relational data warehouse	SQL Server 2000 relational database	SQL Server 2005 relational database
Multidimensional database	SQL Server 2000 Analysis Services	SQL Server 2005 Analysis Services
Data mining	SQL Server 2000 Analysis Services	SQL Server 2005 Analysis Services
Managed reporting	SQL Server 2000 Reporting Services	SQL Server 2005 Reporting Services
Ad hoc reporting		SQL Server 2005 Reporting Services
Ad hoc query and analysis	Microsoft Office products (Excel, Office Web Components, Data Analyzer, SharePoint Portal)	Microsoft Office products (Excel, Office Web Components, Data Analyzer, SharePoint Portal)
Database development tools	SQL Server 2000 Enterprise Manager, Analysis Manager, Query Analyzer, various other	SQL Server 2005 Business Intelligence Development Studio (New!)
Database management tools	Enterprise Manager, Analysis Manager	SQL Server 2005 SQL Server Management Studio (New!)

Two components are new for SQL Server 2005: SQL Server Management Studio and SQL Server Business Intelligence Development Studio. The other primary BI components – Integration Services, Analysis Services OLAP, Analysis Services Data Mining, and Reporting Services – are substantially different and improved in SQL Server 2005. The SQL Server 2005 relational database contains several significant new features. Although the Microsoft Office query and portal tools are not part of SQL Server, the current releases will continue to work with SQL Server 2005. The BI functionality in Microsoft Office will evolve with the Office product release cycle.

Business Intelligence Applications

BI systems beneficiaries include a wider and wider group of users starting from specialists in controlling, financial reporting and finance, through salespeople, up to members of the board. Sectors that use BI systems most frequently include trading companies, insurance companies, banks and a financial sector, telecommunications and manufacturing companies,

Retail industry

• Forecasting. Using scanning data to forecast demand and based on the forecast, to define inventory requirements more accurately
• Ordering and replenishment. Using information to make faster decisions about items to order and to determine optimum quantities
• Marketing. Providing analyzes of customer transactions (what is selling, who is buying)
• Merchandising. Defining the right merchandise for the market at any point in time, planning store level, refine inventory
• Distribution and logistics. Helping distribution centers manage increased volumes. Can use advance shipment information to schedule and consolidate inbound and outbound freight
• Transportation management. Developing optimal load consolidation plans and routing schedules
• Inventory planning. Helping identify the inventory needed level, ensure a given grade of service

Insurance

• Claims and premium analysis. The ability to analyze detailed claims and premium history by product, policy, claim type, and other specifics
• Customer analysis. Analyze client needs and product usage patterns, develop marketing programs on client characteristics, conduct risk analysis, improving client service
• Risk analysis. Identify high-risk market segments and opportunities in specific segments, relate market segments, reduce frequency of claims

Banking, finance and securities

• Customer profitability analysis. Determinate the overall profitability of individual customer, current and long term, provide the basis for high-profit sales and relationship banking, maximize sales to high-value customers, reduce costs to low-value customers, provide the means to maximize profitability of new products and services
• Credit management. Establish patterns of credit problem progression by customers class and type, warn customers to avoid credit problems, to manage credit limits, evaluate of the bank’s credit portfolio, reduce credit losses
• Branch sales. Improve customer service and account selling, facilitate cross selling, improve customer support, strengthen customer loyalty

Telecommunications

• Customer profiling and segmentation. Determine high-profit product profiles and customer segments, provide detailed, integrated customer profiles, develop of individualized frequent-caller programs, determine future customer needs
• Customer demand forecasting. Forecast future product needs or service activity, provide basis for churn analysis and control for improving customer retention

Manufacturing industry

• Sales. Provide analyzes of customer-specific transaction data
• Forecasting. Forecast demand, define inventory requirements
• Ordering and replenishment. Order optimum quantities of items
• Purchasing. Helping distribution centers manage increased volumes.
• Distribution and logistics. Can use advance shipment information to schedule and consolidate inbound and outbound freight
• Transportation management. Developing optimal load consolidation plans and routing schedules
• Inventory planning. Identify the inventory level needed, ensure a given grade of service