Server Load SQL side or Cubed in Dundas


(Gary Francoeur) #1

Hello all, I’m new to Dundas and setting up my first dashboard.
I have a SQL view for evaluating GPM on individual products. (Unit Cost vs Unit Price).
Since we are a manufacture and use actual time vs standard time the cost for every inventory layer may be different. That said, I would like to average the last 8 receipts for the product and use that as the comparison. At this point I’m afraid the overhead in trying to do this is from the view might be overwhelming. Is using a cube a better use or resources for this type of inquiry?
Any insight would be most helpful!
Thanks
Gary


(Ariel Pohoryles) #2

did you mean that executing the query against your view is taking too much time? Please share any additional details if you have those.

That said, I’m not sure how you configured it but one option to look at is to use the Dundas BI top/bottom option to only pull the last 8 receipts and then use an average formula on top of it.

If this is indeed what you tried and that is too slow then looking at storing your data in-memory or in the warehouse is indeed a good option assuming your data doesn’t need to be updated in real time from the source. You can read more about the consideration for which to use under this blog.


(James Davis) #3

Gary,
It depends no how you are getting your results. I have a query (that is the guts of a Data Cube) and it is long running so in Dundas I make that Data Cube of Storage type warehoused.
When making a Data Cube Dundas has many options, just dragging a table over, using a query, you can even do a lot of the things like joins union that you normally do in a query in the Data Cube by just dragging the tables and views onto the cube then using the built in joins and other things we normally do in a query.

All that said, to really help you we have to know how the cube is set up.
In general I can say that if you take what you are doing in the Cube and run as regular SQL and it runs long then it will run long in Dundas.
So if you cannot change the storage of the Cube then you have to work on the underlying SQL to optimize it to get the result you want in a timely manner.