When using data cubes to transform your data to the structure you need it for your analytics, a common transformation to add is a calculated element that allows you to calculate new values using your own script logic.
The advantage of this transformation is that you can write pretty much almost any logic to calculate data as needed. On the flip side, since you can use C# programming language to write your logic, it is not possible to translate that logic to a valid SQL query, meaning that your data cube will be forced to bring the data into the Dundas BI server and then apply the script logic on the data before it can be delivered to the visualizations or be stored (in-memory/warehouse).
One way to prevent the need to bring all the data into the Dundas BI server is using SQL expressions to calculate your calculated columns. SQL expressions are not as rich and capable as free form C# scripts, but often they are rich enough and allow you to avoid the need for a separate calculated element.
You can of course use SQL expression in your own manual SQL querybut you can also leverage the speed of the UI drag and drop to generate your queries (that is standard SQL Select transforms) and simply add a SQL expression in it.
For example, you have a table with a date column and you want to get the Year alone. You can add a SQL expression such YEAR( date column name ) and get that added to your query as shown in the image below. Please note that the syntax will have to comply with the underlying data source so for example you won’t be able to use SQL Server expressions that don’t exist in Oracle if you are running this on an Oracle database connector.
.png)