what if a fact table/view is a template(meant to contain only table structure but no data)?

views:

193

answers:

what if a fact table/view is a template(meant to contain only table structure but no data)?

I noticed that the fact tables used in a cube were actually views. Infact they were the templates of the fact tables (i noticed it in the script that "where 1=2" was used for the fact-views).

So, if the template is used, there wont be any data in the view at any cost (and i dont know if I can insert in the view becasue I dont have insert privilege in the view).

So, my question is: Am I missing something in the cube to look at? because the cube is designed by a very experienced dev and I am just a QA. The cube designing pane shows clearly that it s using the template (as it shows in the yellow header of each rectangular shaped object in DSV designer. Can it be referenced to any other table/view as opposed to what is being shown in the header?

Not entirely sure why this is a CW, but regardless, you're not missing anything. The fact table can be a view (and in fact, I do this a lot with testing).

What you are missing is that you cannot insert into a view because it's read-only (henceforth, "view"). You're looking at a SELECT statement from other table(s). Since it's abstracted, there's no way that you can insert data into the view--the view only reflects data in its source tables.

Eric 2009-06-30 21:12:00

Ok. I got you. But, what about a fact view being a template. Is it possible for a cube to process?

sagar 2009-06-30 21:14:09

What do you mean being a template?

Eric 2009-06-30 21:15:35

well i mentioned it in my title. Anyways, the view contains no data. It contains only column names. And it is designed that way (using select query...where 1=2). I am not seeing the point using that kinda view in a cube. Can a fact table/view be empty? I dont think so. Well, it could only if it has foreign keys. I hope I am clear.

sagar 2009-06-30 21:21:13

Yes, a fact table can be empty. It will just bring in absolutely no values (hooray for null!). You can still process dimensions, etc, and they will still be navigable. Just don't use the NON EMPTY clause in your MDX!

Eric 2009-06-30 21:27:32

Additionally, it seems that somebody would only do this to see if their dimensions process correctly, which is a bit odd, since you can process all of the dimensions and browse them within BIDS. Generally, facts are the easy part (relatively speaking), so it strikes me as odd that you aren't loading up the facts to test against. Validation is the most important thing...

Eric 2009-06-30 21:30:20

Great...Thanx a lot for the info...I am not the one who does all the loading. I QA the loaded data.

sagar 2009-06-30 21:33:56

If you dont mind, can you please tell me how do you validate the cubes' data and data mart's data. This is my first time on cubes. I was just using TSQL queries to get data from the data mart and comparing with that of cubes through cube brower.

sagar 2009-06-30 21:36:49

Generally, I go with an existing report, and match apples to apples. If the filters I'm applying aren't intuitive to get to a simple report and the numbers aren't right, something's wrong. Also, check the aggregate values. I trust that the cube actually processes all of the fact table, so there's no reason to test that. You can test the fact table with the source, though! That's certainly recommended!

Eric 2009-06-30 21:43:03

Isn't there any need to validate the dimension with the source? Dimension tables also contain the data.

sagar 2009-06-30 21:47:10

@sagar: Absolutely, and that's probably best done SQL to SQL. Of course, many times, dimensions have user defined hierarchies in them, making it hard to do an apples to apples. Make sure that you're only validating the leaves in this case, and identify a SME to validate the hierarchy.

Eric 2009-06-30 22:09:05

What does SME stand for?

sagar 2009-07-01 14:55:23

Subject Matter Expert--aka a very knowledgeable user.

Eric 2009-07-01 15:13:24

thanx...Eric...Your info has been a great help

sagar 2009-07-01 15:58:05

Hi Eric, I dint have any means to ask you directly. So I use this comment box for asking question to you. I hope you dont mind.

sagar 2009-07-08 16:45:45

My question is:I have a calculated measure. I need to validate the data of the calculated measure by writing a TSQL query (thas the only thing I know of so far). But, the problem is, the individual measures belong to columns of different fact tables, and there is no direct relationship between them. So, how do I know which columns to join to get the value as that of calculated measure? There are many common columns between them, but it is necessary to join all of them? What if I am looking fot the calculated measure over date dimension (MMMDD)?

sagar 2009-07-08 16:46:32

I can get the columns to use for select statement, but just dont know the columns to join.

sagar 2009-07-08 16:48:50

It may be a case for maintaining a many - many relationships developer might have used the fact table to match primary keys of 2 diff dimensions

paranjai 2009-07-08 11:13:07

ansaurus

tags:

views:

answers:

what if a fact table/view is a template(meant to contain only table structure but no data)?

related questions