Konzeption Daten Import-/Exporttool mit Zusammenfassen von Daten, welche Unterschiedlich sein können

Guten Morgen,

ich bräuchte mal wieder nen kleinen Denkansatz. Und zwar soll ich eine komplett neue Version eines Programmes erstellen, was folgendes bewerkstelligt.

[Momentaner Stand]
Das Tool läuft einmal pro Tag und ruft eine Batchdatei auf, welche wieder rum einen Datenexport anstößt. Die Exportierten Daten werden zur zeit in eine Access mdb gespeichert.
Diese Datei wird ausgewertet auf einem MSSQL Server werden die gleichnamigen Tabellen erstellt (alle Spalten als nvarchar [dazu komm ich aber noch] und die Daten importiert.
Danach werden die Daten ausgelesen zusammengefasst und in eine weitere dynamisch erstellt Outputtabelle geschrieben, wovon sie dann mit einer Excel Datenverbindung in Excel angezeigt werden.

Bei den Daten handelt es sich um Artikel, mit Einkaufspreisen, Warenbeständen, Lieferanten und Herstellern etc

Artikelnr. können mehrfach vorkommen und bilden lediglich mit der Herstellernummer eine eindeutige ID. Die Spalten mit Strings wie Warenbezeichnung etc sind häufig identisch, können sich aber auch unterscheiden. Die Spalten mit Zahlen etc sollen summiert, mittelwerte usw berechnet werden.

Ziel ist es, lediglich einmal die Informationen zu jedem Artikel angezeigt zu bekommen. Mit summierten Lagerbeständen etc

Durch Teilweise unterschiedliche Werte fällt da meines Wissens nach Group By raus.
Das Rechnen ging ohne umkonvertierungen ebenfalls nicht (da wie erwähnt alle Spalten nvarchars sind)

Bei unterschiedlichen Datensätzen soll, dann anhand einer Prioritätsliste entschieden, werden welcher Datensatz von welchem Hersteller (Prioritäten werden anhand der Herstellernummern festgelegt) genommen werden soll.

(Hersteller2 ist höher priorisiert)
z.B. Artikel1 Hersteller 1 Bestand 5 Bezeichnung1
und Artikel1 Hersteller 2 Bestand 4 Bezeichnung2

soll später rauskommen
Artikel1, Hersteller2 Bestand 9 Bezeichnung2

Meine erste Idee ist ein Dataset. Da die Datensätze ungefähr 70.000 bis 200.000 Datensätze pro Tabelle sind und es ca 10 Tabellen sind dürfte es noch gehen. Die Auswertung würde, dann das Programm übernehmen und das Ergebnis in die Outputtabelle schreiben (würde ich mir sogar die ganzen Tabellen sparen)

allerdings würde ich das gerne von der Datenbank erledigen lassen und daher wollte ich mich auch mal erkundigen ob es nicht zufällig mit StoredProcedures realisieren lässt.
Da würde ich mir quasi ein "mapping" anlegen, damit ich weiß welche spalte in der access datei bzw in der csv datei welchem datentyp im mssql entsprechen soll (und dann die strings ggf in einen float oder integer umkonvertieren) die ganzen tabellen 1:1 (wie jetzt auch schon) in die Datenbank schreiben und dann mittels storedprocedure oder so die daten zusammenfassen, ggf entscheiden welcher von n-fastgleichen datensätzen genommen werden soll und ansonsten die berechenbaren spalten berechnen.

Hallo st@tic

Warum benötigst du den Umweg über die Access-Datenbanken? Kannst du die Daten nicht direkt in CSV-Files exportieren? Das wäre wesentlich schneller.

Ich würde die Tabellen nicht jedesmal neu anlegen und wieder löschen. Wenn die Tabellen immer wieder neu angelegt werden kann SQL Server bestehende Execution Pläne nicht wiederverwenden sondern muss alles immer wieder neu kompilieren.

Natürlich kannst du das ganze direkt über SQL machen. Ist wesentlich schneller und wesentlich weniger Code.

Ich habe solche Sachen schon einige Male gemacht. Wo hängt's denn genau?

Grüße
Flo

also momentan läuft es über access.
ich persönlich würde es auch mit csv files machen.
auch das immer wieder neu anlegen wird zur zeit so gemacht.

ich versuche es effektiver zu gestalten (und weil die jetzige lösung einfach nicht mehr auf die wünsche erweiterbar ist)

mein größtes problem ist fehlende erfahrung. darum ging es in erster linie um machbarkeit.

es sollte so ablaufen
csv daten werden in die jeweiligen tabellen importiert (wobei kann ich einfach so die daten z.b. in float oder integer spalten importieren sofern es zahlen sind?)
bei oracle ist das ja etwas einfacher, da hat man den sql loader gibt dem ne command-datei und der importiert dann feucht fröhlich das ganze.

die stored procedure müsste folgendes können
die importierten daten aus den tabellen auslesen (zusammenstellen)
dann untersuchen welche doppelt, dreifach oder sonstwas vorhanden sind (anhand der artikelnummer).
die zahlenwerte summieren, mittelwerte berechnen usw (ist im vorfeld schon bekannt) und teilweise strings in spalten, welche sich unterscheiden anhand einer prioritätenliste (HerstellerID) ,welche als parameter übergeben werden könnte?, entscheiden welche der beiden werte er für den datensatz genommen wird (also z.b. die Produktbezeichnung) Aufgrund der nicht gleichen Daten fällt ja wie schon gesagt die Gruppierung weg.

das ganze dann in eine outputtabelle speichern (das halte ich für sinnvoll, weil mehrere leute auf die daten zugreifen werden, aber sich die daten nur einmal täglich ändern.)

und wie gesagt ich persönlich mit meiner Bescheidenen Erfahrung stelle mir es halt so vor, dass das Programm lediglich die ganzen Vorgänge anstößt, aber selbst wenig bis garnix berechnen, auswerten muss.

als nächster schritt wären natürlich ein paar gute tips wichtig, wie ich solche etwas komplexeren prozeduren erstelle, leider hab ich in richtung mssql kein gutes buch zur hand.

also momentan läuft es über access.
ich persönlich würde es auch mit csv files machen.
auch das immer wieder neu anlegen wird zur zeit so gemacht.

ich versuche es effektiver zu gestalten (und weil die jetzige lösung einfach nicht mehr auf die wünsche erweiterbar ist)

War nur ein Hinweis, wenn die Access Datenbanken gegeben sind ist's halt erstmal so 😉.

mein größtes problem ist fehlende erfahrung. darum ging es in erster linie um machbarkeit.

Die Machbarkeit ist auf jeden Fall gegeben.

es sollte so ablaufen
csv daten werden in die jeweiligen tabellen importiert (wobei kann ich einfach so die daten z.b. in float oder integer spalten importieren sofern es zahlen sind?)
bei oracle ist das ja etwas einfacher, da hat man den sql loader gibt dem ne command-datei und der importiert dann feucht fröhlich das ganze.

Du kannst dir, wenn die Daten eh schon in den Access Datenbanken vorhanden sind, den Umweg über die CSV Files sparen. Schau mal nach dem Thema "Linked Server" so kannst du die Access Datenbanken direkt über die Prozedur einlesen.

Ob das mit Oracle "einfacher" ist weiß ich nicht, würde ich aber einfach mal anzweifeln 😉. Im SQL Server:
* Kannst du die Daten direkt aus den Access Datenbanken auslesen ohne die CSV Files
* Kannst du auch die CSV Files entweder über Linked Server oder auch über BCP oder BULK INSERT direkt importieren - was extrem schnell ist.
* Kannst du die Werte natürlich auch beim Import direkt Konvertieren
* Kannst du die Files über SSIS einlesen, konvertieren, umstellen, filtern, umbauen, inserten.

Soll jetzt nicht heißt, dass das mit Oracle nicht geht - das weiß ich nicht. Aber mit SQL Server geht's (auch).

... dann untersuchen welche doppelt, dreifach oder sonstwas vorhanden sind (anhand der artikelnummer). die zahlenwerte summieren, mittelwerte berechnen usw (ist im vorfeld schon bekannt) und teilweise strings in spalten, welche sich unterscheiden anhand einer prioritätenliste (HerstellerID) ,welche als parameter übergeben werden könnte?, entscheiden welche der beiden werte er für den datensatz genommen wird (also z.b. die Produktbezeichnung) Aufgrund der nicht gleichen Daten fällt ja wie schon gesagt die Gruppierung weg.

Schau dir mal ROW_NUMBER OVER PARTITION an. Damit kannst du die Duplikate sehr einfach rausschmeißen.

das ganze dann in eine outputtabelle speichern (das halte ich für sinnvoll, weil mehrere leute auf die daten zugreifen werden, aber sich die daten nur einmal täglich ändern.)

Ist auch sinnvoll. Entspricht den Facts-Tabellen bei Datawarehouse Datenbanken.

und wie gesagt ich persönlich mit meiner Bescheidenen Erfahrung stelle mir es halt so vor, dass das Programm lediglich die ganzen Vorgänge anstößt, aber selbst wenig bis garnix berechnen, auswerten muss.

Wenn du die Sache über eine (oder mehrere) Prozedur(en) machst brauchst du eigentlich gar kein Programm mehr. Du kannst die Prozedur entweder über ein Batch-File und SQLCMD aufrufen oder einen Task im SQL Server einrichten der entweder zeitlich gesteuert oder per Aufruf startet.

als nächster schritt wären natürlich ein paar gute tips wichtig, wie ich solche etwas komplexeren prozeduren erstelle, leider hab ich in richtung mssql kein gutes buch zur hand.

Ein sehr gutes Buch zu T-SQL ist z.B.
Inside Microsoft SQL Server 2005
T-SQL Programming
von Itzik Ben-Gan

Grüße
Flo

also momentan läuft es über access der export geht auch als csv also noch ne umbastelei ist es nicht.

ssis klingt auch jedenfall mal "nachschlagenswert"

ROW_NUMBER OVER PARTITION
kann ich damit die entscheidung treffen welche daten von den nicht gruppierbaren spalten genommen wird
also quasi alles berechnen und gruppieren und bei den "risikospalten" ein over mit der klausel where Herstellernummer = Nr1
und mit OR Verknüpfungen quasi die "Reihenfolge" festlegen?

oder hab ich da jetzt irgendwie was falsch verstanden bei?

ROW_NUMBER OVER PARTITION
kann ich damit die entscheidung treffen welche daten von den nicht gruppierbaren spalten genommen wird
also quasi alles berechnen und gruppieren und bei den "risikospalten" ein over mit der klausel where Herstellernummer = Nr1
und mit OR Verknüpfungen quasi die "Reihenfolge" festlegen?

Korrekt. Du kannst einschränken und Kriterien definieren soviel und worauf du willst. ROW_NUMBER() (oder RANK() oder DENSE_RANK() ) kann über bestimmte Gruppierungen Sequenzen bilden welche du als Kriterium angeben kannst. Da ROW_NUMBER nicht innerhalb der WHERE Klausel verwenden kannst pack die ganze Sache einfach in eine CTE, auf die kannst du dann abfragen und einschränken.

Hier ein simples Beispiel:

DECLARE @Artikel TABLE
(
   Id INT IDENTITY,
   Hersteller VARCHAR(30),
   Artikel VARCHAR(30)
); 

INSERT INTO @Artikel
             SELECT 'H1', 'A1'
   UNION ALL SELECT 'H1', 'A2'
   UNION ALL SELECT 'H2', 'A2'
   UNION ALL SELECT 'H2', 'A3';


WITH a AS
(
   SELECT
      Id,
      Hersteller,
      Artikel,
      ROW_NUMBER() OVER (PARTITION BY Artikel ORDER BY Hersteller) RowNum
   FROM @Artikel
)
SELECT 
      *
   FROM a
   WHERE RowNum = 1

Grüße
Flo

das muss ich mir morgen dann wohl mal genauer anschauen.
schonmal danke

was ist die abkürzung cte eigentlich?

was ist die abkürzung cte eigentlich?

CTE steht für "Common Table Expression". In meinem Beispiel der Teil von "WITH" bis zum Ende der Klammer. Diese können als u.a. Inline-Views verwendet werden und bieten einige geniale Features. SQL Server kann diese meist wesentlich besser auflösen und verarbeiten als Sub-Queries. Außerdem sind sie wesentlich leichter zu lesen, wenn man sich an die Syntax gewöhnt hat.