web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / SSAS Cube; define meas...
Microsoft Dynamics AX (Archived)

SSAS Cube; define measures

(0) ShareShare
ReportReport
Posted on by Community Member

Hi everyone,

can we get data from view as they are presented; wich mean without aggregation?

I need to get QTYSOLD ,ItemID, Store, TransactionDate from retailTransactionSalesTrans  Table.

I have defined my measure with aggregate function as “None” and all the values are returned with sum function

3487.p1.PNG

How can i proceed to fix this issue?

Thanks.

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Lance [MSFT] Profile Picture
    Lance [MSFT] on at

    Do you actually need an aggregate on those fields?

    If all you need is the field value with no aggregation, then change the AnalysisUsage property to Attribute and remove the AnalysisDefaultTotal setting.  This should create a dimension for your view with the field as a dimension attribute.

    If you need both the sum or count of some value and the raw value, then set AnalysisUsage to Both.  This will create a dimension with a dimension attribute and also create a measure group with a measure using the specified aggregate function.

    There is one drawback though of defining an attribute on a view that is expected to have a large number of rows.  When processing the dimension attributes values, Analysis Services will run a query to select distinct value from that field from your view.  When the view row count is large or the view query itself is slow, that distinct count query will take a while to run.

    Hope this helps,

    Lance

  • Community Member Profile Picture
    Community Member on at

    Thank you for replying,

    In fact i have tried your presposition; i have tried with attribute, mesure, both..and i have set  remove the AnalysisDefaultTotal as "No"

    View contain: store-color-itemid-transactiondate-quantity.

    I need quantity with no aggregation per product,color,store in the available transactiondate.

    I get usually the sum of the quantity in an  specific date , none the quantity per product per date as it shown in my view. This is my problem

  • Lance [MSFT] Profile Picture
    Lance [MSFT] on at

    What query are you using to get the data from analysis services?

    As far as getting analysis services to group results by more categories, it sounds like your query needs to include dimension attributes for store, item, etc, and not just the dimension attribute for date.

    Do you have dimensions defined in your analysis services project for Stores, Items and Colors?   And is there a relationship defined between the measure group for this retail sales transactions table and the tables in which Stores and Items are defined?

    If yes, you should be able to add those attribute to your query and then analysis services should give you back data grouped by all those categories, and not just grouped by date.  

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Priya_K Profile Picture

Priya_K 4

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#3
Ali Zaidi Profile Picture

Ali Zaidi 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans