go to post Samuel Claiborne · Jan 16, 2017 I would add something about 'This Service Unavailable" messages when trying to run Analyzer, Architect etc. - this is not a 'hang', but is symptomatic of no more available licenses. Sorry I didn't mention this explicitly before. Even the support guys on the phone with me did not know that the system was trying to tell us it had run out of licenses - they had to check the logs. Until a more meaningful error message is provided, perhaps this should be added to the chart too. Cheers.
go to post Samuel Claiborne · Jan 9, 2017 Thank you Alessandro for this post. It got me started, and was quite valuable to my education.However, in my usage case, performance was still quite sluggish, so I tried another approach: I created a SQL-based KPI that selected against the underlying table, which resulted in much better performance. In my testing on a cube with 900K facts, the performance was quite literally more than an order of magnitude faster.Using SQL with CASE statements, one can derive all of these fields, although the SQL is perhaps even more baroque and complex than even the MDX is.
go to post Samuel Claiborne · Dec 29, 2016 Great guide. I would add something to the 'hangs' section on system health. We were evaluating DeepSee and having massive performance and hang issues. I was trying to do complicated stuff, and the machine was underpowered, but a MAJOR part of the problem was license thrashing. We had four licenses and my queries were essentially trying to spawn multiple processes and hitting the wall, and then trying again and again and again. Not only did we miss this, but it did not occur to support either, until they saw 'This Service Unavailable" messages, and tracked that down to license depletion. Changing our evaluation version to 10 licenses, with no other changes, to the machine, or the queries, instantly took the machine from DOA to usable.
go to post Samuel Claiborne · Dec 29, 2016 Thanks for your answer!My replies to your questions:1) I am working with the month because when you're doing year-over-year comparisons, you need comparative data. What if it's February, and I am comparing jan-feb of this year with all of last year? Similarly, what if it's December 3rd, and I am comparing jan-dec of this year, missing almost all of December's sales, with all of last year? That is why I use last month as the cutoff, to insure a fair comparison. If I am running it in January of 2017, for example, it will run for jan-dec of 2016 and compare that with jan-dec of 2015.However, once I got a semi-solution working (using %MDX, as you'd suggested) I tried to improve performance by making a higher-level cube, where the data was aggregated by customer, by product, and all on a single day per month, the first of the month, which obviously reduced the number of records for each customer for each product massively, as in the detail cube, there is a row for every single LINE of every single invoice! But performance is still underwhelming, and I am now investigating using SQL KPIs against the base tables. I think this is one of those areas where SQL may outshine MDX.1a) I did 'solve' the issue exactly as you said, wrapping an mdx 'subquery' via the %MDX statement, and, yes, using context on columns. It works, but it's SLOW... Even for a single, small customer with only a handful of sales per year.2) I have used cellzero previously, but I am not fond of it, because it limits functionality to a specific query, and to that query's layout - a layout I may not even want to use - some columns needed for the calculation are columns that may screw up a chart in a dashboard. I much prefer to build flexible, reusable parts. I wish that the MDX execution engine were smart enough, as some SQL engines are, to see if the same stuff is selected more than once, and if so, to save it in a variable array for reuse.Thanks for your thoughtful answers!
go to post Samuel Claiborne · Nov 14, 2016 I'm also not sure I phrased the initial question properly. It's period A - Period B to date - so: year to date, quarter to date, month to date, year to date - this year/last year or this quarter/last quarter etc. However, I will probably want to develop the ability to see 3rd quarter last year vs 3rd quarter this year too, eventually - so, yes, a period-to-date, and a discrete period as well.
go to post Samuel Claiborne · Nov 14, 2016 In answer to both questions, yes. Ideally, I'd have drop downs for year/week/month in the dashboard and the user could pick from them, and then the Last Period would dynamically react to what they selected. Not sure exactly how I'd implement the user interface, as I am just starting to make dashboards. So, maybe a radio button for year/quarter/month/week, and then a dynamically-populated dropdown based on which radio button they select, or perhaps 4 dropdowns... I do NOT foresee them picking last year 3rd quarter, and then giving them last year -1 3rd quarter - I more foresee only giving them options within this year - but that may not be true at all at the start of the year, when there's little data. I have to research it more.I have seen the YTD and TOTALYTD functions referenced in MDX queries on the web, but I don't see these supported in DeepSee. It *appears* to me that these functions would make this a fairly easy thing to implement at the yearly level, which, frankly, I could live with in the short term. Long term, they will want to see quarter-to-quarter performances, month-to-month, and week-to-week.Thanks
