Início / dba / Perguntas / 20099
Accepted
AmmarR
Asked: 2012-06-29 04:09:29 +0800 CST

Definir dinamicamente um intervalo em uma dimensão

  • 772

Tenho um problema que enfrento toda vez que decido construir um cubo e ainda não encontrei uma maneira de superá-lo.

A questão é como permitir que o usuário defina uma gama de coisas automaticamente sem ter a necessidade de codificá-las na dimensão. Vou explicar meu problema em um exemplo.

Tenho uma tabela chamada Clientes :

Estrutura da Tabela

esses são os dados da tabela:

Tabela com dados

Desejo exibir os dados em um estilo dinâmico e agrupar o Salário e a Idade em intervalos definidos, como abaixo:

Tabela com Dados com Intervalo Definido

Eu escrevi este script e defini os intervalos:

SELECT [CustId]
      ,[CustName]
      ,[Age]
      ,[Salary]
      ,[SalaryRange] = case
        when cast(salary as float) <= 500 then
            '0 - 500'
        when cast(salary as float) between 501 and 1000 then
            '501 - 1000'
        when cast(salary as float) between 1001 and 2000 then
            '1001 - 2000'
        when cast(salary as float) > 2000 then
            '2001+'
        end,
        [AgeRange] = case
        when cast(age as float) < 15 then
            'below 15'
        when cast(age as float) between 15 and 19 then
            '15 - 19'
        when cast(age as float) between 20 and 29 then
            '20 - 29'               
        when cast(age as float) between 30 and 39 then
            '30 - 39'
        when cast(age as float) >= 40 then
            '40+'
        end
  FROM [Customers]
GO

Meus intervalos são codificados e definidos. Quando copio os dados para o Excel e os visualizo em uma tabela dinâmica, aparece como abaixo:

Dados na tabela dinâmica

Meu problema é que quero criar um cubo convertendo a tabela Customers em uma tabela de fatos e criar tabelas de 2 dimensões SalaryDim & AgeDim .

A tabela SalaryDim tem 2 colunas ( SalaryKey,SalaryRange ) e a tabela AgeDim é semelhante ( ageKey,AgeRange ). Minha tabela de fatos do cliente tem:

Customer
[CustId]
[CustName]
[AgeKey] --> foreign Key to AgeDim
[Salarykey] --> foreign Key to SalaryDim

Ainda tenho que definir meus intervalos dentro dessas dimensões. Sempre que conecto um pivô do Excel ao meu cubo, só consigo ver esses intervalos definidos codificados.

Minha pergunta é como definir intervalos dinamicamente da tabela dinâmica diretamente, sem criar as dimensões do intervalo como AgeDim e SalaryDim . Não quero ficar preso apenas aos intervalos definidos na dimensão.

Sem intervalo definido

O intervalo definido é '0-25' , '26-30' , '31-50'. Talvez eu queira alterá-lo para '0-20', '21-31' , '32-42' e assim por diante, e os usuários solicitam intervalos diferentes sempre.

Toda vez que eu mudo, tenho que mudar a dimensão. Como posso melhorar esse processo?

Seria ótimo ter uma solução implementada no cubo, para que qualquer ferramenta cliente de BI que se conectasse ao cubo pudesse definir os intervalos, mas não me importaria se houvesse uma boa maneira de usar apenas o Excel.

sql-server sql-server-2008

3 respostas

  1. Best Answer

    COMO FAZER ISSO COM T-SQL:

    Conforme solicitado, esta é uma alternativa à minha resposta anterior, que mostrava como fazer isso por usuário com o Excel. Esta resposta mostra como fazer a mesma coisa compartilhada/centralmente usando o T-SQL. Eu não sei como fazer Cubes, MDX ou SSAS para isso, então talvez Benoit ou alguém que saiba pode postar seu equivalente ...

    1. Adicionar tabela e exibição SQL SalaryRanges

    Crie uma nova tabela chamada "SalaryRangeData" com o seguinte comando:

    Create Table SalaryRangeData(MinVal INT Primary Key)

    Adicione colunas calculadas agrupando-as em uma exibição com este comando:

    CREATE VIEW SalaryRanges As
WITH
  cteSequence As
(
    Select  MinVal,
            ROW_NUMBER() OVER(Order By MinVal ASC) As Sequence
    From    SalaryRangeData
)
SELECT 
    D.Sequence,
    D.MinVal,
    COALESCE(N.MinVal - 1, 2147483645)  As MaxVal,
    CAST(D.MinVal As Varchar(32))
    + COALESCE(' - ' + CAST(N.MinVal - 1 As Varchar(32)), '+')
                        As RangeVals
FROM        cteSequence As D 
LEFT JOIN   cteSequence As N ON N.Sequence = D.Sequence + 1

    Clique com o botão direito do mouse na tabela no SSMS e selecione "Editar as 200 principais linhas". Em seguida, insira os seguintes valores nas células MinVal: 0, 501, 1001 e 2001 (a ordem não importa para o SQL Server, ela será criada para nós). Feche o editor de linha da tabela e faça um SELECT * FROM SalaryRangespara ver todas as linhas e informações de intervalo.

    2. Adicionar tabela e visualização SQL AgeRanges

    Siga exatamente as mesmas etapas do item 1 acima, exceto substituir todas as ocorrências de "Salário" por "Idade". Isso deve criar a tabela "AgeRangeData" e a exibição "AgeRanges".

    Insira os seguintes valores na coluna AgeRangeData [MinVal]: 0, 15, 20, 30 e 40.

    3. Adicione intervalos aos dados

    Substitua sua instrução SELECT por expressões CASE para recuperar os dados e intervalos com o seguinte:

    SELECT [CustId]
      ,[CustName]
      ,[Age]
      ,[Salary]
      ,[SalaryRange] = (
            Select RangeVals From SalaryRanges
            Where [Salary] Between MinVal And MaxVal)
      ,[AgeRange] = (
            Select RangeVals From AgeRanges
            Where [Age] Between MinVal And MaxVal)
  FROM [Customers]

    4. Todo o resto, o mesmo de agora

    A partir daqui, basta fazer tudo da mesma forma que você está atualmente. Todos os intervalos devem aparecer em sua Tabela Dinâmica como estão atualmente.

    5. Teste a magia

    Vá para o editor de linhas da tabela SalaryRangeData no SSMS novamente e exclua as linhas existentes e insira os seguintes valores: 0, 101, 201, 301, ... 2001 (novamente, a ordem não importa para a solução T-SQL) . Volte para sua tabela dinâmica e atualize os dados. E assim como a solução do Excel, os intervalos da tabela dinâmica devem ser alterados automaticamente.

    Adição

    COMO ADICIONAR A UM CUBO:

    1. Crie uma Visualização

    CREATE VIEW CustomerView As
SELECT [CustId]
      ,[CustName]
      ,[Age]
      ,[Salary]
      ,[SalaryRange] = (
            Select RangeVals From SalaryRanges
            Where [Salary] Between MinVal And MaxVal)
      ,[AgeRange] = (
            Select RangeVals From AgeRanges
            Where [Age] Between MinVal And MaxVal)
  FROM [Customers]

    1. Crie um projeto de BI no Visual Studio e adicione oCustomerView

    Conecte-se ao banco de dados e adicione a CustomerViewexibição na Data Source Viewstabela de fatos

    Exibições da fonte de dados

    2. Crie um cubo e defina medidas e dimensões

    só precisamos de customerId como uma medida para a contagem de clientes e teremos a mesma tabela de fatos como uma dimensão

    Medidas

    Dimensões

    3. Adicione Atributos à Dimensão

    Adicionar intervalos como atributos à dimensão

    4. Conecte-se ao Cube do Excel

    Adicionar origem SSAS ao Excel

    Selecione o cubo

    5. Visualize os dados do cubo no Excel

    Exibir o cubo no Excel

    6. Para quaisquer alterações nos intervalos, basta reprocessar a dimensão e o cubo

    se precisar alterar os Ranges, altere os dados no SalaryRangeDatae AgeRangeDatae depois é só reprocessar as dimensões e o cubo

    • 12

  2. COMO FAZER ISSO COM O EXCEL

    Aqui está como eu faria isso no Excel ...

    1. Adicionar tabela Excel SalaryRanges

    Insira uma nova planilha, chame-a de "Faixas Salariais". Na primeira linha, adicione os cabeçalhos de texto "Min", "Max" e "Range" nessa ordem (devem ser as células A1, A2, A3, respectivamente).

    Na célula B2 adicione a seguinte fórmula:

    =IF(A2="","",IF(A3="","+",A3-1))

    Na célula C2 adicione esta fórmula:

    =IF(B2="","",A2 & IF(B2="+",""," - ") & B2)

    Preencha automaticamente essas duas fórmulas nas colunas B e C para o número máximo de linhas que você pode precisar (digamos 30).

    Em seguida, selecione toda a faixa (A1..C31). Acesse a guia Inserir e clique no botão Tabela para alterar esse intervalo para uma Tabela do Excel (anteriormente chamada de "Listas"). Na guia Table Tools Design, altere o nome dessa tabela para "SalaryRanges".

    Agora, vá para a célula A2 na coluna Min e digite "0", "501" em A3, "1001" na célula A4 e finalmente "2001" na célula A5. Observe que, ao fazer isso, as colunas MAx e Range são preenchidas automaticamente.

    2. Adicionar tabela Excel AgeRanges

    Agora faça outra nova planilha chamada "Intervalos de idade" e siga exatamente as mesmas etapas do item 1 acima, exceto chamar esta tabela de "Intervalos de idade" e na coluna Min preencher as células A2 a A6 com 0, 15, 20, 30 e 40, em ordem. Novamente, os valores Max e Range devem ser preenchidos automaticamente à medida que você avança.

    3. Obtenha os dados

    Obtenha os dados do banco de dados em sua pasta de trabalho do Excel como você fez antes (não faça a tabela dinâmica ainda, fazemos isso abaixo), exceto que você deve remover as colunas de função de caso AgeRange e SalaryRange.

    4. Adicione as colunas Salário e Faixa etária aos seus Dados

    Na planilha onde estão seus dados, adicione uma coluna "SalaryRange" e "AgeRange". Na coluna SalaryRange, preencha automaticamente a seguinte fórmula (supõe que "D" seja a coluna Salary):

    =LOOKUP(D2,SalaryRanges)

    E preencha automaticamente esta fórmula para a coluna AgeRange (supondo que "C" seja a coluna Age):

    =LOOKUP(C2,AgeRanges)

    5. Faça sua Tabela Dinâmica

    Faça isso exatamente como você fez antes. Observe que os valores/rótulos da faixa de idade e salário correspondem às faixas que você escolher.

    6. Teste a magia

    Agora a parte divertida. Vá para a planilha SalaryRanges e insira novamente a coluna Min, começando em 0, depois 101, 201, 301, ... 2001. Volte para sua tabela dinâmica e apenas atualize-a. Shazaam!

    Devo mencionar que é claro que você também pode obter o mesmo efeito colocando as tabelas em SQL e alterando sua instrução SELECT para fazer os LOOKUP(..)s como subconsultas (um pouco confuso por causa da correspondência de intervalo, mas definitivamente capaz). A razão pela qual fiz dessa maneira (no Excel) é

    1. Alterar os intervalos é um pouco mais fácil para a maioria das pessoas. Mesmo para DBA's e desenvolvedores SQL (como nós), esse caminho é um pouco mais fácil porque é mais próximo da UI/resultados.
    2. Isso permite que seus usuários alterem seus próprios intervalos sem ter que incomodá-lo. (uma GRANDE vantagem na minha vida)
    3. Isso também permite que cada usuário defina seus próprios intervalos.

    No entanto, às vezes é indesejável que os usuários definam seus próprios intervalos. Se for esse o seu caso, ficarei feliz em demonstrar como fazer isso centralmente, em SQL.

    • 8

  3. Com a linguagem MDX, você pode criar membros personalizados que definirão os intervalos. A seguinte expressão definiu um membro calculado que representa todos os salários entre 501 e 1000:

    MEMBER [Salary].[between_500_and_1000] AS Aggregate(Filter([Salary].Members, [Salary].CurrentMember.MemberValue > 500 AND [Salary].CurrentMember.MemberValue <= 1000))

    Você pode fazer o mesmo com a dimensão idade:

    MEMBER [Age].[between_0_and_25] AS Aggregate(Filter([Age].Members, [Age].CurrentMember.MemberValue <= 25))

    Este artigo explica como adicionar esses membros calculados no Excel (consulte a seção ' Criando membros/medidas e conjuntos calculados no Excel 2007 OLAP PivotTables '). Infelizmente, não há interface do usuário no Excel para isso. No entanto, você pode encontrar clientes de BI que suportam a linguagem MDX , que permitem definir seus intervalos nas consultas.

    • 5

relate perguntas