自定义计数测量永远运行 MDX [英] custom count measure runs forever MDX

问题描述

所以这个问题在这里

我一直在尝试进行类似的计数测量，并且我执行了建议的解决方案，但它仍在运行......并且已经超过 30 分钟没有结果，而没有结果它会在一分钟内运行.所以我错过了什么吗?任何指导都会有所帮助.这是我的查询:

WITH成员 [Measures].[IteractionCount] AS非空(筛选(([DimInteraction].[InteractionId].[ALL].Children,[措施].[展示次数]),[DimInteraction].[交互状态].&[启用])).数数选择({[措施].[迭代计数]，[措施].[展示次数]})在列上，(([DimCampaign].[CampaignId].[CampaignId].MEMBERS,[DimCampaign].[Campaign Name].[Campaign Name].MEMBERS,[DimCampaign].[所有者].[所有者].成员),[DimDate].[日期].[日期].成员)在行从(选择({[DimDate].[date].&[2020-05-06T00:00:00] : [DimDate].[date].&[2020-05-27T00:00:00]})在列上来自 [型号])在哪里({[DimCampaign].[EndDate].&[2020-05-27T00:00:00]:NULL},[DimCampaign].[Campaign State].&[Active],{[DimInteraction].[结束日期].&[2020-05-27T00:00:00]:NULL}//,//[DimInteraction].[Interaction State].&[Enabled])

我不知道 FILTER 是否以任何方式影响它，但我尝试了它，不管它有没有，它仍然永远运行.我确实需要将它专门过滤到 [DimInteraction].[Interaction State].&[Enabled].我也尝试过在 WHERE 子句中过滤到该选项，但没有运气

任何优化此功能的建议将不胜感激！谢谢！

更新:我最终使用此查询将数据加载到 python 数据帧中.这是我的代码.我使用这个脚本来连接和加载数据.尽管要使用 Windows 身份验证，但我必须对其进行一些编辑.

ssas_api._load_assemblies() #this 使用 Windows 身份验证conn = ssas_api.set_conn_string(server='server name',db_name='db name')df = ssas_api.get_DAX(connection_string=conn, dax_string=query))

dax_string 参数是接受 dax 或 mdx 查询以从多维数据集中提取的参数.

解决方案

请试试这个优化:

<预><代码>和成员 [Measures].[IteractionCount] AS和([DimInteraction].[InteractionId].[InteractionId].Members* [DimInteraction].[Interaction State].&[Enabled],IIF(IsEmpty([Measures].[Impression Count]),空值，1))选择({[措施].[迭代计数]，[措施].[展示次数]})在列上，(([DimCampaign].[CampaignId].[CampaignId].MEMBERS,[DimCampaign].[Campaign Name].[Campaign Name].MEMBERS,[DimCampaign].[所有者].[所有者].成员),[DimDate].[日期].[日期].成员)属性成员_CAPTION在行从(选择({[DimDate].[date].&[2020-05-06T00:00:00] : [DimDate].[date].&[2020-05-27T00:00:00]})在列上来自 [型号])在哪里({[DimCampaign].[EndDate].&[2020-05-27T00:00:00]:NULL},[DimCampaign].[Campaign State].&[Active],{[DimInteraction].[结束日期].&[2020-05-27T00:00:00]:NULL}//,//[DimInteraction].[Interaction State].&[Enabled])细胞特性值

如果这表现不佳，请描述当您从列轴注释掉 IteractionCount (sic) 时查询返回的行数.并请描述您有多少唯一的 InteractionId 值.

So this question goes off the one here

I've been trying to do a similar count measure and I did the suggested solution but it's still running.... and it's been more than 30 minutes with no results, while without that it runs in under a minute. So am I missing something? Any guidance would help. Here is my query:

WITH 
    MEMBER [Measures].[IteractionCount] AS 
    NONEMPTY
    (
        FILTER
        (
            ([DimInteraction].[InteractionId].[ALL].Children,
            [Measures].[Impression Count]),
            [DimInteraction].[Interaction State].&[Enabled]
        )
    ).count

SELECT 
(
    {[Measures].[IteractionCount],
    [Measures].[Impression Count]}
)
ON COLUMNS,
(   

    ([DimCampaign].[CampaignId].[CampaignId].MEMBERS,
     [DimCampaign].[Campaign Name].[Campaign Name].MEMBERS, 
     [DimCampaign].[Owner].[Owner].MEMBERS)
    ,[DimDate].[date].[date].MEMBERS
)
ON ROWS
FROM 
(
    SELECT 
    (
       {[DimDate].[date].&[2020-05-06T00:00:00] : [DimDate].[date].&[2020-05-27T00:00:00]}
    )
    ON COLUMNS 
    FROM [Model]
)
WHERE
(
        {[DimCampaign].[EndDate].&[2020-05-27T00:00:00]:NULL}, 
        [DimCampaign].[Campaign State].&[Active], 
        {[DimInteraction].[End Date].&[2020-05-27T00:00:00]:NULL}//,
        //[DimInteraction].[Interaction State].&[Enabled]
)     

I don't know if FILTER is affecting it in any way but I tried it with and without and it still runs forever. I do need it specifically filtered to [DimInteraction].[Interaction State].&[Enabled]. I have also tried to instead filter to that option in the WHERE clause but no luck

Any suggestions to optimize this would be greatly appreciated! thanks!

UPDATE: I end up using this query to load data into a python dataframe. Here is my code for that. I used this script for connecting and loading the data. I had to make some edits to it though to use windows authentication.

ssas_api._load_assemblies() #this uses Windows Authentication
conn = ssas_api.set_conn_string(server='server name',db_name='db name')

df = ssas_api.get_DAX(connection_string=conn, dax_string=query))

The dax_string parameter is what accepts the dax or mdx query to pull from the cube.

解决方案

Please try this optimization:

WITH 
    MEMBER [Measures].[IteractionCount] AS 
    SUM
    (
        [DimInteraction].[InteractionId].[InteractionId].Members
         * [DimInteraction].[Interaction State].&[Enabled],
         IIF(
           IsEmpty([Measures].[Impression Count]),
           Null,
           1
         )
    )

SELECT 
(
    {[Measures].[IteractionCount],
    [Measures].[Impression Count]}
)
ON COLUMNS,
(   

    ([DimCampaign].[CampaignId].[CampaignId].MEMBERS,
     [DimCampaign].[Campaign Name].[Campaign Name].MEMBERS, 
     [DimCampaign].[Owner].[Owner].MEMBERS)
    ,[DimDate].[date].[date].MEMBERS
)
PROPERTIES MEMBER_CAPTION
ON ROWS
FROM 
(
    SELECT 
    (
       {[DimDate].[date].&[2020-05-06T00:00:00] : [DimDate].[date].&[2020-05-27T00:00:00]}
    )
    ON COLUMNS 
    FROM [Model]
)
WHERE
(
        {[DimCampaign].[EndDate].&[2020-05-27T00:00:00]:NULL}, 
        [DimCampaign].[Campaign State].&[Active], 
        {[DimInteraction].[End Date].&[2020-05-27T00:00:00]:NULL}//,
        //[DimInteraction].[Interaction State].&[Enabled]
)     
CELL PROPERTIES VALUE

If that doesn’t perform well the please describe the number of rows returned by your query when you comment out IteractionCount (sic) from the columns axis. And please describe how many unique InteractionId values you have.

这篇关于自定义计数测量永远运行 MDX的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！