Dynamics AX 2009 default cubes configuration issues & resolution

Issue-1. OLE DB error: OLE DB or ODBC error: Login failed for domain/machinename 28000; Cannot open database requested by the login. The login failed.; 42000.

Solution -- Give the user access on the OLTP database. In this case user is the machine hosting OLAP DB.

Issue-2. SQL execution error. The multi-part-identifier "dbo.CUSTTABLE.CUSTABLE.CUSTGROUP"  could not be bound.

Solution -- After removing invalid columns from Named-Queries when it is executed it may give this error. After monitoring the issue you will find statements like this "dbo.CUSTTABLE.CUSTGROUP.CUSTGROUP AS CUSTGROUP_CUSTGROUP". This is a known issue with Dynamics AX 2009 with SSAS 2008. 

Step1: Install the sql server 2005 native client.

Step2: Open properties page of SSAS Database dataset and change the PROVIDER to "SQLNCLI.1" from the existing "SQLNCLI10.1" value.

Step3: Make changes in the Named Queries. Make further changes as per required and Save, Build & Deploy them.

Step4: Change back the PROVEDER to SQLNCLI10.1as in step2. 

     For more details Click here.

Issue-3. Cannot call method on nvarchar.

Solution -- This error comes while executing the named query.  In complex queries it is difficult to parse the entire query and try to find where is the exact fault. Better is to check the table. It is possible that a FIELD in the table is disabled due to configuration key. I compared the table fields with those available when browsing the table. Fields that are disabled will not show up while browsing table. You can also find it by looking at the property sheet and the config keys.

Issue-2. Revisited.

Solution -- There are chances that the issue 2 comes back to haunt even after changing the Property for Provider. The solution is to correct the Identifiers in the query manually and change like CUSTGROUP.CUSTGROUP instead of CUSTABLE.CUSTGROUP.CUSTGROUP .

Hope it helps ......