Динамическое определение диапазона в измерении

У меня есть проблема, с которой я сталкиваюсь каждый раз, когда я решаю построить куб, и я еще не нашел способ его преодолеть.

Проблема заключается в том, как разрешить пользователю определять диапазон вещей автоматически, не требуя жесткого кодирования их в измерении. Я объясню свою проблему в примере.

У меня есть таблица под названием Клиенты :

Структура таблицы

это данные в таблице:

Таблица с данными

Я хочу отображать данные в сводном стиле и группировать Зарплата и Возраст в определенных диапазонах, как показано ниже:

Таблица с данными с заданным диапазоном

Я написал этот скрипт и определил диапазоны:

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

Мои диапазоны жестко закодированы и определены. Когда я копирую данные в Excel и просматриваю их в сводной таблице, это выглядит так:

Данные в сводной таблице

Моя проблема в том, что я хочу создать куб, преобразовывая таблицу Customers в таблицу фактов и создавая две таблицы размеров SalaryDim & AgeDim .

Таблица SalaryDim имеет 2 столбца ( SalaryKey, SalaryRange ), а таблица AgeDim аналогична ( ageKey, AgeRange EM>). В таблице фактов Customer есть

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

Мне еще нужно определить диапазоны внутри этих измерений. Каждый раз, когда я соединяю вершину Excel с моим кубом, я могу видеть только эти жестко определенные диапазоны.

Мой вопрос заключается в том, как напрямую определять диапазоны динамически из сводной таблицы, не создавая размеры диапазона, такие как AgeDim и SalaryDim . Я не хочу только зацикливаться на диапазонах, определенных в измерении.

No Range Defined

Определенный диапазон: '0-25', '26 -30 ', '31-50'. Я мог бы изменить его на «0-20», «21 -31», «32 -42» и т. Д., И пользователи каждый раз запрашивают разные диапазоны.

Каждый раз, когда я меняю его, я должен изменить измерение. Как я могу улучшить этот процесс?

Было бы здорово иметь решение, реализованное в кубе, так что любой инструмент клиента BI, который соединяется с кубом, может определять диапазоны, но я не против, если есть хороший способ использовать только Excel.

18 голосов | спросил AmmarR 28 J0000006Europe/Moscow 2012, 16:09:29

3 ответа


12

КАК ДЕЛАТЬ С Т-SQL:

В соответствии с запросом это альтернатива моему предыдущему ответу, который показал, как сделать это для каждого пользователя с помощью Excel. Этот ответ показывает, как сделать то же самое, что и общее /централизованное использование T-SQL. Я не знаю, как это сделать для Cubes, MDX или SSAS, так что, возможно, Бенуа или кто-то, кто знает, что может опубликовать его эквивалент ...

1. Добавить таблицу и представление таблицы SalaryRanges

Создайте новую таблицу под названием «SalaryRangeData» со следующей командой:

Create Table SalaryRangeData(MinVal INT Primary Key)

Добавьте вычисленные столбцы, обернув его в представление с помощью этой команды:

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

Щелкните правой кнопкой мыши на таблице в SSMS и выберите «Edit Top 200 Rows». Затем введите следующие значения в ячейки MinVal: 0, 501, 1001 и 2001 (заказ не имеет значения для SQL Server, он создаст его для нас). Закройте редактор строк таблицы и выполните SELECT * FROM SalaryRanges, чтобы просмотреть все строки и информацию о диапазоне.

2. Добавить таблицу и представление таблицы AgeRanges

Выполняйте те же самые шаги, что и в # 1 выше, за исключением замены всех вхождений «Зарплата» на «Возраст». Это должно сделать таблицу «AgeRangeData» и представление «AgeRanges».

Введите следующие значения в столбец AgeRangeData [MinVal]: 0, 15, 20, 30 и 40.

3. Добавить диапазоны в данные

Замените оператор SELECT выражениями CASE для извлечения данных и диапазонов со следующим:

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. Все остальное, то же, что и сейчас

Отныне просто делайте все так же, как сейчас. Диапазоны должны отображаться в сводной таблице, как они это делают в настоящее время.

5. Тест Магия

Перейдите в редактор строк таблицы SalaryRangeData в SSMS еще раз и удалите существующие строки, а затем вставьте следующие значения: 0, 101, 201, 301, ... 2001 (опять же, порядок не имеет значения для T- SQL). Вернитесь к сводной таблице и обновите данные. И точно так же, как решение Excel, диапазоны сводной таблицы должны быть автоматически изменены.


Добавление

КАК ДОБАВИТЬ К КУБЕ:

1. Создать представление

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. Создайте проект BI в Visual Studio и добавьте CustomerView

Подключитесь к базе данных и добавьте представление CustomerView в Data Source Views в таблицу фактов

Представления источника данных

2. Создать куб и определить меру & Размер

нам нужен только customerId, как показатель количества клиентов и будет иметь ту же таблицу фактов, что и размер

Меры

Размеры

3.Добавить атрибуты в размер

Добавить диапазоны как атрибуты в размер

4. Подключение к кубу из Excel

Добавить источник SSAS в Excel

Выбрать куб

5. Просмотр данных куба в Excel

Просмотр куба в Excel

6. для любых изменений в диапазонах просто перерабатывает Dimension & куб

, если вам нужно изменить диапазоны, изменить данные в SalaryRangeData и AgeRangeData, а затем просто переработать размеры и куб

ответил RBarryYoung 3 J000000Tuesday12 2012, 18:39:23
8

КАК ДЕЛАТЬ С EXCEL

Вот как я буду делать это в Excel ...

1. Добавьте таблицу Excel SalaryRanges

Вставьте новый лист, назовите его «Зарплаты». В строке 1 добавить текстовые заголовки «Min», «Max» и «Range» в этом порядке (должны быть ячейки A1, A2, A3, соответственно).

В ячейке B2 добавьте следующую формулу:

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

В ячейке C2 добавьте эту формулу:

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

Заполните эти две формулы по столбцам B и C для максимального количества строк, которые вам могут понадобиться (скажем, 30).

Затем выберите весь диапазон (A1..C31). Перейдите на вкладку «Вставка» и нажмите кнопку «Таблица», чтобы изменить этот диапазон в таблице Excel (они назывались «Списки»). На вкладке «Дизайн инструментов таблицы» измените имя этой таблицы на «SalaryRanges».

Теперь перейдите в ячейку A2 в столбце Min и введите «0», «501» в A3, «1001» в ячейке A4 и, наконец, «2001» в ячейке A5. Обратите внимание, что при этом столбцы MAx и Range автоматически заполняются.

2. Добавить таблицу показателей Excel для AgeRanges

Теперь создайте еще один новый лист с названием «Диапазоны возраста» и выполните те же действия, что и в предыдущем примере 1, за исключением вызова этой таблицы «AgeRanges» и в ячейках с ячейками «Min» заполняйте A2-A6 с помощью 0, 15, 20, 30 и 40, по порядку. Опять же, значения Max и Range должны автоматически заполняться, когда вы идете.

3. Получить данные

Получите данные из базы данных в книгу Excel так же, как и раньше (не делайте сводную таблицу еще, мы делаем это ниже), за исключением того, что вы должны удалять столбцы функции случая AgeRange и SalaryRange.

4. Добавьте столбцы «Зарплата и возраст» в свои данные

В листе, где указаны ваши данные, добавьте столбец «SalaryRange» и «AgeRange». В столбце SalaryRange выполните автозаполнение следующей формулы (предполагается, что «D» - столбец «Заработная плата»):

=LOOKUP(D2,SalaryRanges)

И закрепите эту формулу в столбце AgeRange (при условии, что «C» - столбец «Возраст»):

=LOOKUP(C2,AgeRanges)

5. Создайте сводную таблицу

Сделайте это так же, как и раньше. Обратите внимание, что значения /метки диапазона возраста и зарплаты соответствуют диапазонам, которые вы выбираете.

6. Тест Магия

Теперь самое интересное. Перейдите на рабочий лист SalaryRanges и снова введите столбец Min, начиная с 0, затем 101, 201, 301, ... 2001. Вернитесь к сводной таблице и просто обновите ее. Shazaam!


Я должен упомянуть, что, конечно, вы также можете добиться такого же эффекта, поместив Таблицы в SQL и изменив инструкцию SELECT, чтобы сделать LOOKUP (..) s как подзапрос (немного грязный из-за соответствия диапазона, но безусловно, способный). Причина, по которой я сделал это таким образом (в Excel), -

  1. Изменение диапазонов для большинства людей немного проще. Даже для разработчиков DBA и SQL (как и мы) этот способ немного проще, потому что он ближе к результатам пользовательского интерфейса.
  2. Это позволяет вашим пользователям изменять собственные диапазоны, не беспокоя вас. (БОЛЬШОЙ плюс в моей жизни)
  3. Это также позволяет каждому пользователю определять свои собственные диапазоны.

Однако иногда на самом деле нежелательно иметь пользователей, определяющих собственные диапазоны. Если это так, я буду рад продемонстрировать, как сделать это централизованно, вместо SQL.

ответил RBarryYoung 2 J000000Monday12 2012, 21:14:10
5

С языком MDX вы можете создавать пользовательские элементы, которые будут определять диапазоны. В следующем выражении определяется вычисляемый элемент, который представляет все зарплаты между 501 и 1000:

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

Вы можете сделать то же самое с возрастным измерением:

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

Этот статья объясняет, как добавить тезисы вычисляемых членов в Excel (см. « Создание вычисляемых членов /мер и наборов в разделе Excel PivotTables в Excel 2007 »). К сожалению, для этого нет интерфейса пользователя в Excel. Тем не менее вы можете найти клиентов BI, которые поддерживают язык MDX , который позволяет определять ваши диапазоны в запросах.

ответил Benoit 2 J000000Monday12 2012, 01:15:34

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132