Merging dimensions to do a thing

[tnoflahc]

 Greetings!

I have data that comes into PBI in the form of a number of dimension tables and a fact table, all of which come in "directly" via our consolidation system. I'm trying to generate a report using that data. Presently, I am able to generate that report more-or-less correctly by creating my own data warehouse in Excel and pulling the data from our consolidation system into that, and then pointing PBI to that Excel file. I am unable to generate that same report using what's pulled directly into PBI from the consolidation system.

I know this sounds complicated--I'll give ya'll an example:

Here's how I organized the data in the Excel file I referenced above:

Which gives me, in a matrix visual in PBI, the following:

Now, I'm not thrilled with the doubling of values for the city-level fields (in my hierarchy, this is the "whatever" level), but I can collapse up to the next level in the visual for now and figure that out later, unless someone knows a quick fix for that.

The problem I'm running into is the way the data is actually structured in our consolidation system. In our consolidation system, we have dimensions for account, time-period, entity, market, product, etc., and in order to generate this report, I have to, in essence, create a new hierarchy. "In essence," I say, but this is actually what I've done in the above Excel file.

So, let's say that Maine and California are intersections of Entity and Market, where I call on entity "Maine" and then specify the markets "Brunswick" and "Portland," and similarly with California, I call entity "California" and specify markets "San Jose, Los Gatos," etc.

But Alabama is different. The data I need for Alabama is entirely in the Entity dimension, so I call entity "Alabama" and the top-level parameters for all the other necessary dimensions, and then entity "Montgomery" and top-level parameters for the other dimensions, etc.

And then there are other states, too, that I haven't shown in my visuals. So, Texas, for example, is different still--Texas uses Product to differentiate itself by calling entity "Texas" and the top-level params for the other necessary dimensions (so, this would show the roll-up to "Texas"), and then I call entity "Texas" and PRODUCT "San Antonio," and then product "Wichita Falls," and product "San Angelo," etc., which would then all add up to the "Texas" total.

So, in my Excel file, I've just done the necessary formulae to call on the appropriate values to create the hierarchy shown in the screenshots above.

In the PBI file that pulls directly from our consolidation system, however, there is a table for ENTITY, for ACCOUNT, PRODUCT, etc., and within each table is a hierarchy: lvl 1, lvl 2, lvl 3, and so on.

I have some vague, nebulous understanding of what I need to do; I reckon I need to create a new table, maybe? And merge some columns according to the hierarchy specified in the above screenshots. So, since for Texas, the product dimension represents the city level, and a certain level of entity represents the city level for another state, I would want to merge the product column at whichever level has my Texas cities and the entity column of whichever level has my other cities, etc.

I think that's the solution. I just don't know how to do implement this.

I could really use some guidance. Am I correct in the overall solution? How would I go about implementing that in PBI? Or, am I just way off-track here?

[v-huizhn-msft]

Hi @tnoflahc,

Actually, I am still confusing about your requirement. If you want to create a report like the first screenshot shown, you just need to create a table visual, select all needed columns as value fields. If not, please share your expected result, then we can post solution which is close to what you want.

Best Regards,
Angelia

[tnoflahc]

Hi Angelia,

Thanks for the response!

To clarify, the report I'm looking to generate ought to be identitical to the matrix visual shown in I think the second screenshot of my OP. A table would be simpler--and that may indeed be a viable intermediate step--, but ultimately, I need to be able to drill down and have subtotals and all that, which functionality I do not believe is available on tables.

Additionally, my data structure has changed somewhat in my own Excel-based data warehouse, such that, instead of having columns for Actual, Budget, Forecast, and Prior Year, there is now a "time-period" column that specifies Actual, Budget, etc. and then a Values column. I made this change because it more closely reflects how the data is coming in from our consolidation software.

It might be helpful, too, if I include some information about how the data comes in from the consolidation software. I know I mentioned it briefly in the OP, but here's a little more detail.

We have a (what seems to me, anyway) textbook star schema coming into PBI, with a fact table and related dimension tables, which hold the hierarchies of the different dimensions referenced in the OP.

So, the fact table is arranged thus: 

And then for each _ID, there is a corresponding dimension table, "Account Hierarchy," "Time Period Hierarchy," etc.

Within each dimension table, a hierarchy is specified:

The problem I'm encountering when I try to use the data as-is, is that some sectors' hierarchy is entirely within the Entity dimension, so City is one level within entity, State is a level higher within entity, Region is yet another level higher within entity, and so on.

Other sectors use a combination of Entity and Market. Yet others use Entity and Product.

When I create a matrix visual using the data as I'm given it, adding, say, Product levels to those sectors that don't use Product to differentiate between sub-sectors adds data to the visual that I don't want.

I believe what I need to do is create a new data table in PBI that is nearly identitical to the one I've created in Excel. Basically, I would combine all these different dimensions and their hierarchies--entity, market, product--so that the level in Entity that corresponds to the City level is combined with the level in Product that also corresponds to the City level.

I know that's a lot to read through, but hopefully that clarifies what I'm working with and what I'm trying to do.

Conceptually, I'm fairly certain of what needs to be done; I just don't know how to implement it in PBI.

Thanks for your time!