Northwind Database For DB2

Well if I thought Oracle was a mess getting the Northwind database to I hadn’t seen anything yet. I don’t care for the user tools at all in IBM Data Studio. The mapping to get SSIS to push a copy of my SQL based Northwind database was all over the map. If you don’t know, Northwind is the training tool of choice for many developers, businesses and consultants.

So again, I am doing you a solid. Linked here is the SSIS package you can pick up and use to transfer your SQL based version of Northwind to DB2. I also included the DB2 ddl for the database for your reference. I haven’t found an easy way to do it from the Access version. Sorry folks.

A couple things. You will need to create the database on your DB2 instance like below. use your standard command prompt with admin privileges.

create database NORTHWIN using codeset UTF-8 territory en

You may notice I left off the D. Yes I did. DB2 prefers its databases to be 8 characters or less. So that is why

Get the DTSX package here.

Once you have it open in Notepad and edit these lines with your information:

DTS:ConnectionString=”Data Source=DESKTOP-P1TI349;Initial Catalog=Northwind;Provider=SQLOLEDB;Integrated Security=SSPI;Auto Translate=false;” />

DTS:ConnectionString=”Data Source=NORTHWIN;User ID=db2admin;Provider=IBMOLEDB.DB2COPY1;Persist Security Info=True;Location=DESKTOP-P1TI349:50000;Extended Properties="";”>

Find/Replace DB2COPY1 with the name of your instance.

Save the file and open in SSIS.

And then you should be good to go…. if you have changes in your SQL Northwind database that aren’t reflected here (it will prompt you) you will need to use SSIS to transfer the data. Be sure for your destination you choose the instance name of your DB2 instance. (Mine was DB2COPY1).

That’s about it. Have a good day….

Northwind Database For Oracle

Ah Northwind. The database everybody learns on. Well almost everyone. I realize it is a Microsoft product but you would have thought somebody along the line would have developed the same for Oracle and MySQL.

Why did this come up? In the next rendition of MySQLMove (which is being renamed for the next release – I still haven’t settled on a title for it yet. Something that indicates it is multi environment data collector and reporter. If you have suggestions or ideas please let me know…) I have been telling you about I wanted to add Oracle back ends as an option to get your data from. During the examples on past releases I used Northwind to demonstrate how to use the software. So I looked high and low on the web and didn’t find a single useable version of Northwind for Oracle. So I did it the old fashioned way. I exported my Northwind from SQL, table by table to excel files and imported them into Oracle. There’s an hour of my life I won’t get back. So just in case any one else has to go through this I pulled all the Oracle scripts for creating the tables and inserting the data, zipped them up and have made them available to you for download. All you have to do is download it, unzip it and run the scripts. Why? Because I am a nice guy that way and I had the time.  If I saved anyone some time, it was worth it.

MySQLMove PivotWizard Adds SQL,CSV, MS Access Support and Chart Creation

If you haven’t been following along, MySQLMove is a tool that started out as a way for MySQL developers to do things the free tools to manage MySQL did not manage well. One of these things was the PivotWizard, which originally allowed MySQL developers, who do not have access to a pivot related function, to accomplish so here. As time went on, it became the most popular part of this software. The early versions of the PivotWizard were crude and did just the basics of pivoting data. I first added SQL support. Now I have added Microsoft Access and CSV file support, in addition to MySQL and SQL Server Database support and also have given you the ability to choose from 50+ different chart types to be able to look at your data in different ways. A lot has changed so I am going to go through it again, step by step.

You can download it here. Be sure to remove the old version if you installed it as the default MS install package can be buggy that way about removing it. Below is the directions on how to use the PivotWizard.

When you open MySQLMove you will see the following:

Click on the “Pivot Wizard”.

You will then see the screen to allow you to specify your connection type and parameters. If you choose MS Access you can use mdb or accdb file types and can specify if you use a password for it not. If you choose MS Access or CSV, a file dialog box will appear and ask  you to browse to the path your file is located at. If you choose CSV, it MUST be a comma delimited file and you should have a header row to define the columns. (Incidentally I should note that as long as your CSV files are located inside the same directory (folder) you specified in your connection you can join separate files in a query just as if you were joining two SQL tables like so:

SELECT *FROM [Orders.csv] a
INNER JOIN [Employees.csv] b on b.[ID] = a.[Employee ID]

No need for tools like PowerShell, other applications or confusing syntax.)

If you choose MySQL, be sure to precise on your casing of words. For this example, we will check the box for SQL Server and fill in its connection parameters.

I suggest you click on the Test button and verify all is well. I will check it for you before you leave the following screen but may as well get that out of the way now. If you get any of this information incorrect, you can’t proceed until it is fixed.

Once this is done, click the “Next” button.

You will then be asked to type or copy and paste a query based on this connection into the textbox. If you chose CSV format in the earlier screen this information will be filled in for you. After  you enter the query, you must click the “Parse” button. This will verify your connection is correct and your statement compiles. If it does, you will be told via message box how many columns you have to work with and then you will click “Next” to proceed. If it does not compile correctly, you will be informed and you will have to fix the statement to be able to proceed. (Please don’t email me about those kinds of issues. I really don’t have time for that.). You might also see a prompt letting you know it compiled with errors. As long as the issues aren’t fatal, you will be allowed to proceed and a report will be given to you letting you know what the problems were. Below the query box, you are asked to identify what you want Excel to call your report. Since this is the raw data, I called it “Raw Data”. This field is not mandatory. You might also notice the “?” mark icon. On the previous version, I attempted to have you directed to links to the MySQL Facebook group to answer common questions. That was a miserable failure. Instead in this edition, I have placed a tooltip that when you hover over the icon, it gives an explanation of what is going on.

Once this step is completed click the “Next” button.

Here you will pick what columns you will want to appear as the rows in your pivot report. You can select multiple columns as rows, just be sure they are related somehow. Once selected you are allowed to choose a name for the field. Excel will use that name instead of the field name in your report. Click the “Next” button to continue.

You will then be asked to choose “data” for your report. In this case I have chosen to see how many Order_IDs exist for each salesman (see the last screen). Use the arrows to choose your fields (you can have more then one, and you can reuse the same field if need be.) and select a grouping to be applied. Click the “Next” button to continue.

You will then be asked to choose the columns you want to represent in your pivot report and how you want the data grouped. If  you notice we choose to see Order_Date twice in our columns because we want it grouped by Quarter and inside that quarter, by Day of Week. I add a column to your original query with the same name and data with a “_1” after it. So now we have asked to see how many orders our salesman did, divided into quarter and inside that quarter by Day of Week. Play with it, you will get the hang of it. Click “Next” to continue.

Here you choose the type of chart to represent the data you have requested and in Chart Title have given Excel the information it needs to call your sheet and chart name what you want. Hopefully the images of the charts you see will assist you in making the choice of charts that you want. Click the “Next” button to continue.

Finally, at the end. Now you can give your pivot report a name for Excel to use for your pivoted data (or not if you don’t care) and then click the “Finish” button. Be patient, it takes a few second for Excel to show you your work. Just as a word of caution, I tried to anticipate anything you could do wrong in naming your charts and reports. If something you should happen, you will get a notice of what the problem is but it will allow you to still see what you have produced. It just will not give the worksheet name you tried to give it.

You can see the sample we produced here.

The next version of this software will allow you to save templates of different reports and allow you to create more then just one query. The next version will allow you to store multiple connections, multiple querys and pivot reports and charts specific to each query, each produced into the same Excel Workbook. Keep an eye on here and on the MySQLMove Facebook page for updates.Again you can download the install package for MySQLMove here.

Well that about covers it. Well not quite. I have been asked if I would be willing to do a custom version of this for a specific person or company. The answer is of course, yes. Also if you have bugs you have seen please let me know. Just email me at kellyjmartens@hotmail.com and I would be happy to help.