The September release of Power BI saw the introduction of a powerful new feature: full text indexing, which makes searching and filtering on text columns much faster and enables the use of the new TextContains() and TextSimilarity() DAX functions. You need to enable it on a per-column basis and when you do that you have the option to rebuild the full-text index for those columns when you do a Full refresh or when you do a refresh of the type Indexes. So the big question is: how long does it take to build a full-text index on a column and what impact will it have on your overall refresh times?
Obviously the answer is “it depends” and you’ll have to test. So, to test it myself, I added a full-text index to a column containing street names (so therefore fairly short strings) in a semantic model containing data from my favourite public data source, the UK Land Registry Price Paid data. The Street column contained 73,206 distinct values and the table itself contained 160,257 rows. While refreshing the index I ran a Profiler trace to see what I could see and I spotted a pair of Progress Report Begin/End events with the subclass Tabular Object Processing that represented the index build:
Here’s what the TextData column for the Progress Report End event contained:
Finished building persisted full-text index for column ‘Street’ of table ‘Transactions’. (TableTMID=’14’, ColumnTMID=’28’)
For this particular refresh the index build took 1392ms (from the Duration column) and used 484ms of CPU Time (from the CPUTime column). So fairly quick, but I can imagine that on a column containing longer strings and with more distinct values then refreshing the index could take a significant amount of time. The docs specifically point out that significantly slower refresh times could result from adding full-text indexes to too many text columns.
Is it worth enabling full-text indexes right now? They can’t be used by Power BI reports yet, but as the last few weeks have showed, in the future there are going to be more and more front-ends of different types that can run any arbitary DAX against your semantic model beyond regular Power BI reports. Full text indexing opens up new types of analysis which, traditionally, Power BI semantic models were not good at and which are going to be more common in “chat with your data” scenarios: for example, imagine a semantic model containing hotel booking information and a text feedback column – you’re only going to be able to answer questions like “how many refunds did we give to customers who complained about air conditioning?” with a DAX query that uses the TextSimilarity() function. So, to answer my own question, yes I think it is worth exploring this feature and checking what impact it will have on your refresh times.