0

I am re-designing a data model (in SQL Server) that will be populated via survey data and then analyzed in Power BI. I have limited experience with Power BI, so wanted to get some input on some questions please:

Money Columns
Several of the survey questions are money fields -- always in USD. The original data model was storing this data in columns of MONEY data type, but after doing some research it appears the prevailing sentiment is that it's better to use the DECIMAL data type to avoid any rounding errors in calculations.

I am planning to use DECIMAL(19,2) since the currency will always be in USD. Does this sound correct?

Other columns
For reference, the other columns I have so far are of type INTEGER, NVARCHAR, DATE, and FLOAT.

I'm assuming Power BI will be able to handle these conversions, but would like to know what are the ideal physical data types and precision to ensure the best compatibility and performance?

This is on SQL Server 2019. Thank you.

ravioli
  • 3,749
  • 3
  • 14
  • 28
  • 2
    Though you should be aiming to use the correct data types (numerical data types for numerical data, date and time for date and time, etc) these have minimal impact on the performance of a database. The real performance comes from the ability to get the data quickly, with well written SARGable queries, good indexing, maintained statistics, etc. (Side note, there aren't many cases to use a `float` though). – Thom A Sep 21 '21 at 16:30
  • 1
    For Power BI the discussion is largely irrelevant if the data is imported (as opposed to using Direct Query) because Analysis Services (the engine underlying Power BI) does not have a `decimal` type (or more accurately `decimal` types, plural, as each combination of precision and scale is technically a separate type). It does have a `Currency` type, which not coincidentally is identical to `money` as far as storage, range and precision goes. That does not actually mean your database should also use `money`, though -- it means the PowerBI type should not dictate your database type. – Jeroen Mostert Sep 21 '21 at 18:10
  • Thanks. In this case, I believe the data will be accessed directly from the DB. According to the BI documentation, there is no explicit `CURRENCY` data type - https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-data-types - it just references `fixed decimal number`. I haven't used Power BI yet, so I'm just going by the documentation here. Where do you see the `currency` data type in Power BI? – ravioli Sep 22 '21 at 13:32
  • `fixed decimal number` is just the interface name for what Analysis Services calls [`currency`](https://learn.microsoft.com/analysis-services/tabular-models/data-types-supported-ssas-tabular). You'll notice that it has the same range and precision as `money`, per the docs you linked: "If you’re familiar with SQL Server, this data type corresponds to SQL Server’s Decimal (19,4), or the Currency Data type in Power Pivot." Power Pivot of course is the predecessor technology of Power BI; in both cases the underlying database engine used is SSAS in tabular mode. – Jeroen Mostert Sep 22 '21 at 18:07
  • Interestingly and misleadingly enough, the claim that this corresponds to `DECIMAL(19, 4)` is of course wrong -- a `DECIMAL(19, 4)` can hold larger numbers than `MONEY` (and hence `fixed decimal number`) can, even if the slightly different range isn't likely to matter much in practice. – Jeroen Mostert Sep 22 '21 at 18:10
  • Thanks for the insights. I am confused now, though, since the `Power BI` documentation makes it seem like there is no `CURRENCY` data type. So, does it make sense to store the physical data as `MONEY` or as `DECIMAL(19,2)`? Or does it not really even matter? – ravioli Sep 24 '21 at 16:15

1 Answers1

0

Yes, you can use decimal data type for money column, use without any currency signs.

Next go to data modelling and select column , in here you can you can choose any currency sign for that.

enter image description here

enter image description here

Mr Robot
  • 307
  • 2
  • 12