Niedawno stworzyliśmy model tabelaryczny SSAS, aby nasi użytkownicy mieli do niego dostęp za pomocą PowerView. Mamy miarę w jednej z naszych tabel faktów, aby uzyskać TotalActiveItemsformułę:
TotalActive:=COUNTAX(FILTER('Stats', ISBLANK([DeactDate]) = TRUE), 1)Działa to świetnie w razie potrzeby, ale teraz mamy prośbę o zdobycie 10 najlepszych rodziców na każdy miesiąc w TotalActive.
Dla odniesienia, oto część naszego modelu:
create table factStats
(
StatsID INT IDENTITY NOT NULL PRIMARY KEY,
DevID INT NOT NULL,
DeactDate DATETIME NULL,
BillDateTimeID BIGINT NOT NULL,
CustID INT NOT NULL,
ParentID INT NOT NULL
);
create table dimCust
(
CustID INT NOT NULL PRIMARY KEY,
CustName varchar(150) NOT NULL
);
create table dimParent
(
ParentID INT NOT NULL PRIMARY KEY,
ParentName varchar(100) NOT NULL
);
create table dimDateTime
(
DateTimeID BIGINT NOT NULL PRIMARY KEY
);
Fiddle SQL z tabelami i przykładowymi danymi.
factStatsTabela ma FKS do DevID, CustID, BillDateTimeID, i ParentID. Żądanie, które musimy obliczyć lub zapisać Top 10 Parentsdla każdego BillDateTimeIDna podstawie TotalActive AND, obejmuje wszystko, co nie znajduje się w pierwszej dziesiątce, w zwiniętej kategorii podobnej do następującej:
+----------------+------------+------+
| BillDateTimeID | Parent | Rank |
+----------------+------------+------+
| 20140801 | Jim | 1 |
| 20140801 | Bob | 2 |
| 20140801 | All Others | 3 |
+----------------+------------+------+
Mogę to łatwo osiągnąć w SQL przy użyciu funkcji okienkowania, ale próba odtworzenia tego dla SSAS była trudna. W SQL otrzymalibyśmy wynik, używając:
;with Total as
(
select
ParentID,
BillDateTimeID,
sum(case when DeactDate is null then 1 else 0 end) TotalActive
from factStats
group by ParentID, BillDateTimeID
),
PRank as
(
select
ParentID,
BillDateTimeID,
TotalActive,
row_number() over(partition by BillDateTimeID
order by TotalActive desc) pr
from total
)
select
parentid,
BillDateTimeID,
TotalActive,
pr
from prank
where pr <= 2
union all
select
0,
BillDateTimeID,
sum(TotalActive) TotalActive,
3
from prank
where pr > 2
group by BillDateTimeID
order by BillDateTimeID desc, pr;
Wersja demonstracyjna SQL Fiddle .
Próbowałem kilku różnych sposobów, aby uzyskać wynik, ale każdy z nich miał problem. Moje próby są poniżej.
Początkowo byłem w stanie nieco pobrać dane za pomocą zapytania MDX, ale potem nie miałem pojęcia, jak włączyć to do naszego modelu tabelarycznego. Zapytanie MDX w celach informacyjnych to:
with
set [Top10Parent] AS
(
(TOPCOUNT({ORDER(({[Parent].[Parent Name].[Parent Name]}),
([Measures].[Total Count]), BDESC)}, 10))
)
MEMBER [Parent].[Parent Name].[Others] AS
(
AGGREGATE(EXCEPT([Parent].[Parent Name].[Parent Name], [Top10Parent]))
)
select
[Measures].[Total Count] on columns,
{[Top10Parent]}+ {[Parent].[Parent Name].[Others]} on Rows
from [OurModel]
where {[Date and Time].[Month and Year].[Month and Year].[Jul 2014]};
Oczywiście dało mi to wynik tylko przez jeden miesiąc, a nie co miesiąc.
Kiedy zdałem sobie sprawę, że zapytanie MDX nie zadziała, zacząłem od zmiany naszej factStatstabeli, tak aby zawierała nową kolumnę, aby oflagować elementy w pierwszej dziesiątce i w zwiniętej wartości.
alter table factStats
add Top10ParentID INT NOT NULL
constraint DF_factStats default (0);
Domyślne ograniczenie odnosi się do naszej wartości „zwiniętej” dla pierwszej dziesiątki.
Próba nr 1: Utworzyłem nową tabelę Top 10 do przechowywania ParentID, imienia i rangi:
create table dimTop10Parent
(
Top10ParentID INT NOT NULL PRIMARY KEY,
ParentName varchar(100) NOT NULL,
Parent_Rank INT NOT NULL
);
Ta tabela będzie następnie zapełniana za każdym razem, gdy odświeżymy nasz model nowymi 10 najlepszymi rodzicami na podstawie łącznej liczby aktywnych elementów, które mają. Parent_RankKolumna zostaje ukryty w naszym modelu tabelarycznej i wykorzystywane wyłącznie do sortowania. Działa to świetnie, z wyjątkiem tego, że nie mamy możliwości historycznego zdobycia pierwszej dziesiątki, ponieważ nie opiera się ona na miesiącach.
Próba nr 2: Utwórz nową tabelę do przechowywania Top 10, ale KLUCZ PODSTAWOWY będzie zawierał zarówno Top10ParentID, jak i BillingDateTimeID.
create table dimTop10Parent
(
Top10ParentID INT NOT NULL,
ParentName varchar(100) NOT NULL,
Parent_Rank INT NOT NULL,
BillDateTimeID BIGINT NOT NULL
);
Problem polega na tym, że nie możemy utworzyć związku między faktemStats pojedynczym FK a dwuczęściową PK w dimTop10Parent w modelu tabelarycznym.
Próba nr 3: Utwórz nową tabelę, ale użyj tożsamości jako PK.
create table dimTop10Parent
(
Top10ID INT IDENTITY NOT NULL PRIMARY KEY,
Top10ParentID INT NOT NULL,
ParentName varchar(100) NOT NULL,
Parent_Rank INT NOT NULL,
BillDateTimeID BIGINT NOT NULL
);
factStatsTabeli będzie przechowywać Top10IDwartość, która będzie wyjątkowy dla każdego wiersza. Myślałem, że to rozwiąże mój problem, ale tak się nie stało, ponieważ nie możemy już sortować według Parent_Rankmodelu, powoduje to błąd:
Nie można sortować ParentName według Parent_Rank, ponieważ co najmniej jedna wartość w ParentName ma wiele różnych wartości w Parent_Rank. Na przykład możesz sortować [Miasto] według [Regionu], ponieważ dla każdego miasta jest tylko jeden region, ale nie możesz sortować [Regionu] według [Miasta], ponieważ dla każdego regionu jest wiele miast.
Korzystając z przykładowych danych, końcowy wynik powinien być podobny do tego (pokazuje to Top 2 z trzecim zwiniętym):
| PARENTNAME | BILLDATETIMEID | TOTALACTIVE | PR |
|------------|----------------|-------------|----|
| FDN | 201408010000 | 11 | 1 |
| FDO | 201408010000 | 3 | 2 |
| All Others | 201408010000 | 5 | 3 |
| FDN | 201407010000 | 12 | 1 |
| EVOD | 201407010000 | 2 | 2 |
| All Others | 201407010000 | 5 | 3 |
W tym momencie brakuje mi sposobu na uzyskanie tego końcowego wyniku. Mogę w razie potrzeby zmieniać tabele, mogę modyfikować model za pomocą formuły, miary itp. Czytałem o rankingach za pomocą formuł DAX 1 , 2 , 3, ale wydaje mi się, że nie mogę się obejść wystarczająco dużo, aby móc dokładnie uzyskać wynik.
Jak mogę obliczyć / przechowywać tę Top 10 na dowolny miesiąc i nadal móc dzielić dane zgodnie z potrzebami w naszym modelu tabelarycznym?