По поводу SQL - согласен, вот такие есть недостатки SQL, что появляются идеи улучшений SQL кода, уже без выбора диалекта SQL, СУБД, её версии (версия до 22.02.2022 - такая версия часто рекомендуется к использованию, или после 22.02.2022, и т.д.) - достаточно много нюансов, которые отвлекают от решения основных задач, от бизнес-логики. Например, у меня встречалось, что версия СУБД до 22.02.2022 не применяет WHERE сразу до JOIN при наличии нескольких JOIN, т.е. выделение подзапросов с WHERE приводит к более эффективному плану выполнения SQL запроса в версии СУБД до 22.02.2022, но в последней версии СУБД это исправлено и можно не использовать подзапросы с WHERE.
DAX составляет суть Visiology в том смысле, что DAX пишется не только через UI, но и генерируется компонентами Visiology (фильтрами и другими виджетами), поддерживаются меры и вложенные меры, меры для всего набора данных и меры для одного дашборда и т.д.
Поэтому Visiology дает свободу в реализации сложных мер и использовании их в фильтрах, других виджетах и дашбордах.
Кстати, рекомендуют сначала сделать базовую метрику
Хорошая практика, Вы описываете дополнительные преимущества DAX с точки зрения архитектуры BI решения, схемы данных и визуализаций. В первом примере статьи цель была в сравнении решений задачи на DAX и SQL, поэтому для упрощения сравнения DAX был написан без меры, в третьем примере рассмотрен уже DAX с мерой.
Описанный Вами подход к расчету мер может быть эффективно реализован в Visiology.
FILTER внутри CALCULATE в этом случае генерируется автоматически
Поскольку в перечисленных случаях CALCULATE без FILTER является синтаксическим сахаром для CALCULATE с FILTER (более полный список эквивалентных DAX можно найти, например, здесь, или в других источниках), то использование в Visiology CALCULATE с FILTER не накладывает ограничений для этих случаев.
SUM лучше чем MAX отвечает на поставленный вопрос "Кто скупил дешевую колбасу?"
Справедливо и выглядит как дополнительный критерий, по объемам покупок :)
Да, интересно, даже есть об этом материал, но обычно DISTINCTCOUNT быстрее
There may be reports where the # Customer SUMX measure is way faster than the classic # Customer measure based on DISTINCTCOUNT, though usually the opposite is true.
Отличий много, хочется отметить, что DAX работает практически со всеми источниками данных, в отличие от диалекта SQL, который применяется только в рамках одной базы данных. Например, лишь небольшая часть окна импорта данных в Power BI с источниками данных выглядит так:
Многообразие источников данных для DAX в Power BI
В общем и целом, DAX и любой диалект SQL - инструменты для решения разных задач, но в области Business Intelligence, дашбордов и анализа данных у DAX есть свои преимущества над диалектами SQL, например в скорости написания запросов, лаконичности кода запросов, простоте анализа разнородных источников данных и т.д.
Я сам работаю и работал над платными BI системами, например, Visiology, MercerInsight, также были проекты с SAP BI, и самописные BI системы на стеке .NET + Elasticsearch + RabbitMQ, и поэтому в бесплатных BI продуктах, если честно, меньше ориентируюсь.
Здесь ответ просто для справки, если у кого-то, например, нет времени спросить AI, да и пора отпусков, может не у всех удобный интернет или VPN, чтобы спросить AI. Например, я сам этот опенсорсный список детально не смотрел, на пляже, если будет минутка, можно что-то погуглить по очереди.
Тут же мой прошлый комментарий и не звучит как 100% рекомендация, он с долей сомнения и для справки.
Плюс статья, в первую очередь, про выбор KPI, чтобы получить об этом представление.
И по поводу BI системы или инструментов - выбор вроде есть, но очень часто его на самом нет. Например, до 2022 года SAP BI был практически стандартом для компаний уровня Газпрома, крупных металлургических компаний и т.д., у которых есть акции на рынке и которые проходят аудиторскую проверку и публикуют свою отчетность и информацию для инвесторов. Или, например, амбициозная IT компания может считать KPI своими силами, и выбор инструментов уже может быть в рамках стека приоритетного языка программирования компании, а не опенсорсных систем. У российских гос. компаний свои требования к безопасности BI систем, какие-то опенсорсные системы могут им не удовлятворять.
Т.е. при выборе BI системы нужно учитывать требования рынка (например, информация для акционеров), учитывать структуру компании (например, группа компаний, и требуется ли консолидированная отчетность), сколько видов отчетности требуется (например, отченость по международным стандартам МСФО и РСБУ), учитывать ограничения безопасности для гос. компаний, учитывать возможности IT подразделения самой компании, учитывать бюджет, сроки, успехи конкурентов на рынке и т.д. Если применить все эти фильтры, то список BI систем сильно сокращается, и для многих компаний вовсе исключаются опенсорсные BI системы.
UPDATE: по поводу Apache Superset, например, посмотрел демо видео на главной странице, там как-то не очень заметно какого-то сложного drill down, итогов-подытогов, таблиц с группировкой с +/-, сводных таблиц, сложных фильтров и т.д., как-то негусто на первый взгляд, на минималках. Ещё условно такие критерии есть.
Apache Superset: платформа визуализации и исследования данных с открытым исходным кодом, разработанная как визуальная, интуитивно понятная и интерактивная.
Metabase: простой и легкий инструмент бизнес-аналитики, который позволяет пользователям легко визуализировать и анализировать данные.
Redash: инструмент бизнес-аналитики с открытым исходным кодом, который интегрируется с различными источниками данных и позволяет пользователям создавать визуализации и информационные панели.
Apache Druid: высокопроизводительная аналитическая база данных в режиме реального времени, предназначенная для быстрых специальных запросов к большим наборам данных.
KNIME: платформа анализа данных с открытым исходным кодом, которая позволяет пользователям создавать визуальные рабочие процессы для обработки, анализа и визуализации данных.
Pentaho: Комплексная платформа бизнес-аналитики с открытым исходным кодом, которая предлагает интеграцию данных, интеллектуальный анализ данных, отчеты, информационные панели и многое другое.
BIRT (Инструменты бизнес-аналитики и отчетности): проект программного обеспечения с открытым исходным кодом, который позволяет пользователям создавать визуализации данных, отчеты и информационные панели.
Без ограничений по a1 в T никак, без ограничений по a1 даже INNER JOIN наоборот превратится в CROSS JOIN:
SELECT T_1.a1, T_2.a1 FROM T AS T_1 INNER JOIN T AS T_2 ON T_1.a1 = T_2.a1
Ограничения по a1 в T могут быть разные (уникальность, первичный ключ, уникальность на основе данных в таблице T, но без формальных ограничений уникальности на уровне ClickHouse), и связь T_1.a1 и T_2.a1 может быть условно через третьи таблицы, каждый случай нужно рассматривать отдельно, но условие T_1.a1 = T_2.a1 по крайней мере должно работать (обеспечивать связь один к одному) на основе какого-то набора данных, чтобы дальше исследовать проблему.
Согласен, что для произвольного запроса убрать CROSS JOIN невозможно, но в статье рассматриваются три частных случая и есть условия их применимости, не предлагается выкинуть любой CROSS JOIN. Такое впечатление, что в комментарии не дубликаты имеются в виду, а декартово произведение, полный перебор. Три случая в статье относятся к особым видам запросов, где действительно можно убрать CROSS JOIN. Здесь я проиллюстрировал два запроса: первый попадает под один из описанных случаев, второй нет, и так и остаётся CROSS JOIN.
Кроме того, в реляционной алгебре нет дубликатов (т.к. идёт работа со множествами), но декартово произведение всё равно используется, т.е. декартово произведение без дубликатов (другого декартового произведения условно и нет в реляционной алгебре) и CROSS JOIN без дубликатов как минимум не лишены смысла, поэтому с учетом того, что проектов и кейсов много, и здесь наверно тоже какие-то частные случаи имеются в виду, и этот кейс не противоречит кейсам из статьи
CROSS JOIN, которые я встречал, именно для того и нужны, чтобы произвести дубликаты
Согласен, но и на практике при ручном написании запросов такие случаи тоже встречаются (хоть и в виде диких ляпов).
Эти случаи могут быть актуальными, когда SQL генерируется кодом (.NET, Java, Go и т.д.) для конкретного UI или отчета, т.е. не вручную. Во всех трех случаях для таблицы с 1 млн записей условно UI корректный (например, сводная таблица), или отчет условно корректный, т.е. их можно получить не за 1 млн × 1 млн операций, а за разумное число операций, условно 1 млн, но из-за CROSS JOIN получить отчет невозможно. Т.е. это больше для случаев, когда SQL генерируется не вручную и чинится тоже не вручную.
Рассмотренные случаи помогают что-то исправить в CROSS JOIN (удалить его) без значительного вмешательства в проект, такого, как:
ограничить число записей в запросе для одной или обеих таблиц в CROSS JOIN (например, LIMIT, LIMIT BY, topK в ClickHouse, или обычный GROUP BY - если возможно)
добавление паджинации для одной или обеих таблиц из запроса (если возможно)
условное упрощение сводной таблицы до таблицы размером 4 × 4 и выполнение 16 отдельных запросов для каждой ячейки (может быть актуально, например, для таблиц по кварталам или месяцам, при возможности можно выполнять запросы параллельно)
добавление или изменение фильтров для сводной таблицы
отказ от сводной таблицы как условно последнее средство, замена одного отчета несколькими и т.д., т.е. пересмотр бизнес-логики
Согласен, забыл в том случае дополнить, что a1 в T даже должно быть уникальным, ненулевым, в общем, ключом, иначе совсем не получится JOIN по ON T_1.a1 = T_2.a1;
Согласен, что разные результаты для SELECT T_1.a1, T_1.a1 FROM T AS T_1 CROSS JOIN T AS T_2; и SELECT a1, a1 FROM T;, пояснение "возвращают одинаковые результаты с точностью до дубликатов (которые можно удалить с помощью GROUP BY)" может выглядеть неоднозначно, в playground приведён пример
SELECT T_1.a1, T_1.a1 FROM T AS T_1 CROSS JOIN T AS T_2 GROUP BY T_1.a1, T_1.a1;
SELECT a1, a1 FROM T;
Он корректен, если a1 - это ключевое уникальное поле, для произвольного a1 корректно только такое:
SELECT T_1.a1, T_1.a1 FROM T AS T_1 CROSS JOIN T AS T_2 GROUP BY T_1.a1, T_1.a1;
SELECT a1, a1 FROM T GROUP BY a1, a1;
Согласен, что с точки зрения SQL это можно назвать иначе, "оптимизация CROSS JOIN" - для упрощения, чтобы не писать условно "оптимизация производительности SQL запроса путём удаления CROSS JOIN и замены на эквивалентный SQL запрос с другими типами JOIN (или без них) и дополнительными ограничениями, который возвращает те же результаты запроса"
UPDATE: Ошибся веткой
Спасибо за отличные аргументы в копилку DAX :)
По поводу SQL - согласен, вот такие есть недостатки SQL, что появляются идеи улучшений SQL кода, уже без выбора диалекта SQL, СУБД, её версии (версия до 22.02.2022 - такая версия часто рекомендуется к использованию, или после 22.02.2022, и т.д.) - достаточно много нюансов, которые отвлекают от решения основных задач, от бизнес-логики. Например, у меня встречалось, что версия СУБД до 22.02.2022 не применяет WHERE сразу до JOIN при наличии нескольких JOIN, т.е. выделение подзапросов с WHERE приводит к более эффективному плану выполнения SQL запроса в версии СУБД до 22.02.2022, но в последней версии СУБД это исправлено и можно не использовать подзапросы с WHERE.
Спасибо за подробную обратную связь.
DAX составляет суть Visiology в том смысле, что DAX пишется не только через UI, но и генерируется компонентами Visiology (фильтрами и другими виджетами), поддерживаются меры и вложенные меры, меры для всего набора данных и меры для одного дашборда и т.д.
Поэтому Visiology дает свободу в реализации сложных мер и использовании их в фильтрах, других виджетах и дашбордах.
Хорошая практика, Вы описываете дополнительные преимущества DAX с точки зрения архитектуры BI решения, схемы данных и визуализаций. В первом примере статьи цель была в сравнении решений задачи на DAX и SQL, поэтому для упрощения сравнения DAX был написан без меры, в третьем примере рассмотрен уже DAX с мерой.
Описанный Вами подход к расчету мер может быть эффективно реализован в Visiology.
Поскольку в перечисленных случаях CALCULATE без FILTER является синтаксическим сахаром для CALCULATE с FILTER (более полный список эквивалентных DAX можно найти, например, здесь, или в других источниках), то использование в Visiology CALCULATE с FILTER не накладывает ограничений для этих случаев.
Справедливо и выглядит как дополнительный критерий, по объемам покупок :)
Да, интересно, даже есть об этом материал, но обычно DISTINCTCOUNT быстрее
Спасибо, да, баги - это другая сторона ClickHouse :)
Интересно, что в русской документации не упоминается DEPRICATED, только warning
В английской документации есть DEPRICATED
Да, Firebird через ODBC
Отличий много, хочется отметить, что DAX работает практически со всеми источниками данных, в отличие от диалекта SQL, который применяется только в рамках одной базы данных. Например, лишь небольшая часть окна импорта данных в Power BI с источниками данных выглядит так:
В общем и целом, DAX и любой диалект SQL - инструменты для решения разных задач, но в области Business Intelligence, дашбордов и анализа данных у DAX есть свои преимущества над диалектами SQL, например в скорости написания запросов, лаконичности кода запросов, простоте анализа разнородных источников данных и т.д.
Я сам работаю и работал над платными BI системами, например, Visiology, MercerInsight, также были проекты с SAP BI, и самописные BI системы на стеке .NET + Elasticsearch + RabbitMQ, и поэтому в бесплатных BI продуктах, если честно, меньше ориентируюсь.
Здесь ответ просто для справки, если у кого-то, например, нет времени спросить AI, да и пора отпусков, может не у всех удобный интернет или VPN, чтобы спросить AI. Например, я сам этот опенсорсный список детально не смотрел, на пляже, если будет минутка, можно что-то погуглить по очереди.
Тут же мой прошлый комментарий и не звучит как 100% рекомендация, он с долей сомнения и для справки.
Плюс статья, в первую очередь, про выбор KPI, чтобы получить об этом представление.
И по поводу BI системы или инструментов - выбор вроде есть, но очень часто его на самом нет. Например, до 2022 года SAP BI был практически стандартом для компаний уровня Газпрома, крупных металлургических компаний и т.д., у которых есть акции на рынке и которые проходят аудиторскую проверку и публикуют свою отчетность и информацию для инвесторов. Или, например, амбициозная IT компания может считать KPI своими силами, и выбор инструментов уже может быть в рамках стека приоритетного языка программирования компании, а не опенсорсных систем. У российских гос. компаний свои требования к безопасности BI систем, какие-то опенсорсные системы могут им не удовлятворять.
Т.е. при выборе BI системы нужно учитывать требования рынка (например, информация для акционеров), учитывать структуру компании (например, группа компаний, и требуется ли консолидированная отчетность), сколько видов отчетности требуется (например, отченость по международным стандартам МСФО и РСБУ), учитывать ограничения безопасности для гос. компаний, учитывать возможности IT подразделения самой компании, учитывать бюджет, сроки, успехи конкурентов на рынке и т.д. Если применить все эти фильтры, то список BI систем сильно сокращается, и для многих компаний вовсе исключаются опенсорсные BI системы.
UPDATE: по поводу Apache Superset, например, посмотрел демо видео на главной странице, там как-то не очень заметно какого-то сложного drill down, итогов-подытогов, таблиц с группировкой с +/-, сводных таблиц, сложных фильтров и т.д., как-то негусто на первый взгляд, на минималках. Ещё условно такие критерии есть.
Из опенсорсных, похоже, популярные:
Apache Superset: платформа визуализации и исследования данных с открытым исходным кодом, разработанная как визуальная, интуитивно понятная и интерактивная.
Metabase: простой и легкий инструмент бизнес-аналитики, который позволяет пользователям легко визуализировать и анализировать данные.
Redash: инструмент бизнес-аналитики с открытым исходным кодом, который интегрируется с различными источниками данных и позволяет пользователям создавать визуализации и информационные панели.
Apache Druid: высокопроизводительная аналитическая база данных в режиме реального времени, предназначенная для быстрых специальных запросов к большим наборам данных.
KNIME: платформа анализа данных с открытым исходным кодом, которая позволяет пользователям создавать визуальные рабочие процессы для обработки, анализа и визуализации данных.
Pentaho: Комплексная платформа бизнес-аналитики с открытым исходным кодом, которая предлагает интеграцию данных, интеллектуальный анализ данных, отчеты, информационные панели и многое другое.
BIRT (Инструменты бизнес-аналитики и отчетности): проект программного обеспечения с открытым исходным кодом, который позволяет пользователям создавать визуализации данных, отчеты и информационные панели.
Без ограничений по a1 в T никак, без ограничений по a1 даже INNER JOIN наоборот превратится в CROSS JOIN:
Ограничения по a1 в T могут быть разные (уникальность, первичный ключ, уникальность на основе данных в таблице T, но без формальных ограничений уникальности на уровне ClickHouse), и связь T_1.a1 и T_2.a1 может быть условно через третьи таблицы, каждый случай нужно рассматривать отдельно, но условие T_1.a1 = T_2.a1 по крайней мере должно работать (обеспечивать связь один к одному) на основе какого-то набора данных, чтобы дальше исследовать проблему.
Согласен, что для произвольного запроса убрать CROSS JOIN невозможно, но в статье рассматриваются три частных случая и есть условия их применимости, не предлагается выкинуть любой CROSS JOIN. Такое впечатление, что в комментарии не дубликаты имеются в виду, а декартово произведение, полный перебор. Три случая в статье относятся к особым видам запросов, где действительно можно убрать CROSS JOIN. Здесь я проиллюстрировал два запроса: первый попадает под один из описанных случаев, второй нет, и так и остаётся CROSS JOIN.
Кроме того, в реляционной алгебре нет дубликатов (т.к. идёт работа со множествами), но декартово произведение всё равно используется, т.е. декартово произведение без дубликатов (другого декартового произведения условно и нет в реляционной алгебре) и CROSS JOIN без дубликатов как минимум не лишены смысла, поэтому с учетом того, что проектов и кейсов много, и здесь наверно тоже какие-то частные случаи имеются в виду, и этот кейс не противоречит кейсам из статьи
Точно, здесь как раз простые случаи, как от него избавиться и что нужно учесть
Согласен, но и на практике при ручном написании запросов такие случаи тоже встречаются (хоть и в виде диких ляпов).
Эти случаи могут быть актуальными, когда SQL генерируется кодом (.NET, Java, Go и т.д.) для конкретного UI или отчета, т.е. не вручную. Во всех трех случаях для таблицы с 1 млн записей условно UI корректный (например, сводная таблица), или отчет условно корректный, т.е. их можно получить не за 1 млн × 1 млн операций, а за разумное число операций, условно 1 млн, но из-за CROSS JOIN получить отчет невозможно. Т.е. это больше для случаев, когда SQL генерируется не вручную и чинится тоже не вручную.
Рассмотренные случаи помогают что-то исправить в CROSS JOIN (удалить его) без значительного вмешательства в проект, такого, как:
ограничить число записей в запросе для одной или обеих таблиц в CROSS JOIN (например, LIMIT, LIMIT BY, topK в ClickHouse, или обычный GROUP BY - если возможно)
добавление паджинации для одной или обеих таблиц из запроса (если возможно)
условное упрощение сводной таблицы до таблицы размером 4 × 4 и выполнение 16 отдельных запросов для каждой ячейки (может быть актуально, например, для таблиц по кварталам или месяцам, при возможности можно выполнять запросы параллельно)
добавление или изменение фильтров для сводной таблицы
отказ от сводной таблицы как условно последнее средство, замена одного отчета несколькими и т.д., т.е. пересмотр бизнес-логики
Согласен, забыл в том случае дополнить, что a1 в T даже должно быть уникальным, ненулевым, в общем, ключом, иначе совсем не получится JOIN по
ON T_1.a1 = T_2.a1;Согласен, что разные результаты для
SELECT T_1.a1, T_1.a1 FROM T AS T_1 CROSS JOIN T AS T_2;иSELECT a1, a1 FROM T;, пояснение "возвращают одинаковые результаты с точностью до дубликатов (которые можно удалить с помощью GROUP BY)" может выглядеть неоднозначно, в playground приведён примерSELECT T_1.a1, T_1.a1 FROM T AS T_1 CROSS JOIN T AS T_2 GROUP BY T_1.a1, T_1.a1;SELECT a1, a1 FROM T;Он корректен, если a1 - это ключевое уникальное поле, для произвольного a1 корректно только такое:
SELECT T_1.a1, T_1.a1 FROM T AS T_1 CROSS JOIN T AS T_2 GROUP BY T_1.a1, T_1.a1;SELECT a1, a1 FROM T GROUP BY a1, a1;Согласен, что с точки зрения SQL это можно назвать иначе, "оптимизация CROSS JOIN" - для упрощения, чтобы не писать условно "оптимизация производительности SQL запроса путём удаления CROSS JOIN и замены на эквивалентный SQL запрос с другими типами JOIN (или без них) и дополнительными ограничениями, который возвращает те же результаты запроса"