Access database from SQL 2005/64

Moved:
http://information-management-enabled.blogspot.com/2005/11/access-database-from-sql-200564.html

OLAP defined

What is this OLAP thing?
Almost a year ago, I spent an entire week on the ultimate Business Intelligence course. I have taken and passed the corresponding brainbench tests (Data warehousing and OLAP). Still I must admit that I am not confident about OLAP (especially MDX). Having lots of stuff boiling in my sub-conscious mind, I will get there eventually (maybe I am a bit slow, he he). And my guess is that any BI specialist confident about their understanding of leveraging OLAP technology, has stopped developing.


googling
Wikipedia (initially) states
“OLAP is an acronym for online analytical processing. It is an approach to quickly provide the answer to complex analytical queries.”

google define gives eg.

www.georgetown.edu/uis/ia/dw/GLOSSARY0816.html
On-line retrieval and analysis of data to reveal business trends and statistics not directly visible in the data directly retrieved from a data warehouse. Also known as multidimensional analysis

www.intoweb.co.za/it-terms.php
On-line Analytical Processing. Information analysis that pass the FASMI test: Fast Analysis of Multidimensional Information.


Aha!
FASMI: Fast Analysis of Shared Multidimensional Information. The summary description of OLAP applications used in The OLAP Report, and now very widely referenced elsewhere.
http://www.olapreport.com/fasmi.htm: We think that the FASMI test is a reasonable and understandable definition of the goals OLAP is meant to achieve.


Definition
So, here some of my own definitions (it depends on what angle you want to see it, of course):
OLAP (personal): An interesting possibility I would like to be more involved with.
OLAP (informative): A tool for converting data into actable information.
OLAP (technical): Information analysis that is fast, statistical, multi-dimentional, ad hoc, secure and built on a large amount of data.
OLAP (for the dba): All the groupings and aggregates you can do on a dataset precalculated.
OLAP (measurable): Analysis leveraging a tool that passes the FASMI test.

(F)ast
Tools must give most answers in less than five secounds

(A)nalysis     
It should be possible to add value to the data, converting it to information, being data mining, statistical formulas, score-carding, exception alerting or other special applications

(S)hared     
Integrate into the security model and following any complaience requirements

(M)ultidimentional     
This is not hard to understand: it must take into account more than two measures at a time, eg. the three or four: "what is it", "where is it", "when is it" and possibly "is it".

(I)nformation     
Much information. It must be able to gather, manage and use much information.


Thanks
Thanks goes to two undisputed geniuses
Reed Jacobsen, genius, Hitachi
Kamal Hathi, Lead Program Manager (=genius) for SQLIS (DTS/ETL) (SQL Server 2005) and (Lead, I think) Program Manager for OLAP (SQL Server 2006+), Microsoft

Hardware problems solved, for now...

Hardware problems… I called Dell, complaining of my servers loss of a hard drive. The support technician suggested that I turned off the machine and took the SCSI cables out and back in. Said that the cables could have fallen out while in Transport.

I was not convinced. Turning off a new production server to do this sounded to far fetch for me. Still he (finally) convinced me that this was a good idea. IT WORKED!. I have always told people that Dell has a flawless record by my account. No more. If you miss putting the cables in the disks (so that they fall out after a few days in operation), you can’t say you have made a server.

Still I am back with my AJAX server. While it lasts.

Aaaaaaaaaah What a day!

Aaaaaaaaaah. What a day!
I don’t have MSDN
My hard disk is totally gone.

MSDN: misplaced order!
Today is the official release of VS2005 and SQL2005. Products I have used for 5 percent of my life. But, not having MSDN, I can’t get my hands on the RTM bits. I used to have MSDN, but forgot to renew it, since I was testing betas of the products I used. I saw this coming a couple of weeks ago, so I called MSDN and ordered my MSDN team architect version (or whatever). Every day since, I have asked the invoice department if they have paid the bill. Every day the same answer: We haven’t seen any bill!
So, it turns out, MSDN has misplaced (what ever that word means) my order. This means that while every MSDN subscriber on the planet gets to play with the release, I have to wait.

Hardware problems, I hope, follow up
I lost contact with my native web database server on Sunday. Black-screen. Please insert boot device. This is the 2nd time this year.

Tomorrow will be better. I’m missing webcast of the release. I will watch the recordings. I will lose a day, but, having worked with SQL Server for quite a while and learning about it in all possible ways except visiting the team or SQL PASS. I guess I will just lose this one day.

Good night.

Hardware problems, I hope

Hardware.
I hate being stroked out by hardware. I have this web server I have written (from scratch in a way). But I experience heavy downtime. The last time the disk was physically destroyed. I was certain it was hackers, but when I got to the machine, it complained about not having any boot device…

Upside.
I try to look at the bright side of everything, but hardware failure is a crusher. The only solution I know of is buying more expensive hardware. That costs money. To get the money, you need attention in a budget. To get a budget you need to show some earned value for the amount spent. To show some value of things not crashing you need to see what happens when things go wrong. It is hard to see the bright side of hardware failure.

Hackers.
It is difficult to see how hackers make any contribution to anything. By hackers, I don’t mean the people that makes the tools (say satan or netscan) I mean the average computer users that uses the tools to try to destroy other peoples lives. They are not revolutionaries, they are human bugs.

Hope it was hardware this time too, I will find out tomorrow. Til then, don’t expect anything from my webserver.

Move it all into your new home. Sql 2005.

Data conversion.
Get your data into SQL Server 2005, where it belongs.

Q: How do I get the data I have extracted from [some system] into SQL Server 2005?

TSql OpenRowset.

If your source system talks OLEDB (they all do), I would prefer OPENROWSET.
-- Snippet for Access
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'x:\dir\mymdb.mdb';'admin';'',objectname)
GO
-- EoSnippet for access


Integration Services

If you need to clean your data, or take data from many types of systems and combine them, you can use Integration Services. This takes some work to be done properly (it looks like programming like DTP is here, but it is not quite there jet). Here you can script (or low-level create, for that matter) your own components using the AI stuff you use in your day to day programs (kidding)


Import program

If you are happy with neither OPENROWSET nor SSIS for some reason, often I’m not, you have still a few ways other ways of doing it.
- Writing a CLR Stored Procedure is the COOL way to do it. Showing off your tech skills.
- Using bcp might be the DBA way of doing it.
- Use the OLEDB provider in Delphi, ODBC with C/C++ or even JDBC with Java will work. (I’ve tried the two OLEDB, anyway)
But the best way is to create a Forms app and ADO.NET (in my not so humble opinion). You can do the max of it by reading and writing data at the same time (storing the intermediate in memory), but that would be like re-eventing the SSIS(DTS) wheel. No, just relax and insert your data with ADO.NET (actually, I am not an expert on ADO.NET, because I need to know all the other stuff too. It’s an obsession)


Happy processing and create SSIS packages for the lot. Even if you don’t need to hit the “Data Flow”-button or use the log capabilities.

SQL XML (Re: SQL cursors: This was a funny one)

Moved:
http://information-management-enabled.blogspot.com/2005/11/sql-xml-re-sql-cursors-this-was-funny.html

Rootkit distributed by SONY

If you run a 32-bit OS, don’t play copy protected music from SONY on your PC.
If you run 64-bit Windows XP or 64-bit Windows Server 2003, you are more likely to be safe.

http://www.sysinternals.com/blog/2005/10/sony-rootkits-and-digital-rights.html

SONY distributes a rootkit!
You might allready have a rootkit (colllection of hidden programs) on your PC that is eating up your CPU cycles if you have played a copy-protected CD from SONY.

If the system of the free market does not adjust this kind of behavior, I will study economics and go into politics!

This shows that it is important to have technical competent advisors high up in the large enterprises, I for one reacted impulsively to this as decision-making without knowing the technology you rely on. SONY should never have taken this risk, it is overly aggressive. It will no doupt be a costly mistake.