Re: Table Analyzer and Normalization

From: BI_Specialist (BISpecialist_at_discussions.microsoft.com)
Date: 02/23/05 

Date: Wed, 23 Feb 2005 12:05:05 -0800

Hi John,
Thanks a lot for the reply. It appears that I'm on the right track for the
present. See specific follow up comments below:

"John Nurick" wrote:

> Hi John,
>
> It sounds as you know more than Table Analyzer. Here's what I'd probably
> do:
>
> 1) Work out a normalised (or part-normalised) data structure suitable
> for the reporting needs. Calculate how many bytes of actual data this
> would involve over 12 months. If this is much over 1GB (to allow for
> indexes and other overhead) then a single Access back end won't do.

I was succesful finally in creating a 8 table db working with 25k rows in
Table Analyzer. For my current level of granularity, one back end might do,
otherwise I will store some of the tables in another db and use linked tables
to create one view for querying purposes as you suggest.

> 2) Link (save space by not importing) to the big text file and run a
> series of append queries to populate the normalised tables. If any of
> the normalised tables are really big, it seems to help if you remove as
> many indexes as possible before appending, and re-create them afterwards
> having first compacted the database.

I am linking my big Access table. My normalized dimesion tables have only
one index that link to lookup fields in the Fact table (thanks to Table
Anlayzer, this is done automatically). Now the challenge seems to be in
populating the rest of the 1million odd rows from my source table into this
schema via update queries - while still maintaining referential integrity.
Basically I've to recreate what Table Analyzer did, by associating the
correct lookup values in fact table to new id's in dimension table while I
append each of the tables with new data, so that the entire row can be
recreated.

> 3) Create a series of queries that give views of the data that the users
> can base their own queries on.

> NB1: To get round the size limitation, given that this data will be
> read-only and there's therefore no worry about maintaining relational
> integrity against user editing, you could put some of the tables into a
> separate mdb file and use linked tables to connect the two. Or perhaps
> you could put one over on your DBA and install MySql somewhere.

 Isn't that blasphemous...mentioning MySql in a Microsoft forum? :) I have
in fact installed MySQl and an evaluation copy of SQl server too. Preference
is for SQL server, just because the integration between Excel, SQL server is
tighter, especially when it comes to building Pivot Tables or building cubes
from Excel.

> NB2: If you're faced with humongeous text files, they can be brought
> down to size (e.g. strip out unwanted fields or records) with text-file
> tools from the Unix world (see http://unxutils.sourceforge.net/ and
> http://gnosis.cx/publish/programming/text_utils.html) or a scripting
> language such as Perl.
>
> John Nurick [Microsoft Access MVP]

No such luck regarding reducing the number of fields. If anything if I am
successful with this effort, we'll be adding more granular data (weeks, city,
zip code etc) to the database.

Thanks again for all the suggestions.

Relevant Pages

  • Re: The best elegant solution to override 65k rows limit in a sheet
    ... Access ships with MSDE.. ... >SQL Server Books Online (again-- Access ships with freeware SQL Server ... better ways to achieve their results through queries. ...
    (microsoft.public.excel)
  • Re: Official Status of SQLServer 2005 ADP
    ... solution might be to use ADP. ... With MDB and Linked tables, the only ways of accelerating things are the use ... of Views and the cumbersome use of SQL passthrough queries. ... > SQL Server, and carry on using Access like I aways had. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: SQL Server 2000 and Latching problem
    ... While the way we indexed our tables and the way we wrote the queries etc ... "chances are you are getting latches because you are ... query on the results the query .. ... SQL Server 2000 and Latching problem ...
    (microsoft.public.sqlserver.connect)
  • Re: SQL Server (?) performance issues
    ... it's usually that those queries are being blocked by something else. ... Columnist, SQL Server Professional ... exact counters are being watched, but we are being told that all the ... all is fine and there is plenty of spare capacity. ...
    (microsoft.public.sqlserver.server)
  • Re: Data integrity
    ... I do not expect 100% integrity, what I mean by that is "as ... mechanisms in the database and I have to use fingerprints ... SQL server provides guarantee against possible data damage ...
    (microsoft.public.sqlserver.security)