locked
How do I create a fact table? RRS feed

  • Question

  • I must be missing something here because Fact Tables are mentioned everywhere in relation to Powerpivot and data models, however there isn't a single place that I can find which describes how to create one.

    I am using only Excel 2013 and PowerPivot. I have created a datamodel that is based on simple tables from multiple data sources including local worksheet tables, workbooks stored in SharePoint and data coming directly from our MySQL database. I know want to create a fact table.

    From my limited understanding of a fact table, they are normal tables but with specifically chosen columns, is this correct or is there something programmatically different about them? If not, then how do go about generating one. All of the data I would want in a fact table is stored in various other tables that have already imported, so I need to take select columns from each one and merge them together into that single facts table, I do not however have any idea how to do this.

    Could someone please help?

    Thanks.

    Tuesday, August 20, 2013 3:57 PM

Answers

  • It's not the size of your data that determines the necessity of a DataMart or Data Warehouse.  It is your business requirements that determine the need for such.

    Fact Tables and Dimensional Tables represent a denormalization of your datastore in a manner that optimizes reporting and research in opposition to a normalized Data Store that optimizes transactional needs.

    Excel PowerPivot is designed to utilize the benefits of Datacubes which are in turn based upon Fact and Dimensional tables.

    But if you are not prepared to go the SQL-Server SSAS route, you might consider looking into MySQL's built in Pivot queries in order to restructure the data being output to meet your needs and then let Excel consume the results.


    Ibrahim Malluf MCP http://www.malluf.com

    Tuesday, August 20, 2013 5:55 PM
  • You might want to try one of the more Excel-oriented PowerPivot books and come back to the one you have after that. Here's one that I like:

    http://www.amazon.com/DAX-Formulas-PowerPivot-Simple-Revolution/dp/1615470158/ref=sr_1_1?ie=UTF8&qid=1377099669&sr=8-1&keywords=powerpivot

    -Ron

    Wednesday, August 21, 2013 3:43 PM

All replies

  • Fact Tables is a terminology from the Data Mart/Data Warehouse. Power Pivot works really great on top of such data models and that's why you might be reading about it. Search about Fact Tables, Dimension Tables, Data Marts and that should get you started understanding what they are. Generally, IT dept builds Data Marts and Power Pivot users gets access to those. 

    Please follow up if you've further questions.


    Paras Doshi (Blog: ParasDoshi.com | Twitter: @Paras_Doshi )

    Tuesday, August 20, 2013 5:00 PM
    Answerer
  • Thank you for your reply, I think perhaps our usage of PowerPivot is slightly unsual then and I guess I'm wondering if fact tables are still something I should be using.

    We are a relatively small business and do not have anywhere near enough data to be using Data Marts or Data Warehouses - at the same time we have some quite complicated relationships between the data that we do have which I've only managed to put together with PowerPivot - PivotTables and other BI tools didn't seem to do the job.

    Our data all comes from our MySQL database, however some of it has been pre-packaged into reports by our web developer which we then download and use to update local Excel Workbooks, these workbooks are then linked to our DataModel. We also use a few Excel workbooks which are integrated directly with our MySQL database too.

    Would such a model and selection of table require the utilisation of a FACT table, or is there something else I should be looking at? I'm having trouble at the moment needing where I have one-to-many relationships between tables which I really want to be reversed, but don't know how to.
    Tuesday, August 20, 2013 5:12 PM
  • It's not the size of your data that determines the necessity of a DataMart or Data Warehouse.  It is your business requirements that determine the need for such.

    Fact Tables and Dimensional Tables represent a denormalization of your datastore in a manner that optimizes reporting and research in opposition to a normalized Data Store that optimizes transactional needs.

    Excel PowerPivot is designed to utilize the benefits of Datacubes which are in turn based upon Fact and Dimensional tables.

    But if you are not prepared to go the SQL-Server SSAS route, you might consider looking into MySQL's built in Pivot queries in order to restructure the data being output to meet your needs and then let Excel consume the results.


    Ibrahim Malluf MCP http://www.malluf.com

    Tuesday, August 20, 2013 5:55 PM
  • Power Pivot was built to make it easier for folks to do business intelligence without having to master multidimensional concepts like facts and dimensions. If you do have such knowledge, you will be in a better position to do more advanced things and if you coming to Power Pivot with past experience in multidimensional concepts it's natural to map those concepts onto Power Pivot.

    In Power Pivot, any table in your model could be a fact or a dimension table (or both) depending on how the relationships and calculations in the model are defined. One way to think of it is that tables that act as fact tables are usually on the many end of a one-many relationship and contain numbers you want to aggregate or group in your PivotTable, chart, or Power View report. The real key with Power Pivot is to understand how relationships, row context, and filter context work.

    A helpful introduction to Power Pivot and DAX, its calculation language, can be found here:

    http://www.microsoft.com/en-us/download/details.aspx?id=11597

    The download consists of a white paper and an example workbook.

    HTH,
    -Ron


    Wednesday, August 21, 2013 12:53 AM
  • Thank you for the response. I haven't had a chance to download that link but I will do, it sounds like something I should definitely read, and I will.

    I have to say I very much fell into PowerPivot opposed to having set about learning it and it full capabilities from the outset. In our business we have slowly been working towards a more data driven approach, but part of that has been getting to grips with the data we have, attempting to organise in the best ways possible and trying to make sure we continue to record accurate, useful data going forward. For methis

    started out with working out how to track basic data in normal Excel spreadsheetsand from there I have steadily learned new skills; first it was the power of Vlookups, then it was Pivot Tables (a revelation at the time) and most recently it has been discovering PowerPivot,PowerView and the MySQL connection capabilities of Excel. Only now does it feel like we have the right tools to analyse our data even though at the moment we are definitely finding out way.

    As mentioned before our business datadoes not produce enormous amounts of data, but the relationships that we need to analyse can get very complicated and was the reason simple PivotTables were not sufficient.We do not have an 'enterprise' style infrastructure either, I've not heard of "SQL-Server SSAS" for example and no one within our business has experience with data analysis.

    In this context does anyone have any suggestions as to material I could read that would help me better learn the environments and context in which PowerPivot may be used. At the moment we are still finding it very useful even though we are probably not using it exactly as intended.  I would have like to develop my abilities with it and so any pointers towards books, or online materials would be greatly appreciated.


    I already have a book (http://www.amazon.co.uk/Microsoft-Excel-2013-Building-PowerPivot/dp/0735676348) and it is useful for certain things, however some of it is above my head.

    Thanks in advance.


    • Edited by Maracles Wednesday, August 21, 2013 7:32 AM
    Wednesday, August 21, 2013 7:31 AM
  • You might want to try one of the more Excel-oriented PowerPivot books and come back to the one you have after that. Here's one that I like:

    http://www.amazon.com/DAX-Formulas-PowerPivot-Simple-Revolution/dp/1615470158/ref=sr_1_1?ie=UTF8&qid=1377099669&sr=8-1&keywords=powerpivot

    -Ron

    Wednesday, August 21, 2013 3:43 PM