Importing from cube with filters loosing data.

[Anonymous]

Hello

I have a cube from where I'm pulling data from. I have to import so I could use custom measurements. There's a table that is way too big for PBI to handle so before importing all the data I only imported the date table and put a year 2020 filter on it and then added the other tables to get the data I need. For some reason I am having some data geting lost doing this. 

In excel ( connected to the same cube ) puting on filters like 2020 - August it shows data for property A425 - "1094" but in my imported PBI file it shows 2020 - August for A425 only 256. I tried live connection to that cube in PBI, filtered 2020 - August and then it also shows A425 value 1094. 

I can't figure out why am I loosing so much data during import.  Looking at the Date table I imported first I see Date - Time.day, Date - Time.Hour, Date - Time.Month, Date - Time.Year so it can not be i lost some data because of missing hour date or something like that.

What I did: Get data from Analysis services, put in server info and choose Import. Chose the Date table and pressed "Transform data". In query I filtered Year column to 2020 and pressed Add items and chose the remaining tables I need for this report. ( Same tables as in live connection mode ). After successfully importing I am missing so many data.

[FarhanAhmed · Accepted solution]

You can use import method by  

Also instead of filtering you should write Filtered MDX Query

https://docs.microsoft.com/en-us/analysis-services/multidimensional-models/mdx/mdx-query-the-basic-q...

One of the easiest way is to go to SSMS and drag all the fields / measures that are required and then "Design Mode" to get the MDX query of your data and then write query in below segment.

[amitchandak]

@Anonymous , can you share the schema diagram?

also refer

https://community.powerbi.com/t5/Desktop/SSAS-Tabular-Date-Hierarchies-not-working-with-Live-Connection/td-p/862296

[FarhanAhmed]

What is your MDX Import Query ?

It is better to write Import MDX Query rather than importing thru cube and filtering out in PQE.

[Anonymous]

Thank you for answering. I haven't figured out how to import using MDX yet, still learning. I googled it and found some solutions that I got working on a normal DWH but in this cube it doesn't seem to work that straight forward. ( In this cube first when I add server and click import it gives me many options, I guess DBs? and one of them is "PropertyNew" and after I expand it under there lies "Property Cube" - what I need )

In advanced editor my M query goes like this:
let
Source = AnalysisServices.Database("Cube", "PropertyNew", [TypedMeasureColumns=true, Implementation="2.0"]),
#"Property Cube1" = Source{[Id="Property Cube"]}[Data],
#"Property Cube2" = #"Property Cube1"{[Id="Property Cube"]}[Data],
#"Added Items" = Cube.Transform(#"Property Cube2",
{
{Cube.AddAndExpandDimensionColumn, "[Time]", {"[Time].[Day Number Of Month].[Day Number Of Month]", "[Time].[Day Number Of Week].[Day Number Of Week]", "[Time].[Month Number Of Year].[Month Number Of Year]", "[Time].[Week Number Of Year ISO].[Week Number Of Year ISO]", "[Time].[Time].[Calendar Year]", "[Time].[Time].[Month]", "[Time].[Time].[Days]", "[Time].[Time].[Hour]"}, {"Date - Time.Day", "Date - Time.Day of week", "Date - Time.Month", "Date - Time.Week ISO", "Date - Time.Year", "Date - Time.Month.1", "Date - Time.Days", "Date - Time.Hour"}}
}),
#"Filtered Rows" = Table.SelectRows(#"Added Items", each (Cube.AttributeMemberId([#"Date - Time.Year"]) = "[Time].[Time].[Calendar Year].&[2020]" meta [DisplayName = "2020"])),
#"Added Items1" = Cube.Transform(#"Filtered Rows",
{
{Cube.AddAndExpandDimensionColumn, "[Project]", {"[Project].[Project].[Project]", "[Project].[Project Category].[Project Category]", "[Project].[Project Cost Center].[Project Cost Center]", "[Project].[Project Region].[Project Region]", "[Project].[Address].[Project City]", "[Project].[Address].[Project Street]", "[Project].[Country].[Project Country]", "[Project].[Country].[Project Company]", "[Project].[Project opening date].[Opening Date Year]", "[Project].[Project opening date].[Opening Date Month]", "[Project].[Project opening date].[Opening Date]", "[Stores].[Stores].[Store Level 4]", "[Stores].[Stores].[Store Level 3]", "[Stores].[Stores].[Store Level 2]", "[Stores].[Stores].[Store]"}, {"Store.Store list", "Project.Project category", "Project.Cost center", "Project.Project region", "Project.City", "Project.Street", "Project.Country", "Project.Company", "Project.Opening year", "Project.Opening month", "Project.Opening date", "Project.Project level 4", "Project.Project level 3", "Project.Project level 2", "Project.Project list.1"}},
{Cube.AddAndExpandDimensionColumn, "[Project POS]", {"[Project POS].[Project].[Project]", "[Project POS].[Project Pos].[Project Pos]", "[Project POS].[Project pos type].[Project pos type]", "[Project POS].[POS hierarchy].[Project]", "[Project POS].[POS hierarchy].[POS type]", "[Project POS].[POS hierarchy].[Project Pos]", "[Project POS].[Type hierarchy].[Project pos type]", "[Project POS].[Type hierarchy].[Project Pos]"}, {"Project POS.Store", "Project POS.Project POS", "Project POS.POS type", "Project POS.Project.1", "Project POS.POS type.1", "Project POS.Project POS.1", "Project POS.POS type.2", "Project POS.Project POS.2"}},
{Cube.AddMeasureColumn, "Number of receipts", "[Measures].[Count of Receipts]"}
})
in
#"Added Items1"

How can I see what is my MDX import query? Also I'm not sure how to share schema.

[FarhanAhmed · Accepted solution]

You can use import method by  

Also instead of filtering you should write Filtered MDX Query

https://docs.microsoft.com/en-us/analysis-services/multidimensional-models/mdx/mdx-query-the-basic-q...

One of the easiest way is to go to SSMS and drag all the fields / measures that are required and then "Design Mode" to get the MDX query of your data and then write query in below segment.

[Anonymous]

Well I got as far so far:
SELECT NON EMPTY Hierarchize({DrilldownLevel({[Project POS].[Type hierarchy].[All types]},,,INCLUDE_CALC_MEMBERS)}) ON COLUMNS , NON EMPTY Hierarchize({DrilldownLevel({[Project POS].[POS hierarchy].[All POS]},,,INCLUDE_CALC_MEMBERS)}) ON ROWS FROM [Property Cube] WHERE ([Time].[Time].[Calendar Year].&[2020],[Measures].[Count of Receipts])

This MDX works very well in SSMS but in PBI it doesnt.
Instead where I should have Project names I get [Project POS].[POS hierarchy].[All POS] for every value. I read earlyer about PBI having problems with hierarchys but don't know how to bypass it now in my code.

PS. I know this topic has been answered already but I hope I can get some ideas how to fix it without making a new topic.

[FarhanAhmed]

Altought I am not an expert of MDX but your Try something like this

 SELECT 
 
 NON EMPTY { [Measures].[Count of Receipts] } ON COLUMNS, 
 NON EMPTY { ([Project POS].[Type hierarchy].ALLMEMBERS * [Project POS].[POS hierarchy].ALLMEMBERS ) }  ON ROWS 
 FROM ( SELECT ( { [Time].[Time].[Calendar Year].&[2020] } ) ON COLUMNS 
 FROM [Property Cube]) WHERE ( [Time].[Time].[Calendar Year].&[2020] )

If this helps kindly mark "Kudos" to this post

[Anonymous]

Thank you, getting closer 🙂

In your code the type hierarchy is in ROW which means all types are mixed up in rows but I need types to be in a column. IE I have 2 different types WinPOS and WinSCO, need those 2 types in columns not in rows. If you have any suggetions I'd be happy to hear but if not then thank you still, your code got me closer to the final code 🙂

[FarhanAhmed · Accepted solution]

Thanks !

I always believe Dimension must be come it Rows and then you can use Matrix Visualization to swap Rows in columns 

You may want to add those 2 in filters 

 SELECT 
 
 NON EMPTY { [Measures].[Count of Receipts] } ON COLUMNS, 
 NON EMPTY { ([Project POS].[Type hierarchy].ALLMEMBERS * [Project POS].[POS hierarchy].ALLMEMBERS ) }  ON ROWS 
 FROM ( SELECT ( { [Time].[Time].[Calendar Year].&[2020] } ) ON COLUMNS 

 FROM ( SELECT ( { [Project POS].[Type hierarchy].&[WinSCO] ,[Project POS].[Type hierarchy].&[WinPOS]} ) ON COLUMNS 


 FROM [Property Cube]) WHERE ( [Time].[Time].[Calendar Year].&[2020] )) 
 

If I haven't write correct hierarchies you may want to drag and drop each member of type in SSMS and then put in the filter statement 

Hopefully this will helps too..

[Anonymous]

In my case using SSMS doesn't sadly help, whenever I use SSMS connection to Analysis services it doesn't show the ProjectNew DB for me but I can see it in PBI, this was a weird thing a former colleague of mine couldn't understand either who is way more experienced in this than I am. I'll try to figure out how to write the MDX code by myself and try to see if the data is complete through this method. Thanks.