July 2010 - Posts
What I miss with SSAS cube deployment (regardless of MSSQL Server version) is all sorts of statistics where one could track and easily see how fast cube or partition is being processed.
Besides talking to several MVP's (three in general) and reading piles (well, four of five) of books regarding this issue, i still couldn't see where the problem was hiding.
In general four basic components are related when it comes to SSAS Cube process and deployment:
- Building Indexes - this time depends on SSAS service/server
- Building Aggregations - also this time depends on SSAS service/server
- Building partitions - also this time depends on SSAS service/server.
Both are available within deployment process with guided GUI. Neat and nice. But the third one was almost mysterious when i realized it had a huge impact on cube process and deployment.
- Reading the query on the source table / database - this time depends on Sequel server.
Which is so obvious, that i never thought about it. But it makes sense, that processing the cube is just querying the data and preparing aggregated data for faster browsing. So how to diminish the process time with the last one.
Fairly simple - optimize your fact table and all relating tables (star/snowflake) for all the dimensions and hierarchies to read and retrieve the data as fast as possible.
Of course, one can also lose couple of dimensions and hierarchies and cube process will also be dimished. As well as delete couple of million rows :)
Facts: I have couple of OLAP cubes (each holding cca 100mio records) and it took me on daily basis up to 1,5 hour per cube for processing and deploying. But keeping in minds the last rule, I did some query and table optimization and process time decreased dramatically. Now i need less then 30 minutes per cube to process and deploy. And it saved me in general speaking up to one hour in the morning, so fresh data can be delivered one hour earlier.
SSRS in SQL 2008 R2 was a big dissappointment. I couldn't event though about it that Microsoft developers couldn't improve and upgrade two most important things:
- managing reports and
- managing access control.
If one has only 5 reports this concern is absolutelly out of the question. If one has 500 reports for 1000 users created in general for 30 and more countries than you have a huge problem. SSRS was not design to think big. I see SSRS as a "by the way" solution and service with many handy tools, extreme usability and couple of "bugs".
But this two bugs have been bugging me since 2000 release.
Managing reports was never easy. One can use ReportServer database (on SSRS native instance) where tables like dbo.Catalog and dbo.Policies, dbo.Rolesl, dbo.ExecutionLog come very hand, but just will not do the trick.
for example: If you face migration of report server (which is not so obsolete problem or task) there is no way one can by one click or with one query get for all report's data sources. DataSource is by default embedded into report and it is absolutely impossible to get it from the database nor from RDL file. on top of that. you sure can get all the users that have been using the reports, as well as all the rights that user has; meaning one can get a list of all the reports that user has access granted. Thank God. But imagine following. After tidiou task of migrating all the reports, now i have to click again and again through each of the report to see the rights for each user. I'd rather kill my self then do this. And what's even worse is, that adding new user to SSRS can only be done via Report Manager by clicking and writing user's AD name. Again, if I have 5 reports i would not even think of this. But managing 500 reports is not comprehesable.
For all three reasons stated above, i will not argue, there are couple of workarounds that i have found, but are relatively useless when one go on bigger scale. What's even worse is that average life span of a report is up to 6 months in our system.
Creating synonym is child's play. But behind this child's play is much more.
My situation last week was that on our datawarehose SQL server, we hade more than 25 databases which were exact copies of the tables coming from the sources (a source is defined as end application in each of the countries), where each source has the same database design and same database schema.
But since the new replication and mirroring design played some role on changing the existing name of all the tables, this created several problems.
1. One must change all the queries in hard-coded situations (Report Manager, Analysis services)
2. One must change all the ETL processes (Integration services)
3. More or less one should also alter all the views, procedures, functions, etc.
And all this just because the naming of the table with this new design has changed. To clarify:
before one would write: SELECT * FROM MyTable
but in current situation, one must write: SELECT * FROM new_ETL_design_MyTable.
No one really thought about the impact of this changed "naming convention". We all said to ourselves, well it should be one time change only, as for all the DBA's, they will eventually get use to it. But it wasn't until last week that co-worker of mine started complaining that new naming off all the tables is one one hand time consuming, not excatly prone to typing errors and it's not actually mnemonic. Not to mention his long tidious hours that he spend on changing all the queries.
And then it dawned on me. Why not create Synonym for all the new namings and just simply adapt the naming to old naming.
In a matter of seconds, i introduced the solution and everybody felt the immediate happines. It took me about one hour to write dynamic query to go through all the databases and through all the tables to create the synonyms but at the end the we already felt the success of such a simple solution.
Now, we do not need to deal with new naming convention, mainly we do not need to change all the queries, the reportings, the ETL, the procedures, etc.
Synonyms in this particular case, saved us weeks of work. simple solution was still the best one :)