DWDataMart as Dashboard Data Source

Nick_Flint

Has anyone setup DWDataMart from the Data Warehouse as a Dashboard Data Source? I'm trying to do so; but it doesn't seem to be making the connection and I'm not finding any related error messages.

Nick_Flint

On the obscure change that anyone else tries this in the future; we found that the data warehouse was configured to only use Windows Authentication. The connection string had to account for that:

Data Source=server;Initial Catalog=database;Integrated Security=SSPI;

Nick_Flint

On the obscure change that anyone else tries this in the future; we found that the data warehouse was configured to only use Windows Authentication. The connection string had to account for that:

Data Source=server;Initial Catalog=database;Integrated Security=SSPI;

Tom_Hendricks

Yes, in multiple environments.

Does the account your portal is running on (app pool account) have rights to the DWDataMart DB?

If so, the most likely culprit is that the portal does not like something about your query.  What tends to happen is that you get no results and no errors if there is a problem with the query.  The best way I know of (I invite others to share a better one, as this is not wonderful) to test this is to put your query into a chart widget.  If you are unable to select a category column (box is dimmed out) then the portal does not like something about your query.

Running your query in SQL server successfully does not guarantee success in a portal widget, unfortunately.

Things that sometimes stop it, without error (some will cause errors in SQL Server, which is good):

• First row has null values in one or more columns
• ORDER BY clause used in a subquery or common table expression
• WITH (NOLOCK) used on a joined table inside of a CTE
• Others?

Note that my test above will not work if you already have a GROUP BY clause in your SQL Statement, but I think you'll see how to adjust.