Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Model Transaction Detail For SSAS


Model Transaction Detail For SSAS

Author
Message
SQL Dude-467553
SQL Dude-467553
SSC-Addicted
SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)SSC-Addicted (473 reputation)

Group: General Forum Members
Points: 473 Visits: 856
Dimension_Customer
CustomerId
Name

Dimension_Date
DateId
Date
Month

Fact_Sale
CustomerId
DateId
TotalSale

Let’s assume the application that saved the sale accepts a comment from the user (free-form and nothing we want to aggregate by). I would like to save this in the data warehouse so we can use Excel to show the comment for each sale.

Here is an example of how I would like to see the data in Excel. If this isn’t possible without a dimension then what about “right click” show details? What are the best options to model this?

January
TestUser1 $100.00
*Drill down to comments*
TestUser2 $200.00
TestUser3 $300.00
richykong
richykong
SSC-Enthusiastic
SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)

Group: General Forum Members
Points: 134 Visits: 621
If you need to access granular data that is mostly likely at the same level as your fact, there is no simple way to do it with SSAS.

I've actually had to do something similar to this and the method I used was to add a custom action that would run a SQL query against the SQL DB to return the rowset based on the dimension attributes.

If this is a must and you have the time to spend on developing it, you need to install ASStoredProcedures which will allow you to execute stored procedures.

Have the detail data stored on your fact table.

Create a stored procedure on your SQL database with the data that accepts dimension attributes (Customer.Name, Date.Month, Date.Date). Based on these attributed, the stored proc should filter out the Fact data and return the limited data set.

Create an Action in SSAS and use the ExecuteSQL to run the stored proc and pass it the dimension attributes. Setup the action as a Rowset.

If everything is configured correctly, you should be able to run the custom action in excel and get the data set from the stored proc.

The concept works, but I built this at my previous company over a year ago so I no longer have access to any of the code I used so I apologize for any inaccuracies.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search