what fact table is being used to creat dataset

I have reports that connect to SSAS cubes. Is there an easy way of determining which fact table is being joined in with the dimmesions to create the datasets.

I several fact tables that have the same fields so it is hard to determine for which is the correct fact table.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ValentinoVBI ConsultantCommented:
If you open your dataset, you can use the "Design Mode" button to switch from the graphical designer to the text editor.  This allows you to have a look at the MDX query behind the dataset and you'll see all dimensions and measures being retrieved.

Here's an article that should give you a bit more insight in this: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/A_2320-Your-First-OLAP-Report.html
vbnetcoderAuthor Commented:
I want to know what fact (measure)
table is being used since I have more then one. The MDX only says [Measures].[FieldName]
ValentinoVBI ConsultantCommented:
Ah, well in that case you'll need to look at the cube's design (SSAS project).  If you don't have that project, you can open an existing cube by launching the BIDS and then File > Open > Analysis Services Database.

The following assumes you're looking at the OLAP DB in the BIDS.

If you open the cube and select your measure, you can then use the Properties pane to look at its Source settings.  The table name that you'll find there is the "friendly name" as specified in the Data Source View.  To get the physical name of the fact table, open the Data Source View and locate your table.  In its properties you'll find the actual table name in the Name property.

So I guess that means there isn't really an "easy" way, but at least there is a way :)
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

vbnetcoderAuthor Commented:
Any way that i can tell when i am in bids in a SSRS project?
ValentinoVBI ConsultantCommented:
Not that I'm aware of (would have mentioned it if it was)...  The Data Source View is the only place in the OLAP database that has references to the physical tables, so you'll really need to check there.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vbnetcoderAuthor Commented:
OK Thank you.
ValentinoVBI ConsultantCommented:
If it's really so difficult to remember where the facts are coming from, perhaps it's an option to rename them to make it more clear?

Or to use measure groups?

I know, both require changes to the cube, so it may not be possible in your situation.  Just wanted to share the options.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.