.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

How to create buckets on measures from 2 fact tables, i.e variance between actual and budget coming

Posted By:      Posted Date: April 14, 2011    Points: 0   Category :

1. to put the gist of the problem, i am trying to make an expense analysis report that compares my actual with its budget.

2. actual is coming from 1 fact table e.g. fact_general_ledger (grain = voucher number) and budget is coming from another fact table (grain = account [much higher than voucher number])

3. i have to calculate the variance (actual minus budget) at an account grain and make a percentage measure. hence i will get data like

Account     Var%

a1             -115%

a2              -5%

a3              7%

a4              20%

a5              22%

a6              35%

a7              45%

a8              57%

a9              95%

a10      &

View Complete Post

More Related Resource Links

Relationship between a dimension - two fact tables

I have 3 fact tables and about 10 dimensions. I want to relate both "Fact_Pop1" and "Fact_pop2" to Dim_AgeGroup. But Fact_pop1 has single age group and Fact_pop2 has no single age group (5 years age group). I spent about a day on this but I could't solve it.   Fact_Pop1 Age        Race_key            Gender_key      Geography_key              Pop_size 10           White                    M                                            CA                          10000    10           White                    F        

How to create HTML from XSLT with actual formating

Hi, I am creating HTML script using XSLT using an XML file. I am creating that input XML file from the database values. The database values are contains the data with some html formatting tags like <b>values</b> etc. for Ex:I have the table.EmployeeID CommentsEMP101   Some normal comment with <b>Value with bol</b> I required to create XML file with the fomatted comment, but when I create XML it contains &gt;&lt; etc., like <Employees><Emp id="EMP101"> <Comments>Some normal comment with &lt;b&gt;Value with bol&lt;/b&gt;<Comments></Emp></Employees> When I create HTML using XSLT, the values again contains the &gt;&lt; characters instead of the actual formating what the database stores. Please let me know how to solve this issue. I required the output HTML will be inthe same format what the database stored. Many Thanks,Kelvin.

Dynamically Drop and Create tables, overcoming 4000 charachter limit

We have a Navision SQL-server database for 7 companies. Each table starts with the name of the company. Except for the CompanyName-part, the tables names are equal. I made a Foreach loop that dynamically transfers tables/data from the the Navision-database to a staging-database. I also want to Drop and re-Create the tables dynamically. However, if I try to do that via an expression in a SQL Task, SSIS complains about the 4000 character limit. What is a better way to do this? I want to execute SQL-commands and have SSIS replace part of the tablename with the contents of a variable (being the name of one the 7 companies). I have no access to the Management Studio or the DB so I need it to be done within the package. What is the best way to do that?

How to create a SSIS package to import records form SQl Server 2008 tables to text files

I am a newbie to SSIS and would like to create a package to accomplish the task referenced in the above title. Will appreciate any links and pointers in the right direction.   Thanksakoranteng

SharePoint 2010 + RBS.msi does not create tables ("mssqlrbs") at the content DB

Installed on W2K8 SP2 + SQL 2 K 8 R2 CTP November + SPS2010 beta. SQL instance by default MSSQLSERVER and WSS_Content default content database. Step by step following installation and use of remote Blob Storage capacity documentation:http://technet.microsoft.com/en-us/library/ee748631(office.14).aspxThe RBS.msi, at the log file component installation it seems that installation is correct, but displays the error:... Executing op: ActionStart(Name=FixFilestreamStoreConfig,,)Información 2769. El instalador ha encontrado un error inesperado. El código de error es 2769. La acción personalizada CreateFilesNoUI no ha cerrado 21 identificadores MSIHANDLE.... Executing op: CustomActionSchedule(Action=FixFilestreamStoreConfig, ActionType=3070, Source=BinaryData, Target=RepairProvider, CustomActionData=filestream;C:\Program Files\Microsoft SQL Remote Blob Storage10.50\Provider Libraries\Filestream Provider\FilestreamProviderConfiguration.xml)...The content database have not created the necessary tables "mssqlrbs" and running the command in the SharePoint Shell: $ rbss.Installed () returns "False" I have not found a walkarround or anything let me to implement tables and simulate the installation that performs the .msi, provided in the Feature Pack for Microsoft ® SQL Server ® 2008 R2 November Community Technology Preview (CTP)Any h

Create view that amins to tables of another database on the same sql server instance

Hi to everybody, I found a situation ever met before. I develop on Dynamics NAV 5.01 and I have developed a method to be able to see some particular tables of an external database. In substance it deals with a property of tables of Dynamics NAV. When I create a table in NAV, I can create it in 3 different ways: table common to all the companies table  for company or table linked to a view.    This last case is mine, on the same db of NAV I have created a view with some fields, I have created in NAV a table linked with equal fields and types. Until here all normal.    The view, however, aims to another database that doesn't center anything with NAV but that it is on the same intance of SQL server.    The consumer that accesses NAV is a consumer type database SQL Server and has the permitted db_public and db_datareader on both the database. Then he can read the views on the db of nav both on the db of the other database.    When it tries to enter from the console of sql server, with the consumer database, all it works, if I do it by NAV, it show me an error "The server principal "username" is not able to access the database "some_database_name" under the current security context. (Microsoft SQL Server, Error: 916) "    If I add on the database NAV to the consumer, the role db_owner,

How to model a cube when actual and budget have different granularities

My budget and actuals occur in different levels. I am using Pivot Tables in Excel to load this from a 2005 SSAS cube & display these figures.  The problem is, when I go the leaf level (lowest for actual and under leaf for Budget) I get the correct Actual but the budget replicates the parent value. For instance: I have a Sales Budget which has been defined at a geographical State level, but the Sales Actuals occur at the geographical city level. I've built a cube with a Geography dimension: Country - State - City Measures: Actual, Budget when I use Excel 2007 to browse the cube via a Pivot Table and I drill-down to State (New York) it  correctly shows me  Actual: 10,000  Budget: 20,000 But If I drill down one more level I will see for Actuals: Princeton:  6,000  New Jersey: 4,000   and for the budget I will see both Princeton and New Jersey presenting 20,000.  How can I replace the Budget value with N/A or at least NULL or zero?    

Join two fact tables




I have the following two fact tables, upon which a cube was built. As you can see, these two tables have most of the common (shared) dimensions except for ProductTypeID (product dimension) on fct_productsales table. The relationship between these two fact tables is 1(fct_loansales) to Many (fct_productsales).

When I browse these measures from the cube, they look fine (and break down perfectly) through all the shared dimensions.

However, when I try to filter or browse through product dimension (along with other dimensions), the measure values from fct_productsales will filter down correctly, but the values from fct_loansales does not change.  I tryied to create degenerate dimension from fct_loansales, but no luck. Any help is appreciated.



AccountNumber                       char

How do I create a query that joins two tables and returns a single entry with the most recent update


Say I have the following two tables (Purchase & Customer). 

CustomerID StoreName PurchaseDate
1 Asheville 9/14/2010 15:49

How to create tables from XSD schema?


I am newbie for xsd/xml.
Can you give me an idea how to create tables from the xsd at below.


<?xml version="1.0" encoding="UTF-8"?>
<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="listings" type="listingsType"/>
  <xs:complexType name="componentType">
      <xs:extension base="xs:string">
        <xs:attribute type="xs:string" name="name" use="optional"/>
  <xs:complexType name="addressType">
      <xs:element type="componentType" name="component" maxOccurs="unbounded" minOccurs="0"/>
    <xs:attribute type="xs:string" name="format" use=&qu

Loading Dimension & Fact tables


Hello Experts,

I am new to SSAS & I need to load Dim & Fact tables for a data warehouse. I've the basic idea to load them, but i dont have the exact picture to load a fact table. For eg. if we have 3 dimension tables like Dim_Time / Dim_Geography / Dim_Product and one Fact Table Fact_Product as described in most of the online examples. Then how to load them. I know for fact Tables we need to do aggregations. But how to apply those aggregations on what criteria.  In the said example if we need to load Fact_Product then we should be able to see sales by product, sales for a given point in time & sales for a given geographic location. Then how to do that. Do we need to apply aggregate for all the dimension tables

I know i am not much clear, but i hope you guys can understand. If not please let me know i will try to explain it more. Please clear my doubts.

Thanks & Regards,


How to create Fact Table and Dimention Table in SSAS 2005 ?


Hi friends,

I am very new to SSAS world.

My question, can any one guide me , how to create Fact Table and Dimention Table ?


PK to PK relationship between Fact and Dimension tables


Hi guys,

Is it possible to define a relationship between a Fact Table and Dimension Table using a PK to PK relationship rather than a PK FK relationship?

The reason I ask is that I'm working on building a cube, in which the tables I think I need to use as dimension tables only have matching Primary Keys in the current RDBMS, not a FK.

Is there any reason why I shouldn't use a PK to PK relationship?


How and if to create a (degenerate) Fact Dimension


Hi, need to know how to fully create a degenerate Fact Dimension. In a cube with the following Fact table:


TranxID (PK)

RepArea (PK)

RepCostCentre (PK)

RepOpsType (PK)

FinYear (PK, data is 2010, 2011 etc - Fact Dim?)

FinMonth (PK, data is 1, 2, 3 ... 12 - Fact Dim?)


AssetNo (poss another Fact Dim for Drillthrough)
TranxCost (Measure)


...the Primary Key fields have their own Dimension Tables apart from the Financial Year and Month - it seems overkill to have additional Dimension Tables for these because the data is already set as per shown (and the key values are small enough to act as part of the composite primary key) and the users do not need to see any lookup (ie. integer key value to lookup the month name, they would be happy with 3 for March). So, it seems quickest to just use them as degenerate Fact Dimensions. Same could also be said of the AssetNo, where a browsable fact item is a common requirement I beleive (although I appreciate that normally any item with repeated items, ie. one-to-many not one-to-one,  should be dimensioned through a table so it can be diced up properly).

Question: How do you set a new Fact Dimension so you can Browse by it? Folk here point to BOL but 2005 merely identifies the relationship without showing how to create one, and 2008R2 BOL says even less! My (2005

Can I create multiple report tables from one Dataset?


I'm producing a report in Report Builder 3.0 that needs to show events grouped by time of day, and another table with the same data grouped by day of week. Both tables show the same results, just grouped differently. The query that generates the ungrouped data is expensive, so I don't want to run it twice.

I've created queries in Management Studio that get the ungrouped data into a global temp table, and then I can fetch both grouped data sets from there. Unfortunately I can't get this to work in Report Builder. I have one Dataset that runs the main query, saves it into global temp, and then returns the first result set. The second Dataset returns the results from global temp. This keeps failing, the second result set is blank.

Does Reporting services run the queries in the order they are in the report, or are they run concurrently? If they are run concurrently this would explain it. Is there anything I can do to change this so that the second query doesn't run until the first one has completed?

Is there a better way of achieving the results I'm after?

How to combine 3 Fact Tables into 1: UNION in TSQL vs. MDX Scripts


Hi All,

I have 3 Fact Tables I would like to combine them into 1, I am not sure what's the best way to do it.

In Adventure Works, it uses UNION in TSQL to create a sales_summary Fact from Internet Sales and Reseller Sales.

Can I just write a simple Calculated measure?  What's the best way to handle this kind of situation?







[Distribution Partner].[Distribution Partner].[DP]

Problem with create ActionResults using 2 tables


I have a create actionlink the data that I'm adding belong to table name

One of the fields (name company_Id), is connect to table name company,(one2many
table),  So, if I'm trying to write data that already exist
in the company table , the model is invalid ,

Ne w company name,it is valid, how can write a new data entry and still
use the company table existing data entry

I'm adding my code so mybe my Q. will be more understandable ,


public  interface Irepository
       IEnumerable<company> list1();

       IEnumerable<cosmetic> list();
       cosmetic Get(int id);
       void Create(cosmetic cosmeticToCreate, company conpanyToCreate);
       void Edit(cosmetic cosmeticToEdit);
       void Delete(cosmetic cosmetToDelete);

//the class
 public class CompanyRepository :Irepository
        SR_1Entities _entitiy = new SR_1E
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend