March 27, 2006 at 8:54 pm
Hi All,
I have a fact table with 300 million rows.
Please let me know how i can index it.
can i use index views.
I am using SQL Server 2000.
Thanks for all your help.
Seema
March 29, 2006 at 4:49 am
I'm quite new to this myself having been working on AS for the past year and a half so I'll do my best to help: Could profile building the shared dimenions and create indexes recommended by tuning advisor, as you're likely to create partitions with that amount of data probably the key one would be on the fields you define the partitions by. Profiling will slow things down a fair bit so might want to change views to a subset of the data for this exercise. Indexing the views seems to make sense although if your looking at 2005 then views arent as essential as in 2000 becasue of the udm so you can then index the tables. Guidance with this kind of stuffs pre
Phil Nicholas
March 29, 2006 at 7:06 am
You have several ways to improve performance of AS processing in AS2000. First, the obvious - don't do a full process if only a "small" number of rows are new. Use an incremental process. An index on the column(s) that determine what is new would be helpful.
Second, if only new/current data need be processed, you can partition the data in Analysis Services. Perhaps data is naturally partitioned by date or some other key. Each partition becomes a managable size.
Third, design your aggregations around the AS queries that will be used, if possible. The fewer the aggregations, the less work AS has to do. You can see the usage-based stats and tune just for queries that are used most commonly.
All of the above are true regardless of what indices you have on the base table(s). You can try to optimize the actual retrieval of data by simply optimizing the SQL that AS will use to get the data. When you start to process, AS will actually show you the query it is running. Build the indices needed to support that query - no trace is needed.
Does this address your question, or did I misunderstand?
CynicalDBA
Larry
March 29, 2006 at 7:40 am
Hi,
Thanks very much for your response.
Well i am not worried about the processing time. It is quite reasonable.
Only when trying to access the drill downs and the cube through data analyzer its really slow.
When i do a count(*) on the fact table that is also slow.
And the table is pretty stagnant, doesnt change much.
Please recommend me how to index that.
Fact table and dimension table indexing.
I will try the AS improvement you suggested.
Thanks,
Seema
March 29, 2006 at 7:50 am
I will assume that you have nothing inherently wrong with your cube and dimension design. Not always a safe assumption, I know.
If you are using MOLAP storage, then indexing the relational tables has no impact on OLAP queries as the data is already stored (again) in AS. Now you are talking about aggregation design and/or general cube design. My comment earlier about usage-based aggregations should be a big area of focus.
You can also do things to remove the amount of unnecessary work done by AS. For example, if your data spans several years but no one ever looks in a single query at all time, you can disable the ALL level in your time dimension(s). Any dimension levels you can eliminate will help as the aggregations created will (hopefully) better serve your queries.
I hesitate to go any further until you address any aggregation issues.
COUNT(*), a relational operation, is not related to Analysis Services issues. You may wish to repost your indexing questions under the Performance topic.
Good luck.
Larry
March 29, 2006 at 7:58 am
Hi,
Thanks very much.
I will try AS tuning.
Regards
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply