Data Mapping Database Report

I am working on a data mapping project (i.e. mapping tables from a SQL server database to VSAM files on a mainframe). I know that's bass-ackwards .. but that's what "the man" wants. And what "the man" wants he gets :)

What i am trying to do is to produce one report for each table (in the SQL server database). Each report would contain one column per field-name. Along with the field-name, i would like to also include the 10 most popular values of that field, a count for each, and one key per value (to refer-back).

If anyone is following along, do you have any suggestions ?? I'm assuming Crystal MUST have this functionality, but i'm banging my head against a wall trying to figure out even where to begin.

Thanks in advance !!The first part is easy - I presume you can use the report expert to get the raw data from the SQL table and show that as columns in the report. Am I right?

If you can do that, then you want to produce an individual count for each field - do you mean a count of unique elements for each field, and then the topN for each field.

Here's some sample data, by way of clarification:
ID Name City Salary
1 John A 20000
2 Fred A 20000
3 Mary A 50000
4 Kim B 35000
5 **** C 33000

A = 3 90000
B = 1 35000
C = 1 33000

If that is a very simple example, then you need to create a group for each field that you want to summarise, in this case City, and then create Count field in groop footer for City, and Sum field for Salary.

There's a start. TopN side of things should be able to be achieved using the TopN/Group Sort expert.

If you havew lots of fields, you'll have lots of groups.......my example groups on only City, so is simple, but your could end up looking rather ugly, I think!

Dave|||Hey Dave,

You're on the right track, but what i wanna do is "flip" the report around a little. Let's say i have a Customer table ... with SSN, Name, Addr.

I want my report to contain ONE ROW for each field-value ... not one column. My columns would be the Field-Name, Most Popular Occurence, Refer-Back key, and Count.

I would want my report to look like ...

Field Most popular value 1 Key (to refer back) Count (for that value)

SSN 111111111 111111111 1
222222222 222222222 1
333333333 333333333 1
...
123459699 123459699 1

Name John Smith 243050630 24
Dave Smith 294848372 22
Bob Jones 249858382 21
...
Tom Brown 385838375 11

Addr 123 Main St 948473859 6
123 Main Dr 938584894 6
222 Main St 983583959 4
...
123 Broadway 958347583 2

Thanks for your input !

I know that's ugly. My fields are lined up in my message text, but all of the spacing is ignored when the message is saved.

I want one row per field-name, and then 10 Values, refer-backs, and counts for each field name.

data mapping and migration

Has anyone used DTS packages for migrating old data to a new schema?

If so are there any tutorials on this?

I'd prefer not to do this by hand. ;-)Just use DTS graphic wizard.
BOL is the best tutorial|||Try the wizard in Enterprise Manager. That's a nice easy intro into it.|||I have a simple package where I want to dump data from one table to another. Its erroring out on this line.

DTSDestination("TaxExempt") =1

The destination field is int.

I don't receive any errors when i have the following:

DTSDestination("ShippingWeight") = DTSSource("weight")

for some reason it doesnt like the hardcoded value i supplied it.

Any ideas why?

Thanks for the help.

Data mapping and Importing app for SQL server

Hello,

Our company often receives data from outside sources to add to our application. This data is usually provided to us in Excel, CSV, XML, etc. The files that we receive usually have different columns from the columns in our database, so we have to map these columns to our table structure to import.

I'm looking for an application that will easily allow me to load up the data file (whatever type it may be), expose the columns in the data file, allow me to map these columns in our SQL server, then import the data. I know that this can be done as DTS, however I'm looking for alternatives. Does anyone have any recommendations?

Thanks in advance.

I just dont have a solution to your post but i have the same problem. I just want to get notifications on reply event of this thread. Hopefully some one will answer you and i will benefit.

Data mapping

We have 2 databases - Prod and Dev. They are not syncronized and has a slightly different stored proc-s and table structure (column added/ or removed).

So Prod and Dev are not in sync, they are not mirrors.

Production db has production data.

How I can transition/move/map data from Prod to Dev?

For example, both databases have table Users :(f_name, l_name, field1, field2). And I need to populate dev db with prod data.

What strategy should I follow? What tools showld I use? How I can automate the it?

Should I use Import wizard or DTS?

SQL Server 2000

Thank you

Based on the information you provide both the DTS Import Wizard or a Copy SQL Server Objects Task DTS package will work and both can be automated by a job schedule. Basically they are the same.

If you need to tranform data, which you con't mention you can use a DTS package with tranforms or write you own truncate/insert code and schedule either as a job.

Data Mapping

I'm looking for a tool to make a graphical representation of my MS SQL
databases. specifically, I was hoped to find something that could show the
fields in the tables of all the databases on an MS SQL server.
Anybody know an application that can do that?
Visio for enterprise architects
ERwin
"mindjuju" <mindjuju@.discussions.microsoft.com> wrote in message
news:71D75768-1B7E-4F06-8315-203D86FFAA8E@.microsoft.com...
> I'm looking for a tool to make a graphical representation of my MS SQL
> databases. specifically, I was hoped to find something that could show
> the
> fields in the tables of all the databases on an MS SQL server.
> Anybody know an application that can do that?
|||Hi,
You can use SQL Server Diagrams using Enterprise manager or else you can use
Erwin. Erwin will give you the Physical and Logical model of
your database.
Thanks
Hari
SQL Server MVP
"mindjuju" <mindjuju@.discussions.microsoft.com> wrote in message
news:71D75768-1B7E-4F06-8315-203D86FFAA8E@.microsoft.com...
> I'm looking for a tool to make a graphical representation of my MS SQL
> databases. specifically, I was hoped to find something that could show
> the
> fields in the tables of all the databases on an MS SQL server.
> Anybody know an application that can do that?
|||or ER-Studio
Greg Jackson
Pdx, Oregon