Opțiunea Multidimensional Cube a Analysis Services a gestionat cu ușurință multe-la-multe relații cu ușurință pentru multe versiuni înainte de 2016. Tabularul a lucrat cu ajutorul formulelor DAX până la lansarea SQL Server 2016. Există încă unele limitări pentru mulți-pentru mulți în Tabular, dar, desigur, există câteva „trucuri” pentru a depăși limitele. Dar, relația mulți-mulți va fi în datele de afaceri pentru mulți ani de acum încolo. O soluție trebuie furnizată atunci când vine vorba de bazele de date ale serviciului de analiză.

multe-multe

Înainte de a merge la dezvoltarea și asistența multor la mulți în Analysis Services, ETL-ul unei aplicații de raportare trebuie să formateze datele în tabele care să susțină această opțiune. Aceasta ar putea fi denumită o masă bridge. Datele din tabelul tranzacțiilor sunt „conectate” la numeroasele categorii de suport posibile. Datele Adventure Works DW oferă un exemplu excelent cu multe motive de vânzare legate de multe elemente rând de vânzări pe internet.

Figura 1 prezintă un exemplu de proiectare a acestor tabele.

Figura 1: Relația Mulți-Mulți în Cub

Relația de editare din Figura 1 arată într-un cub relația dintre 2 tabele cu mai multe coloane - SalesOrderNumber (Număr comandă) și SaleOrderLineNumber (Număr linie comandă). Această tabelă de legătură, FactInternetSalesReason, leagă motivele de vânzare de la tabelul de dimensiuni DimSalesReason la tabelul de fapt FactinternetSales prin aceste 2 coloane. Tabelul FactInternetSalesReason poate avea mai multe intrări pentru același număr de comandă plus numărul de linie de comandă. Exemplul din tabelul de mai jos prezintă 2 numere de comandă de vânzare diferite cu 3 motive de vânzare diferite.

SalesOrderNumber SalesReasonName
SO51214 În promoție
SO51214 Alte
SO51214 Preț
SO51298 În promoție
SO51298 Alte
SO51298 Preț

Tabelul 1: motive multiple de vânzare

Figura 2 prezintă Analiza în Excel atunci când nu există nicio relație între motivul vânzărilor și vânzările pe internet pentru cub. Aceasta este aceeași ieșire primită inițial și cu Tabular. Relațiile multe-la-multe nu sunt atribuite automat la construirea unui cub sau a unui model tabelar prin vrăjitori. Există mai multe lucruri de făcut după finalizarea vrăjitorului. Măsurile Numărului de vânzări și Valoarea vânzării sunt însumate pe toate rândurile, mai degrabă decât în ​​felia de vânzare Motiv de vânzare utilizat în Figura 2.

Figura 2: Analizați în cubul inițial Excel

Relația nu este o relație regulată pentru relația de dimensiune în cub. Dar, înainte de a stabili relația, trebuie să fie create dimensiunile Motivul vânzărilor (tabelul DimSalesReason) și Vânzările prin Internet (tabelul FactInternetSales) plus un grup de măsuri pentru tabelul FactInternetSalesReason. Măsura poate fi un număr de rânduri și ascunsă prin proprietatea Vizibilă a măsurii. Odată ce acestea sunt create, relația mulți-la-mulți poate fi creată în relația dimensiunii cubului dintre dimensiunea motivului de vânzare și grupul de măsurare a vânzărilor pe internet, ca în figura 3.

Figura 3: Relație Mulți-Mulți din motive de vânzare

Sub grupul de măsură Vânzări pe Internet, relația Motiv de vânzări este o relație de fapt între dimensiunea de vânzări pe internet de fact și grupul de măsură de vânzări pe internet. Tabelele de date pot fi dimensiuni în Analysis Service și conțin atribute precum numărul PO sau Compania de transport. Poate fi creat și pentru această relație și făcut vizibil = fals în cub. Dimensiunea Vânzării Motivul și Faptul Vânzările pe Internet sunt legate de noul grup de măsuri prin relații regulate.

Figura 4: Analizați în Excel - Corectat

Figura 4 arată acum numărul corect și sumele în dolari în timp ce se analizează în Excel. Suma totală a vânzărilor ca total total este totalul corect al tuturor vânzărilor filtrate. Totuși, suma totală a motivului vânzărilor din tabelul pivot va fi mai mare decât suma totală din cauza vânzărilor care au mai multe motive de vânzare. Este important să instruiți utilizatorii finali cu privire la această variație.

Modelul tabelar al serviciilor de analiză utilizează o nouă caracteristică în SQL Server 2016 numită filtrare bidirecțională. Filtrarea bidirecțională este utilizată pentru mulți-mulți. Crearea filtrării bidirecționale este pentru filtrarea printr-un singur tabel, să spunem un tabel de fapt, la o agregare într-un tabel de dimensiuni. Unii ar putea activa acest lucru la fiecare relație, dar Microsoft avertizează să nu facă acest lucru. Implementați doar acolo unde este necesar.

Singurul mulți-la-mulți cu care va funcționa este un tabel cu 3 mulți-la-mulți. Figura 5 prezintă motivul vânzărilor pe internet, motivul vânzărilor pe internet și motivul vânzărilor într-un model tabelar. Nu utilizați acest lucru atunci când mai mult de 3 tabele sunt implicate într-o relație de mai mulți la mulți.

Figura 5: Tabel Motiv de vânzări Multe la Multe

Ceea ce nu poate fi văzut în acest exemplu sunt coloanele utilizate pentru a lega InternetSalesReason de InternetSales. Relațiile din Modelul tabelar (și din Power BI) pot fi doar o singură coloană. Deci, acest exemplu folosește o coloană calculată în tabelul InternetSales și InternetSalesReason. Figura 6 prezintă această relație.

Figura 6: Relație de la mai multe la multe vânzări pe internet

De asemenea, meniul derulant Filtru direcție arată „Către ambele tabele” în loc de mai multe părți ale unei relații. Aceasta este opțiunea de filtrare bidirecțională. Coloana utilizată pentru a lega cele 2 tabele se numește AltKey. Figura 7 prezintă coloana calculată în tabelul InternetSalesReason.

Figura 7: AltKey de coloană calculată

Coloana calculată AltKey utilizează această logică –InternetSalesReason [SalesOrderNumber] & „-” & InternetSalesReason [SalesOrderLineNumber]. SalesOrderNumber este concatenat cu SalesOrderLineNumber cu o liniuță între cele 2 valori. Această coloană creează o cheie alternativă pentru tabele. Deoarece este creat atât în ​​InternetSales, cât și în InternetSalesReason, acestea pot fi utilizate pentru a se alătura celor 2 tabele de date. Amintiți-vă, tabelul InternetSalesReason este tabelul de legătură între DimSalesReason și FactInternetSales.

Filtrarea bidirecțională poate fi utilizată în alt mod. Spune că utilizatorul final dorește să vizualizeze un număr de produse distincte asociate cu lista de vânzări până la un anumit an. O relație normală multi-la-unu nu va putea demonstra asta. Figura 8 prezintă relația normală DimProduct cu InternetSales.

Figura 8: Relația produsului cu vânzările pe internet

Figura 9 prezintă numărul distinct de produse cu un filtru pe an pentru vânzările pe internet.

Figura 9: Număr distinct cu anul vânzărilor pe internet

Numărul distinctiv este același pentru toți anii, chiar și pentru ani fără vânzări. Schimbarea relației în vizualizarea în diagramă a modelului tabular va rezolva această problemă, cum ar fi Figura 10.

Figura 10: Filtru bidirecțional pentru dimensiunea produsului

Figura 11 prezintă rezultatele Analize în Excel din utilizarea filtrării bidirecționale pe relația tabelului de producție cu tabelul InternetSales.

Figura 11: Filtru bidirecțional Analizați în Excel

Analysis Services a reușit să gestioneze astfel de logici de afaceri în MDX și DAX. Dar utilizatorii sunt mai interesați de aceste reguli logice de afaceri care urmează să fie implementate în baza de date. Modelul tabular începe să semene din ce în ce mai mult cu Cubul multidimensional cu rezultatul final, nu în metoda de obținere a rezultatului. Din fericire, există o mulțime de utilizatori în comunitatea Microsoft Data Technology dispuși să arate cum funcționează acest lucru. Urmăriți noutățile care apar în SQL Server 2017 și rămâneți la curent cu modificările.