SQL Server - Como as páginas de dados são armazenadas ao usar um índice clusterizado

Question

SpeedBirdNine

Asked: 2015-03-09 05:28:49 +0800 CST2015-03-09 05:28:49 +0800 CST 2015-03-09 05:28:49 +0800 CST

As funções MDX Max, Average e StDev não fornecem resultados corretos

772

Eu tenho um cenário onde tenho Datedimensão e Timedimensão junto com algumas outras dimensões. A dimensão de tempo contém apenas Hoursporque não houve necessidade de salvar dados em minuto ou segundo nível.

Uma situação exige que eu calcule o valor Averagede Maxcertas horas do dia. Por exemplo, preciso Maxdo valor entre 01:00 e 04:00 horas e a média desses valores máximos no período de tempo determinado (01 de janeiro de 2015 - 03 de janeiro de 2015 neste exemplo).

Estou filtrando Datee Timedimensões (juntamente com outras dimensões) como Subselect Fromcláusulas em vez de usar Where.

Se os Timemembros da dimensão permanecerem os mesmos para cada valor de Datedimensão selecionado, então Max, Avge StDevfornecer o resultado correto, mas se eu tiver que excluir algumas horas de um dos Datemembros da dimensão, Maxresultará em erro Avge StDevfornecerá valores incorretos.

Consulte o código SQL abaixo para criar o exemplo que usarei nesta pergunta:

create database TestDateTimeDimensions
GO

USE [TestDateTimeDimensions]
GO

create table DimDate (
DateId int not null PRIMARY KEY,
Date [Date] not null
)


insert into DimDate
select 20150101, CONVERT(DATE, '2015-01-01')
UNION ALL
select 20150102, CONVERT(DATE, '2015-01-02')
UNION ALL
select 20150103, CONVERT(DATE, '2015-01-03')
UNION ALL
select 20150104, CONVERT(DATE, '2015-01-04')

create table DimTime(
TimeId int not null PRIMARY KEY,
[Time] Time not null
)

insert into DimTime
select 0, CONVERT(TIME, '00:00:00')
UNION ALL
select 1, CONVERT(TIME, '01:00:00')
UNION ALL
select 2, CONVERT(TIME, '02:00:00')
UNION ALL
select 3, CONVERT(TIME, '03:00:00')
UNION ALL
select 4, CONVERT(TIME, '04:00:00')
UNION ALL
select 5, CONVERT(TIME, '05:00:00')
UNION ALL
select 6, CONVERT(TIME, '06:00:00')
UNION ALL
select 7, CONVERT(TIME, '07:00:00')
UNION ALL
select 8, CONVERT(TIME, '08:00:00')
UNION ALL
select 9, CONVERT(TIME, '09:00:00')
UNION ALL
select 10, CONVERT(TIME, '10:00:00')
UNION ALL
select 11, CONVERT(TIME, '11:00:00')
UNION ALL
select 12, CONVERT(TIME, '12:00:00')
UNION ALL
select 13, CONVERT(TIME, '13:00:00')
UNION ALL
select 14, CONVERT(TIME, '14:00:00')
UNION ALL
select 15, CONVERT(TIME, '15:00:00')
UNION ALL
select 16, CONVERT(TIME, '16:00:00')
UNION ALL
select 17, CONVERT(TIME, '17:00:00')
UNION ALL
select 18, CONVERT(TIME, '18:00:00')
UNION ALL
select 19, CONVERT(TIME, '19:00:00')
UNION ALL
select 20, CONVERT(TIME, '20:00:00')
UNION ALL
select 21, CONVERT(TIME, '21:00:00')
UNION ALL
select 22, CONVERT(TIME, '22:00:00')
UNION ALL
select 23, CONVERT(TIME, '23:00:00')

CREATE TABLE Fact (
Id int identity not null PRIMARY KEY,
DateId int not null REFERENCES DimDate(DateId),
TimeId int not null REFERENCES DimTime(TimeId),
value int not null
)

insert into Fact

select DateId, TimeId, ROUND(RAND(CONVERT(varbinary, NEWID())) * 100, 2)
FROM DimDate, DimTime

A MDXconsulta que funciona corretamente é:

WITH
MEMBER MaxMember as MAX(TimeSet, [Measures].[Value])
MEMBER AvgOfMax as Avg(DateSet, MAX(TimeSet, [Measures].[Value]))
MEMBER StDevOfMax as StDev(DateSet, MAX(TimeSet, [Measures].[Value]))

SET DateSet as EXISTING [Dim Date].[Date Id].[Date Id]
SET TimeSet as EXISTING [Dim Time].[Time Id].[Time Id]

select
{
    [Measures].[Value], MaxMember, AvgOfMax, StDevOfMax
}
on 0,
{
    DateSet * TimeSet
} on 1

FROM 
(
    SELECT ({[Dim Date].[Date Id].&[20150101] : [Dim Date].[Date Id].&[20150103]}, {[Dim Time].[Time Id].&[1] : [Dim Time].[Time Id].&[4]})
    on 0
    FROM [Test Date Time Dimensions]
)

Ele exibe o resultado corretamente como:

Resultado correto

Observe que os itens incluídos no Eixo 1 não são usados e a consulta real retorna apenas uma linha. Eles são apenas para este exemplo.

A consulta que não funciona como esperado é:

WITH
MEMBER MaxMember as MAX(TimeSet, [Measures].[Value])
MEMBER AvgOfMax as Avg(DateSet, MAX(TimeSet, [Measures].[Value]))
MEMBER StDevOfMax as StDev(DateSet, MAX(TimeSet, [Measures].[Value]))

SET DateSet as EXISTING [Dim Date].[Date Id].[Date Id]
SET TimeSet as EXISTING [Dim Time].[Time Id].[Time Id]

select
{
    [Measures].[Value], MaxMember, AvgOfMax, StDevOfMax
}
on 0

,
{
    DateSet * TimeSet
} on 1

FROM 
(
    SELECT {({[Dim Date].[Date Id].&[20150101] : [Dim Date].[Date Id].&[20150102]} * {[Dim Time].[Time Id].&[1] : [Dim Time].[Time Id].&[4]})
    , ([Dim Date].[Date Id].&[20150103] * [Dim Time].[Time Id].&[3] : [Dim Time].[Time Id].&[4])}
    on 0
    FROM [Test Date Time Dimensions]
)

Aqui, excluí Horas 01:00e a coluna '02:00 from Date3 January 2015 . The maximum value (70) is at hour01:00 of3 January 2015 but is still being used to calculate Avg andStDev , andMax` está dando erro, conforme mostra a imagem:

Erro

Aqui, Média e Desvio Padrão estão incorretos.

Por favor, ajude-me a encontrar uma maneira de calcular corretamente os resultados neste segundo caso e explique por que Max está dando erro e Avgas StDevfunções ainda estão calculando, incluindo o valor máximo que não está incluído na Subselectcláusula filtrada.

Uma solução alternativa que não é ideal é adicionar uma nova DateTimedimensão que tenha Datee Time, e usar essa DateTimedimensão na Subselectcláusula e utilizar a Timedimensão existente para calcular Maxe Datedimensionar para obter média e desvio padrão, como no código abaixo :

Crie e preencha a DateTimedimensão:

create table DimDateTime
(
    DateTimeId int not null PRIMARY KEY,
    [Date] Date not null,
    [Time] Time not null 
)

insert into DimDateTime

select ((DateId * 100) + TimeId) DateTimeId, DimDate.Date, DimTime.Time
from DimDate, DimTime

alter table Fact add DateTimeId int References DimDateTime(DateTimeId)

update Fact set DateTimeId = ((DateId * 100) + TimeId)

A MDXconsulta retornando o resultado correto:

WITH
MEMBER MaxMember as MAX(TimeSet, [Measures].[Value])
MEMBER AvgOfMax as Avg(DateSet, MAX(TimeSet, [Measures].[Value]))
MEMBER StDevOfMax as StDev(DateSet, MAX(TimeSet, [Measures].[Value]))

SET DateSet as NONEMPTY ([Dim Date].[Date Id].[Date Id])
SET TimeSet as NONEMPTY ([Dim Time].[Time Id].[Time Id])

select
{
    [Measures].[Value], MaxMember, AvgOfMax, StDevOfMax
}
on 0,
{
    NONEMPTY(DateSet * TimeSet)
} on 1

FROM 
(
    SELECT ({[Dim Date Time].[Date Time Id].&[2015010101] : [Dim Date Time].[Date Time Id].&[2015010104],
            [Dim Date Time].[Date Time Id].&[2015010201] : [Dim Date Time].[Date Time Id].&[2015010204],
            [Dim Date Time].[Date Time Id].&[2015010303] : [Dim Date Time].[Date Time Id].&[2015010304]})
    on 0
    FROM [Test Date Time Dimensions]
)

Mas prefiro evitar a criação de uma nova dimensão, além das duas dimensões existentes contendo os mesmos dados. Em segundo lugar, quero evitar colocar os dados no aplicativo e calcular o resultado nos métodos da API. Então a preferência é fazer com que as funções Maxfuncionem corretamente.AvgStDev

Usando o SQLServer 2012.

1 respostas

Voted

SpeedBirdNine · Answer 1 · 2015-03-15T12:47:20+08:00

Tentei fazer isso no MDX, mas sem sucesso, portanto, tive que usar a função definida pelo usuário do SSAS para calcular o Avg de Max. O conjunto para calcular Avg( DateSet) transversalmente, conjunto para calcular Maxtransversalmente ( TimeSet) e um terceiro conjunto para conter os registros a serem excluídos na Maxoperação, são passados como parâmetros para a UDF.

O código básico é como (apresentando a funcionalidade Avg of Max codificada, excluindo a verificação de erros):

public static class AvgOfMaxMethods
    {
        public static double AvgOfMax(Set maxAcrossSet, Set avgAcrossSet, Set setToExclude, Expression measureExpression)
        {
            var setToExcludeQueryable = setToExclude.Tuples.OfType<Microsoft.AnalysisServices.AdomdServer.Tuple>();
            IList<double> maxMembers = new List<double>();
            foreach(var avgAcrossTuple in avgAcrossSet.Tuples)
            {
                var max = double.MinValue;
                foreach(var maxAcrossTuple in maxAcrossSet.Tuples)
                {
                    if (!setToExcludeQueryable.Any(tuple => tuple.Members[0].UniqueName.Equals(avgAcrossTuple.Members[0].UniqueName) && tuple.Members[1].UniqueName.Equals(maxAcrossTuple.Members[0].UniqueName)))
                    {
                        TupleBuilder tb = new TupleBuilder(avgAcrossTuple.Members[0]);
                        tb.Add(maxAcrossTuple.Members[0]);
                        var calculatedVal = measureExpression.Calculate(tb.ToTuple()).ToDouble();
                        max = calculatedVal > max ? calculatedVal : max;
                    }
                }
                if (!max.Equals(double.MinValue))
                {
                    maxMembers.Add(max);
                }
            }
            return maxMembers.Average();
        }
    }

E com isso a consulta se torna (mantendo o original Maxe StDevpara comparação):

WITH
MEMBER MaxMember as MAX(TimeSet, [Measures].[Value])
MEMBER AvgOfMax as Avg(DateSet, MAX(TimeSet, [Measures].[Value]))
MEMBER StDevOfMax as StDev(DateSet, MAX(TimeSet, [Measures].[Value]))
Member MaxFromUDF as UDFPoc.UDFPoc.AvgOfMaxMethods.AvgOfMax(TimeSet, DateSet, SetToExclude, [Measures].[Value])

SET DateSet as EXISTING [Dim Date].[Date Id].[Date Id]
SET TimeSet as EXISTING [Dim Time].[Time Id].[Time Id]
SET SetToExclude as {[Dim Date].[Date Id].&[20150103] * [Dim Time].[Time Id].&[1] : [Dim Time].[Time Id].&[2]}

select
{
    [Measures].[Value], MaxMember, AvgOfMax, StDevOfMax, MaxFromUDF
}
on 0,
{
    DateSet * TimeSet
} on 1

FROM 
(
    SELECT ({[Dim Date].[Date Id].&[20150101] : [Dim Date].[Date Id].&[20150103]}, {[Dim Time].[Time Id].&[1] : [Dim Time].[Time Id].&[4]})
    on 0
    FROM [Test Date Time Dimensions]
)

E os resultados se parecem (aqui as tuplas do conjunto excluído são destacadas em vermelho):

insira a descrição da imagem aqui

Como pode ser visto, a UDF exclui os membros de cada tupla no SettoExcludeconjunto ao calcular Maxe Average. Agora está sendo calculada a média correta considerando o conjunto excluído, como pode ser visto na imagem. Da mesma forma StDevpode ser calculado desta maneira.

Veja isso como um guia para criar funções definidas pelo usuário do SSAS.

As funções MDX Max, Average e StDev não fornecem resultados corretos

conectar ao servidor PostgreSQL: FATAL: nenhuma entrada pg_hba.conf para o host

Como fazer a saída do sqlplus aparecer em uma linha?

Selecione qual tem data máxima ou data mais recente

Como faço para listar todos os esquemas no PostgreSQL?

Listar todas as colunas de uma tabela especificada

Como usar o sqlplus para se conectar a um banco de dados Oracle localizado em outro host sem modificar meu próprio tnsnames.ora

Como você mysqldump tabela (s) específica (s)?

Listar os privilégios do banco de dados usando o psql

Como inserir valores em uma tabela de uma consulta de seleção no PostgreSQL?

Como faço para listar todos os bancos de dados e tabelas usando o psql?

As funções MDX Max, Average e StDev não fornecem resultados corretos

1 respostas

relate perguntas