Modifying the Cube SQL

Question

Modifying the Cube SQL

CinnamonGirl

Old Hand

Points: 372
More actions
May 28, 2004 at 9:22 am

#152159

Hello all,
I am trying to build a cube in AS and when I join my Time Diminsion, I am getting duplicate records. I have stopped the cube processing and clicked on the "View Details" button to look at the SQL that is creating the cube. Can anyone tell me how to modify this SQL? I think if I can manually write the SQL Statement then I can fix my issue.
On another topic, has anyone ever tried to create an offline cube with a time dimension created by Microsoft Accelerator for BI? I can't get it to work (I think because of the current flag in the Time Dimension tables), I created my own time dimension leaving out the current flag, which is what I am trying to use in the above scenerio. However, I am open to all suggestions if anyone knows of a better way to create an offline cube.
Thanks in advance for all suggestions, guesses, brain storms.
Robin

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Keith Henry Hall of Fame Points: 3759 More actions · Answer 1

You can't modify this SQL directly in Analysis manager. What do you mean by duplicate records? Can you send the spec of the cube you are trying to build?

I haven't used Microsoft Accelerator, but I have created offline cubes. You create a new oledb connection with a new .cub file location, a source data connection string and a create cube statement.

Hope this helps.

Keith Henry

According to everyone I know I "do something with computers?" for a living, so there you go.

CinnamonGirl Old Hand Points: 372 More actions · Answer 2

Hi Keith,

Basically, it looks like AS is creating an invalid join between my fact table and my time dimension.

The spec of my time diminsion is 4 main tables. Year, Qtr, Month, Day

and is joined to my fact table by Payments.Std_Mon_Idx = DimMonth.Month_Idx. For some reason, the cubes starts processing rows and processes all the rows in the fact table, but then you can see the row counter go beyond the row count of the fact table and the aggregations are double or triple when the cube finally finishes.

The offline cube part of this question is not really the ability to create the .cub file per se. I have done that in the past with cubes that do NOT use the time dimension that was created by Accelerator. Accelerator has a flag in the time tables for 'current' and anytime a cube uses the time dimension, the server goes out to the database and checks to see what the current date is in the tables. I believe that is why one cannot create an offline cube that includes that dimension. Now, I have created new time dimension tables excluding that flag and I am trying use a dimension based off of the new tables.

Thanks for your help!!!!

Keith Henry Hall of Fame Points: 3759 More actions · Answer 3

Check that the month ids are unique to each year, to avoid cross joins.

For dates the best bet is useually a date field that you format into YMD. AM has a wizard to help with this.

I avoid star structures unless I need them to speed rollups or control complexity

Keith Henry

According to everyone I know I "do something with computers?" for a living, so there you go.

Keith Henry Hall of Fame Points: 3759 More actions · Answer 4

As for the "offline" cube, do you know how that is stored? I think it might still be the same format as local cube files as they are what Excel uses for Pivot tables also.

Keith Henry

According to everyone I know I "do something with computers?" for a living, so there you go.

CinnamonGirl Old Hand Points: 372 More actions · Answer 5

Thank you so much for your input. I ended up leaving off the DAY table when I built the time dimension and everything worked as it should. Apparently the data in that table is the culprit. I will do more investigation as time permits, however, in the specific day is not neccessary in the cube I am building, so not including it will have little impact.

Thanks Again,

Robin