This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(233.6, 7.000000000000001%, 32%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EC%3C/text%3E%3C/svg%3E)
Hi All,
My cube is taking forever to process, the FACT has 54 million and one of the dimension has 14 million rows. I cannot partition the FACT based on the year as there are many dates.
When i process the cube i see the following SQL and i want to know if there is a way to Edit the SQL
Thanks
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(32, 95%, 13%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAS%3C/text%3E%3C/svg%3E)
You cannot edit the whole of the SQL because its final version is generated by the engine, but it does minor changes such as adding selecting a constant 1 if you use a count measure. For the most part you can influence the code in wherever it comes from, query binding for partition / DSV level query / data source.
What seems more important is that the fact having many dates is by no means a block to partitioning the measure group.
And, you might be able to pare down that huge dimension by pre-filtering the values that do not appear in facts.
@Alexei Stoyanovsky thanks
So if i have 6 different dates
STARTDATE
ENDADATE
EMPDATE
CUSTDATE
PATDATE
SYNCDATE
In my partition i can do
select f.*
from fact f
inner join dimtime t
on f.startdatekey=t.timekey
where t.year=2007
Is that correct, i mean i can do partition on ANY date?
Thanks
Technically, you can partition based on any conceivable logic, a combination of fields even, as long as there's an M:1 relationship between fact table rows and partitions. When choosing, consider also your query patterns for partition elimination. Joins can be used but are not required, e.g. where startdatekey between 20070101 and 20071231.
Hi @Comanchi ,
Agree with AlexeiStoyanovsky, and maybe below links could help you:
SSAS - Best Practices and Performance Optimization
Improving cube processing time
Best regards,
Carrin
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.