How do I create and use a named query in an SSAS cube.

Posted on 2011-10-24
Last Modified: 2016-02-14
This named query creates a dimension with a count  of an item in my fact table:


I do not have a primary key on this.  What are my next steps to utilize this count column in the SSAS cube browser.
Question by:rhservan
    LVL 15

    Expert Comment

    Severla things here:

    A named query that's usable in a cube needs to be defined in MDX - this is in SQL.
    How do you expect to use a dimension without a primary key? (It doesn't look like you're defining a dimension either)
    Are you getting mixed up between a named query that you might define in the Data Source View to aid in selecting data from the underlying database(s) to populate your SSAS database and an MDX named query?

    What is your fact table structure (in SSAS, not SQL) and what are you tryignt o achieve with the named query - simply a count of non-empty rows?

    LVL 15

    Expert Comment

    hi dear

    you dont need a primary key, you can do it on ssas without a primary key

    are you facing any limitation any were...


    Author Comment

    I created the above by right clicking in the dsv and selected NewNamedQuery then entered the SQL. It then shows up in the DSV with No connection to the star.  I would like to use the count result in the browser to show counts by geographic location(this is already in place and working).

    LVL 15

    Accepted Solution

    in your NamedQuery if you are connected to just one table

    why dont you just connect to the table directly instead of a namedquery.

    i wouldnt recomment using a NamedQuery for your Fact.

    after you make a connection to your table, you can make relations to your dimensions, even if you dont have a primary key, i have some fact tables without primary key defined. but you can related it to its dimensions and create your cube.

    off course i don't recommend that, but i mean it can be done..


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Suggested Solutions

    Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
    If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now