MDX - SQL Help

[largeselection]

Hello,

I am trying to figure out the best way to get information out of an OLAP database.

So the database is structured with these fields: [player], [team], [location], [points], [year]. There are many players which roll up into teams and many teams. There are 2 available years.

Basically what I would like to get at is the count for this year and for last year of player/location combinations that sum to less than 2 points. If I pull total player then that gives me the points across all locations, if I pull total location, that gives me points across all players, so I need player/location level detail.

So I could try to create a pivottable and then use formulas to count it out, the issue is that I have 6,000 players, and each player would then have 700 locations = 6,000 x 700 = 4.2 million rows x 2 columns if I put this year and last year as columns, otherwise 8.4 million rows if I try and include it horizontally.

So I thought there must be a simpler way to do this via mdx (or dax in powerpivot if need be). I don't need to see all of the rows of data, I just need to know the count where player/location combos have points total < 2.

I would be happy enough if I could just limit the set to only show those player/locations where that was true because that would be considerably smaller in size than the 4.2 million rows.

I tried something like IIF([POINTS] < 2, [PLAYERS], null) as a named set in MDX, and then made a pivottable where I put locations on the horizontal, then I thought I could throw that named set in and it would work...and it did not. I'm not even a noob when it comes to MDX/SQL I'm way below that level.

Any ideas?? Thanks in advance

 

[Fazza]

hi, suggest you post sample data. it makes understanding the requirement much easier. regards

 

[largeselection]

Not sure if this is helpful, but here is a sample of what I'm trying to accomplish. In the sample you can see how I could potentially pull the data in from the OLAP cube as a pivot table, and then I could either create another pivot table based on that data or use formulas to calculate out what I'm looking for (the count of unique player/location entries with less than 2 points for both years). However, the dataset would be 8.4 million rows if I pulled it in this way so I'm looking for a way to either pull only the rows that meet that criteria or a way to just give me the counts.

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">PLAYER</td><td style="font-weight: bold;text-align: center;;">TEAM</td><td style="font-weight: bold;text-align: center;;">LOCATION</td><td style="font-weight: bold;text-align: center;;">POINTS</td><td style="font-weight: bold;text-align: center;;">YEAR</td><td style="font-weight: bold;text-align: center;;"><2</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">TOM</td><td style="text-align: center;;">APPLE</td><td style="text-align: center;;">1</td><td style="text-align: center;;">10</td><td style="text-align: center;;">2010</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">TOM</td><td style="text-align: center;;">APPLE</td><td style="text-align: center;;">2</td><td style="text-align: center;;">0</td><td style="text-align: center;;">2010</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">TOM</td><td style="text-align: center;;">APPLE</td><td style="text-align: center;;">3</td><td style="text-align: center;;">5</td><td style="text-align: center;;">2010</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">TOM</td><td style="text-align: center;;">APPLE</td><td style="text-align: center;;">1</td><td style="text-align: center;;">4</td><td style="text-align: center;;">2011</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">TOM</td><td style="text-align: center;;">APPLE</td><td style="text-align: center;;">2</td><td style="text-align: center;;">1</td><td style="text-align: center;;">2011</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">RICH</td><td style="text-align: center;;">APPLE</td><td style="text-align: center;;">1</td><td style="text-align: center;;">10</td><td style="text-align: center;;">2010</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">RICH</td><td style="text-align: center;;">APPLE</td><td style="text-align: center;;">2</td><td style="text-align: center;;">0</td><td style="text-align: center;;">2010</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">RICH</td><td style="text-align: center;;">APPLE</td><td style="text-align: center;;">3</td><td style="text-align: center;;">2</td><td style="text-align: center;;">2010</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">RICH</td><td style="text-align: center;;">APPLE</td><td style="text-align: center;;">1</td><td style="text-align: center;;">1</td><td style="text-align: center;;">2011</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;">RICH</td><td style="text-align: center;;">APPLE</td><td style="text-align: center;;">2</td><td style="text-align: center;;">3</td><td style="text-align: center;;">2011</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;;">HARRY</td><td style="text-align: center;;">ORANGE</td><td style="text-align: center;;">1</td><td style="text-align: center;;">5</td><td style="text-align: center;;">2010</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;;">HARRY</td><td style="text-align: center;;">ORANGE</td><td style="text-align: center;;">2</td><td style="text-align: center;;">6</td><td style="text-align: center;;">2011</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;;">HARRY</td><td style="text-align: center;;">ORANGE</td><td style="text-align: center;;">3</td><td style="text-align: center;;">8</td><td style="text-align: center;;">2011</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;;">HARRY</td><td style="text-align: center;;">ORANGE</td><td style="text-align: center;;">4</td><td style="text-align: center;;">9</td><td style="text-align: center;;">2011</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;;">HARRY</td><td style="text-align: center;;">ORANGE</td><td style="text-align: center;;">1</td><td style="text-align: center;;">4</td><td style="text-align: center;;">2011</td><td style="text-align: right;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;;">SUE</td><td style="text-align: center;;">ORANGE</td><td style="text-align: center;;">1</td><td style="text-align: center;;">0</td><td style="text-align: center;;">2011</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: center;;">SUE</td><td style="text-align: center;;">ORANGE</td><td style="text-align: center;;">2</td><td style="text-align: center;;">0</td><td style="text-align: center;;">2011</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: center;;">…</td><td style="text-align: center;;">…</td><td style="text-align: center;;">…</td><td style="text-align: center;;">…</td><td style="text-align: center;;">…</td><td style="text-align: center;;">…</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: center;;">…</td><td style="text-align: center;;">…</td><td style="text-align: center;;">…</td><td style="text-align: center;;">…</td><td style="text-align: center;;">…</td><td style="text-align: center;;">…</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: center;;">…</td><td style="text-align: center;;">…</td><td style="text-align: center;;">…</td><td style="text-align: center;;">…</td><td style="text-align: center;;">…</td><td style="text-align: center;;">…</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: center;;">…</td><td style="text-align: center;;">…</td><td style="text-align: center;;">…</td><td style="text-align: center;;">…</td><td style="text-align: center;;">…</td><td style="text-align: center;;">…</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: center;;">…</td><td style="text-align: center;;">…</td><td style="text-align: center;;">…</td><td style="text-align: center;;">…</td><td style="text-align: center;;">…</td><td style="text-align: center;;">…</td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="font-weight: bold;text-align: center;;">6000 Unique Players</td><td style="text-align: center;;">x</td><td style="font-weight: bold;text-align: center;;">700 Unique Locations</td><td style="text-align: center;;">x</td><td style="font-weight: bold;text-align: center;;">2 Possible Years</td><td style="font-weight: bold;;">8400000 Rows</td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style=";">Sum of <2</td><td style=";">Column Labels</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style=";">Row Labels</td><td style="text-align: right;;">2010</td><td style="text-align: right;;">2011</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style=";">APPLE</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style=";">ORANGE</td><td style="text-align: right;;">0</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br />

 

[Fazza]

How about just using one pivot table directly on the source data?

Suggest starting in a new workbook and at the first step of the pivot table wizard (ALT-D-P) choose the external data option. Follow the wizard [Eaiser if the source data has a defined name. If the source data doesn't have a defined name range you might get a message about no visible tables, choose options then 'system tables' to see worksheet names.] to the end choosing edit in MS Query. Either use the GUI or edit the SQL to obtain the dataset defined by

SELECT DISTINCT TEAM, LOCATION, YEAR
FROM YourData
WHERE POINTS < 2

Exit MS Query, either via menu or the open door icon, and complete the pivot table, using COUNT on the data field.

regards

 

[largeselection]

Thanks for following up.

I tried to do this method, but it said I couldn't edit in query because I'm connecting to an OLAP data source.

I tried adding the clause where Points < 2 to the end of the SQL generated in powerpivot, but it did not work (and also would not let me add points to a filter because it's a measure).

 

[Fazza]

OK. I can't help, sorry. I know nothing about OLAP, mdx, powerpivot or dax.

Guessing: if the sheer number of records is a limit, what about using MS Access? (Or even just an mdb file, which can be created from Excel without using MS Access if need be: though this would be an awkward approach.)

 

[largeselection]

I appreciate your help nonetheless. Thanks very much. At the moment I can import the data set into powerpivot - it just takes 2 hours to import, but that will work until I can figure out a simpler way.

Thanks again!