[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

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

SELECT COUNT (DISTINCT ORDERNUMBERUNIQUE) AS COUNT
FROM FACTTABLE1
WHERE NOCHARGEAMOUNT > 0 AND NOCHARGEAMOUNT IS NOT NULL

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

Expert Comment

by:Tim Humphries
ID: 37023152
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?

Tim
0
 
LVL 15

Expert Comment

by:AmmarR
ID: 37023654
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...

regards
0
 

Author Comment

by:rhservan
ID: 37026706
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).



0
 
LVL 15

Accepted Solution

by:
AmmarR earned 2000 total points
ID: 37029386
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..


0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

872 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