Hi,

We have 1.9 billions of data in the Fact table which is having Serial Numbers of HP Products. We have some default dimensions like Product , Customer , Geography and Date. Now We need, when I drill-through (Probably Row Set Action) we have to get the List of serial Number and its related information from the Relational Database table with dynamic filter which the end user is selected in Excel or Poly vista.

For Example We have Product ID ---XXX and YEAR--2006 and Count---200. Now We have to pass the dynamically the Filter Product ID, Year to Relational database and we have to get the 200 serial numbers information.

We tried to add Dimension with Serial Number. But Cube is very Slow . It is taking so much of time for processing (Some times cube processing is not finishing even for 1 or 2 days) and Query execution(Excel)

Hi,

Here is a good article which describes achieving this functionality via Analysis Services Stored Procedure Project.

http://geekswithblogs.net/darrengosbell/archive/2009/06/18/ssas-executing-arbitrary-sql-queries.aspx

http://geekswithblogs.net/darrengosbell/archive/2009/07/02/ssas-creating-a-rowset-action-with-the-executesql-.net-stored.aspx

http://asstoredprocedures.codeplex.com/

Best regards...

Build up a ROLAP Dimension for for your Serial, Hide it in the cube, so that the users are not able to browse it. Then use the AS Stored Procedure to write your own select-Statement to your relational database.

Dont try to use the standard Drillthrough functionality on a big ROLAP Dimension. Performance is also very poor.

KR

• Proposed as answer by Charlie LiaoMicrosoft contingent staff, Moderator Monday, December 15, 2014 10:37 AM

Hi 

I tried to use the row-set action to fetch at least 10 records from the base table. Below is the Action expression i am using:

"CALL SQLQuery.ExecuteSQL(
'provider=sqlncli;server=Server1;database=database1;trusted_connection=yes'
,'select top 10 * from table1 );"

I am getting below error.

Hi,

Sorry for the belated response. It seems the ExecuteSQL does not have enough permission to execute your SQL code. I actually tried the following method and it worked for me. I actually created my own custom library instead of using ASSP project. I used the following code.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using AMOS = Microsoft.AnalysisServices.AdomdServer;

namespace AMOServerUtility
{
    public class AMOServerUtil
    {
        [AMOS.SafeToPrepare(true)]
        public static System.Data.DataTable ExecuteSQL(System.String SQL, System.String OLEDBConnectionString)
        {
            System.Data.OleDb.OleDbConnection oConn = new System.Data.OleDb.OleDbConnection(OLEDBConnectionString);
            System.Data.OleDb.OleDbDataAdapter oDA = new System.Data.OleDb.OleDbDataAdapter(SQL, oConn);
            System.Data.DataTable dtResults = new System.Data.DataTable("Results");
            try
            {
                oDA.Fill(dtResults);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (oDA != null)
                {
                    oDA.Dispose();
                }

                if (oConn != null)
                {
                    oConn.Dispose();
                }
            }
            return dtResults;
        }
    }
}

Make sure you call SafeToPrepare(true) as in the above code. 

Compile and prepare the library. Next step is to deploy the Library in SSAS. Choose new Assembly in SSAS and follow the step in following screen to register the DLL. Once you are done execute the procedure in SSMS MDX query window and make sure you get the intended results. Here is a screenshot what I have described so far.

Then create your action, deploy and re-process the cube. To test it browse the cube in Excel. Right click on Excel Measure, Additional Actions > Your Action Name (Click on it) and you should get the results as follows;

Hope this helps.

Also one thing to remember ! Though this is a handy method anybody can place SQL DDL statements and execute which is not a good idea at all. Therefore please revisit your SQL security model if you wish to use this method.

Best Rega

Thanks Lakmal.. Now It is working with your code.

Why I am using this is, We have nearly 1.8 billions of data which deals with Serial Number Information of HP Server, HP Laptops, HP Printers and HP Storage Devices . If we get all this data into Cube, it is taking so much of time to process and query retrieving also. So I am trying to get the Least level granularity data (Serial Number Information) from the data base. I can store up to Product information (Second Least Granular information) in the Cube Level.

I tried Using ROLAP dimension also. But it is taking huge of time to get the results  while user doing drill-through.

Now Can you please tell me is there any other approach to solve this issue other than Row Set Action.

Now the main requirement is , I am getting top 100 records when I am executing the below query.  

CALL AMOServerUtility.ExecuteSQL(
'select top 100 * from table1 (nolock) where salary=1000 and name=''xyz''',
'provider=sqloledb.1;server=XXX;database=ABC;trusted_connection=yes'
)

Is there any way to do dynamic the filters (Where clause ).

Because end users are using Excel to browse the cube. Now I have to create a Row Set action and we have to  dynamically pass the parameters to the query ( what ever the filters that end user used in Excel  )

Hi,

What you are asking is a possible thing to do. You can achieve this functionality via MDX Intrinsic Member Properties. Following is the MSDN link which describes the Intrinsic Member Properties.

http://msdn.microsoft.com/en-us/library/ms145528.aspx

By using the Intrinsic Member Properties I modified the Action Expression I used as follows;

"call AMOServerUtility.ExecuteSQL('select TOP 10 facts.* 
from [dbo].[DimProductCategory] as pc
	inner join [dbo].[DimProductSubcategory] as psc
		on pc.ProductCategoryKey = psc.ProductCategoryKey
	inner join [dbo].[DimProduct] as p
		on p.ProductSubcategoryKey = psc.ProductSubcategoryKey
	inner join [dbo].[FactResellerSales] as facts
		on facts.ProductKey = p.ProductKey
where pc.ProductCategoryKey = " + [Product].[Category].CurrentMember.Member_Key + "','Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=AdventureWorksDW2012;Data Source=localhost')"

Notice the "where pc.ProductCategoryKey = " + [Product].[Category].CurrentMember.Member_Key + "'" line. This will pass each Category key dynamically and return the results just for the selected category.

Here are the results.

Best regards...

Hi Lakmal,

I have nearly 7 to 8 dimensions. Users can select any dimension in excel on row labels, Column labels and in Filter area. Now I want to do dynamic filter for the respective dimension members( In Row Label, Column Label and Filter Area). Query should be generated automatically based on the filters dimension selection by the

I have one suggestion regarding the problem of passing through the filters to the SQL Query:

Instead of building the SQL query inside Action Expression, you can create a Stored procedure in the SQL DB and pass on all the filters values as parameters. then inside the stored procedure build the SQL Query based on their values (don't include in the where class if there is an 'all' selected').

the result of the query can then be returned via stored procedure!

Hi Tahir,

How can we pass dynamically the filter Values as Parameters. Filters may vary from one user to another user. In this case how can we give the Parameters in the Procedure.

Hi ,

I created one DLL (Pick the Code from http://asstoredprocedures.codeplex.com/SourceControl/latest ) which will be used for "CurrentCellAttributes" . It is working fine when I am using in MDX. But How can we use in Rowset Action.

Below is the MDX Query which is working fine.



But when I am using "CALL [Drillthrough].CurrentCellAttributes()" in SSAS Rowset Action I am getting below error



Can you please help me on how to overcome the above error.

Hi,

Did any one get idea to solve above issue?