What is the best way to configure a dimension model (preferably star schema) when we have the following requirements? 1. There is an Employees table (25 attributes) where we are required to make some of the attributes to SCD2. For e.g. Salary, LastSalaryIncreaseDate, LastBonusAmount, LastBonuesDate, Designation. We don't have to maintain the reporting hierarchy. 2. There are different Departments. Every Department is head by exactly a single Department head (Employee). 3. An Employee may belong to multiple Departments and vice versa. 4. Monthly payroll information is required to maintain for every Employee. **Understanding and Questions** 1. Should we split the Employees entity into two considering only 5/25 of the attributes are SCD2 based (historical)? 2. I suppose there is a bridge table required for the Employee and the Departments. So every employee must have an attribute (e.g. DepatementGroupCode) showing multiple departments in the bridge table. Correct? 3. There is a direct relationship between employees and the Department. So Department will have the attribute EmployeeKey in it. How do I deal with SCD2 changes of employees with respect to the Department entity? 4. The payroll periodic Fact entity will be linked only with the Employee and the date dimension. This should not be linked with the Department because it is already linked with the Employee entity...Please correct my understanding.

I have my fact table that has data in weekly basis, so I am mapping with Week dimension to pull records in Piviot table. My ssas cube doesnt show all data that is in fact. Should I add any specific scope to show in cube. for Example, My fact has data for week 201839 as well as 201840 (Sep month weeks) but in cube I can see only for one week 201839 but not for 201840

Take a SQL RDBMS data warehouse -- typical facts and dimensions type layout. Say you want Orders x Country. Maybe a date field, an orders field, a country field. And then if you want a report/ software that slices by Continent? Easy country x continent dimensional table, right? But what's the proper architecture for overlapping dimensions? For instance maybe you want "Greater Country" that includes the UK, British Isles, Island of Ireland -- ... these are larger groups that contain overlapping smaller pieces. Northern Ireland for instance is a component of both the UK and Island of Ireland. What's the best architecture so that if an end-user select "UK" or "Island of Ireland" -- that Northern Ireland pops up? Without duplication? I guess there are several ways to do this --- I don't actually care about the UK ha it's just an example -- You have a small component Dimension A. You have a larger grouping Dimension B but A is not unique to a single B. Every "cube" based reporting system is anti-thetical to this, but there are use cases. Nor do you want to create tons of dimensions. Is the best method simply a dimensions table that shows full membership -- aka Northern Ireland - UK, Northern Ireland - Isle of Ireland .... then do a join, return distinct? Doesn't seem efficient but maybe that's the best way.

I am trying to store vectors for word/doc embeddings in a PostgreSQL table, and want to be able to quickly pull the N rows with highest cosine similarity to a given query vector. The vectors I'm working with are numpy.arrays of floats with length *100 <= L <= 1000*. I looked into the cube module for similarity search, but it is limited to vectors with *<= 100* dimensions. The embeddings I am using will result in vectors that are 100-dimensions *minimum* and often much higher (depending on settings when training word2vec/doc2vec models). What is the most efficient way to store large dimensional vectors (numpy float arrays) in Postgres, and perform quick lookup based on cosine similarity (or other vector similarity metrics)?

Working with Postgres I noticed the following: When I run this query I get the total table size: SELECT pg_size_pretty( pg_table_size('tenk1') ); I get 384kb. And when I run: SELECT relpages FROM pg_class WHERE relname = 'tenk1'; I get 45 pages. I know that one page has 8kb. I confirmed that with SELECT current_setting('block_size'); Therefore, my table size should be 45 * 8kB = 360kB, instead of 384kb. Where does the difference of 24kb come from? What else could take up this space? I know that indexes are excluded by pg_table_size. But its TOAST table if any, free space map, and visibility map are included. Is there a way to determine their sizes?

I'm working for a business lender, and trying to come up with a basic dimensional model around the subject area of loan approvals. We use a CRM, (Salesforce) and one of the core objects is a (Sales) "Opportunity". Sales usually start off as a lead, if we get traction on a lead, we will create an account, at least one contact, and an "Opportunity" which is a potential loan (our business calls it a "Deal"). All of the loan approval process happens under a particular "Opportunity". I know that Account and Contact would be good dimension candidates, I am unsure of Lead and Opportunity though. I'm interested in opinions on which category Leads and Opportunities belong in: **Fact or Dimension**? Here is an example of some opportunity fields that may be relevant to a data warehouse:

lang-sql
    CREATE TABLE
      dbo.Opportunity
        (
            OpportunitySK                       BIGINT        NOT NULL
                                                IDENTITY(1,1)
                                                CONSTRAINT 
                                                  PK_dbo_Opportunity
                                                PRIMARY KEY CLUSTERED      
          , AccountId                           NCHAR(18)      NULL                              
          , ACTUAL_AMT_COLLECTED                DECIMAL(18,2)  NULL
          , ACTUAL_AMT_OFFERED                  DECIMAL(18,2)  NULL
          , ActualDailyPayment                  DECIMAL(18,2)  NULL
          , ACTUAL_MCA_TERM                     NVARCHAR(10)   NULL
          , ActualPaymentFrequency              NVARCHAR(255)  NULL
          , ApprovalAmount                      DECIMAL(18,2)  NULL
          , APPROVAL_DATE                       DATETIME       NULL
          , ApprovalExpirationDate              DATETIME       NULL
          , BrokerAccount                       NCHAR(18)      NULL
          , CreatedDate                         DATETIME       NOT NULL
          , CreditAnalyst                       NCHAR(18)      NULL
          , CreditProcessor                     NCHAR(18)      NULL
          , CreditSubmittalDate                 DATETIME       NULL
          , DocsInDate                          DATETIME       NULL
          , DocsOutDate                         DATETIME       NULL
          , eNoahCompleteDate                   DATETIME       NULL
          , ExternalId                          NVARCHAR(30)   NULL
          , FundDate                            DATETIME       NULL
          , FundedAmount                        DECIMAL(18,2)  NULL
          , Funder                              NCHAR(18)      NULL
          , Id                                  NCHAR(18)      NOT NULL
          , InternalCreditAnalyst               NCHAR(18)      NULL
          , LenderAccount                       NCHAR(18)      NULL
          , MerchantNumber                      NVARCHAR(32)   NULL
          , OfferInDate                         DATETIME       NULL
          , OfferOutDate                        DATETIME       NULL
          , OwnerId                             NCHAR(18)      NOT NULL
          , PhysicalAddress1                    NVARCHAR(1300) NULL
          , PhysicalAddress2                    NVARCHAR(1300) NULL
          , PhysicalAddressCity                 NVARCHAR(1300) NULL
          , PhysicalAddressState                NVARCHAR(1300) NULL
          , PhysicalAddressZip                  NVARCHAR(1300) NULL
          , PreQualDate                         DATETIME       NULL
          , RecordTypeId                        NCHAR(18)      NULL
          , RequestedAmount                     DECIMAL(18,2)  NULL
          , StageName                           NVARCHAR(40)   NOT NULL
          , StatementsInDate                    DATETIME       NULL
          , Type                                NVARCHAR(40)   NULL
          , UpForFundingDate                    DATETIME       NULL
          , WC_CreditDecisionRecord             NCHAR(18)      NULL
          , YearsInBusiness                     DECIMAL(18,0)  NULL
          , SystemModstamp                      DATETIME       NOT NULL
          , AccountExternalId                   NVARCHAR(100)  NULL
          , ACH                                 VARCHAR(5)     NULL
          , ACH_Debit                           VARCHAR(5)     NULL
          , ACH_DebitAmount                     DECIMAL(18,2)  NULL
          , ACH_QUALIFIED                       VARCHAR(5)     NULL
          , ACH_StartDate                       DATETIME       NULL
          , Amount                              DECIMAL(18,2)  NULL
          , AmountOffered                       DECIMAL(18,2)  NULL
          , Approved                            VARCHAR(5)     NULL
          , BalancePaid100PercentDate           DATETIME       NULL
          , BalancePaid50PercentDate            DATETIME       NULL
          , BalancePaid60PercentDate            DATETIME       NULL
          , BalancePaidatRenewal                DECIMAL(18,2)  NULL
          , BalancePaidPercent                  DECIMAL(18,2)  NULL
          , BalanceRemaining                    DECIMAL(18,2)  NULL
          , BalanceRemainingPercent             DECIMAL(18,2)  NULL
          , BOFI_Deal                           NVARCHAR(255)  NULL
          , BOFI_RestrictedState                NVARCHAR(1300) NULL
          , BookingStarted                      VARCHAR(5)     NULL
          , BrokerTier                          NVARCHAR(255)  NULL
          , CampaignID                          NCHAR(18)      NULL
          , ClosedLostDate                      DATETIME       NULL
          , CloseDate                           DATETIME       NOT NULL
          , CompanyDBA                          NVARCHAR(1300) NULL
          , ContractNumber                      NVARCHAR(32)   NULL
          , DateEstablished                     DATETIME       NULL
          , DateonDocs                          DATETIME       NULL
          , DaysbwAccountActivationDatePrequal  DECIMAL(18,0)  NULL
          , DaysbwActivationDateFundDate        DECIMAL(18,0)  NULL
          , DaysbwApprovalDateFundDate          DECIMAL(18,0)  NULL
          , DaysbwDocsinDateFundDate            DECIMAL(18,0)  NULL
          , DaysInApprovalStage                 DECIMAL(18,0)  NULL
          , DaysInOfferOutStage                 DECIMAL(18,0)  NULL
          , DeclinedDate                        DATETIME       NULL
          , Deposit                             DECIMAL(18,2)  NULL
          , EqAmount                            DECIMAL(18,0)  NULL
          , EqAmtLeased                         DECIMAL(18,2)  NULL
          , EqExperience                        NVARCHAR(32)   NULL
          , EqHasQuote                          VARCHAR(5)     NULL
          , EqLeasedBefore                      VARCHAR(5)     NULL
          , EqNew                               VARCHAR(5)     NULL
          , EqNumVendors                        NVARCHAR(255)  NULL
          , EqTerm                              DECIMAL(18,0)  NULL
          , EqUsed                              VARCHAR(5)     NULL
          , EqWhat                              NVARCHAR(64)   NULL
          , EqWhen                              NVARCHAR(128)  NULL
          , EqWhy                               NVARCHAR(64)   NULL
          , EqWithWhom                          NVARCHAR(64)   NULL
          , EquipCostForActual                  DECIMAL(18,2)  NULL
          , EquipCostPaidByLenderForActual      DECIMAL(18,2)  NULL
          , EquipCostPerDocRequest              DECIMAL(18,2)  NULL
          , EquipmentCondition                  NVARCHAR(255)  NULL
          , EquipmentGrandTotalSummary          DECIMAL(18,2)  NULL
          , EquipmentSeller                     NVARCHAR(255)  NULL
          , EquipmentStory                      NVARCHAR(255)  NULL
          , EquipmentYear                       NVARCHAR(255)  NULL
          , Fax                                 NVARCHAR(1300) NULL
          , FinalFundDate                       DATETIME       NULL
          , FiscalQuarter                       INT            NULL
          , FiscalYear                          INT            NULL
          , IsClosed                            VARCHAR(5)     NOT NULL
          , IsSplit                             VARCHAR(5)     NOT NULL
          , IsWon                               VARCHAR(5)     NOT NULL
          , LeadSource                          NVARCHAR(40)   NULL
          , LeaseNumber                         NVARCHAR(32)   NULL
          , LegalName                           NVARCHAR(1300) NULL
          , LegalStatus                         NVARCHAR(1300) NULL
          , LendersLeaseNumber                  NVARCHAR(32)   NULL
          , LendingTreeId                       NVARCHAR(9)    NULL
          , LendVantageCommission               DECIMAL(18,2)  NULL
          , LendVantageId                       NVARCHAR(255)  NULL
          , LoanStartDate                       DATETIME       NULL
          , LoanStartDateVerified               VARCHAR(5)     NULL
          , Locked                              VARCHAR(5)     NULL
          , MailingAddrDisplay                  NVARCHAR(1300) NULL
          , MailingAddress1                     NVARCHAR(1300) NULL
          , MailingAddressCity                  NVARCHAR(1300) NULL
          , MailingAddressState                 NVARCHAR(1300) NULL
          , MailingAddressZip                   NVARCHAR(1300) NULL
          , MarketingSubmissionDate             DATETIME       NULL
          , MaturityDate                        DATETIME       NULL
          , MerchantID                          NVARCHAR(32)   NULL
          , MonthlyGrossCC_Volume               DECIMAL(18,2)  NULL
          , MonthlySales                        DECIMAL(18,2)  NULL
          , MonthlyVisaMC_Volume                DECIMAL(18,2)  NULL
          , MonthsInBusiness                    DECIMAL(18,0)  NULL
          , Name                                NVARCHAR(120)  NOT NULL
          , NF_DeclineDate                      DATETIME       NULL
          , OpportunityID18                     NVARCHAR(18)   NULL
          , PartnerAccountId                    NCHAR(18)      NULL
          , PastDueBalance                      DECIMAL(18,2)  NULL
          , PaymentsBehind                      DECIMAL(18,0)  NULL
          , PaymentsBounced                     DECIMAL(18,0)  NULL
          , PaymentsCollected                   DECIMAL(18,0)  NULL
          , Phone                               NVARCHAR(1300) NULL
          , Probability                         DECIMAL(18,0)  NULL
          , ProcessorUserID                     NVARCHAR(32)   NULL
          , RateReviewCompleted                 VARCHAR(5)     NULL
          , Ratio                               NVARCHAR(32)   NULL
          , RenewalNumber                       DECIMAL(18,0)  NULL
          , ResubmitDate                        DATETIME       NULL
          , SplitFunding                        NVARCHAR(255)  NULL
          , SplitOpportunity                    VARCHAR(5)     NULL
          , SplitOpportunitywith                NCHAR(18)      NULL
          , Status                              NVARCHAR(255)  NULL
          , SubmissionDate                      DATETIME       NULL
          , SyndicationDate                     DATETIME       NULL              
        )

I am looking to populate a date dimension table in a SQL Server 2008 database. The fields in the table are as follows: [DateId] INT IDENTITY(1,1) PRIMARY KEY [DateTime] DATETIME [Date] DATE [DayOfWeek_Number] TINYINT [DayOfWeek_Name] VARCHAR(9) [DayOfWeek_ShortName] VARCHAR(3) [Week_Number] TINYINT [Fiscal_DayOfMonth] TINYINT [Fiscal_Month_Number] TINYINT [Fiscal_Month_Name] VARCHAR(12) [Fiscal_Month_ShortName] VARCHAR(3) [Fiscal_Quarter] TINYINT [Fiscal_Year] INT [Calendar_DayOfMonth] TINYINT [Calendar_Month Number] TINYINT [Calendar_Month_Name] VARCHAR(9) [Calendar_Month_ShortName] VARCHAR(3) [Calendar_Quarter] TINYINT [Calendar_Year] INT [IsLeapYear] BIT [IsWeekDay] BIT [IsWeekend] BIT [IsWorkday] BIT [IsHoliday] BIT [HolidayName] VARCHAR(255) I have written a function DateListInRange(D1,D2) that returns all the dates between two parameter dates D1 and D2 inclusive. ie. parameters '2014-01-01' and '2014-01-03' would return: 2014-01-01 2014-01-02 2014-01-03 I want to populate the DATE_DIM table for all dates within a range, i.e. 2010-01-01 to 2020-01-01. Most of the fields can be populated with the SQL 2008 DATEPART, DATENAME, and YEAR functions. The fiscal data contains slightly more logic, some of which is dependant on each other. For example: Fiscal quarter 1 -> Fiscal month must be 1, 2 or 3 Fiscal quarter 2 -> Fiscal month must be 4, 5 or 6 I can easily write a table valued function that accepts a specific date, and then outputs all of the fiscal data, or ALL of the fields even. Then I would just need this function to run on each row of the DateListInRange function. I am not highly concerned with speed as this will only need to be populated a few times a year when the table of holidays is altered. What is the best way to write this in SQL? Currently its like this: SELECT [Date], CAST([Date] AS DATE) AS [Date], DATEPART(W,[Date]) AS [DayOfWeek_Number], -- First day of week is sunday DATENAME(W,[Date]) AS [DayOfWeek_Name], SUBSTRING(DATENAME(DW,[Date]),1,3) AS [DayOfWeek_ShortName], DATEPART(WK, [Date]) AS [WeekNumber], DATEPART(M, [Date]) AS [Calendar_Month_Number], DATENAME(M, [Date]) AS [Calendar_Month_Name], SUBSTRING(DATENAME(M, [Date]),1,3) AS [Calendar_Month_ShortName], DATEPART(QQ, [Date]) AS [Calendar_Quarter], YEAR([Date]) AS [Calendar_Year], CASE WHEN ( (YEAR([Date]) % 4 = 0) AND (YEAR([Date]) % 100 != 0) OR (YEAR([Date]) % 400 = 0) ) THEN 1 ELSE 0 END AS [IsLeapYear], CASE WHEN ( DATEPART(W,[Date]) = 1 OR DATEPART(W,[Date]) = 7 ) THEN 0 ELSE 1 END AS [IsWeekDay] FROM [DateListForRange] ('2014-01-01','2014-01-31') If I do the same for the fiscal data there will be quite a bit of repetition in each case statement would could be avoided using a function and maybe cross applying the TVF over the list of dates. Please note I am using SQL Server 2008 so a lot of newer date functionality is minimal.

I'm trying to build a history table out of an audit log (ultimately to build out a type 2 dimension table). Unfortunately, the audit log only records the specific fields being changes. Here's a rough example of what I'm talking about; CREATE TABLE Staff( [ID] int, [Surname] varchar(5), [FirstName] varchar(4), [Office] varchar(9), [Date] varchar(10) ); INSERT INTO Staff ([ID], [Surname], [FirstName], [Office], [Date]) VALUES (001, 'Smith', 'Bill', 'Melbourne', '2015-01-01'), (001, NULL, NULL, 'Sydney', '2015-03-01'), (002, 'Brown', 'Mary', 'Melbourne', '2014-04-01'), (002, 'Jones', NULL, 'Adelaide', '2014-05-01'), (002, NULL, NULL, 'Sydney', '2015-01-01'), (002, NULL, NULL, 'Perth', '2015-03-01'); The first entry for a particular staff member is for when their record is created, and each subsequent record is an update... but only shows the update to the field that was updated*. I want to "fill out" the update row with the rest of the employee record as it currently stands. ie, a result like this; 001, Smith, Bill, Melbourne, 2015-01-01 001, Smith, Bill, Sydney, 2015-03-01 002, Brown, Mary, Melbourne, 2014-04-01 002, Jones, Mary, Adelaide, 2014-05-01 002, Jones, Mary, Sydney, 2015-01-01 002, Jones, Mary, Perth, 2015-03-01 I know I can do this using a while loop or a cursor but I suspect there is probably a more performant option. --- *A NULL always means "value didn't change" rather than "value changed to NULL".

One type of fact table in Dimensional modeling is Accumulating Snapshot fact Table. if you think you need to review the meaning and definition of this type , you can take a look at this article : https://www.holistics.io/blog/the-three-types-of-fact-tables/ . Imagine we have an accumulating fact table which has three important date column in it 'Order_Datekey , Manufacturing_Datekey , Ship_Datekey' When the table is being inserted for the first time ,there is no data for columns Manufacturing_Datekey and Ship_Datekey'. We only know the date in which the product has been ordered (Order_date). What I need to know is that how we can handle null values for Date columns ? The important point I read in Kimbal book is that null should be avoided in a foreign key column. 'Referential integrity is violated if you put a null in a fact table column declared as a foreign key to a dimension table' On the other hand we don't have any value for these two date columns at first. What do you suggest? Thanks in advance

so far this is what i have code wise, but im getting an error 1239 . Is it not referencing something correctly . CREATE TABLE CustomerDim ( C_CustomerID_Cu INT(10) NOT NULL, C_FacilityReservationNumber_Cu INT(10) NOT NULL, I_EventCode_Cu INT(10) NOT NULL, CONSTRAINT C_CustomerID_Cu FOREIGN KEY (C_CustomerID_Cu) REFERENCES CustomerDIM ); CREATE TABLE RoomDim ( C_RoomNumber_Ro INT(10) NOT NULL, C_RoomType_Ro INT(5) NOT NULL, C_RegionID_Re INT(4) NOT NULL, C__ResortCode_Fa INT(10) NOT NULL, CONSTRAINTC_RoomNumber_Ro FOREIGN KEY(C_RoomType_Ro) refereREFERENCES nces RoomDIM ); CREATE TABLE ReservationFact ( C_RoomReservationNumber_Rr INT (10) NOT NULL, C_CustomerID_Cu INT (10) NOT NULL, C__RoomType_Ro INT (10) NOT NULL, C_D_ArrivalDate_Rr DATE, C_EmployeeNumber_Em INT (5) NOT NULL C_ResortCode_Rs INT (10), CONSTRAINT C_RoomReservationNumber_Rr_pk PRIMARY KEY (C_RoomReservationNumber_Rr) );

I'm having a hard time figuring out how to create a lookup table or a table that references multiple dimension tables. I've got a "tracking" table that tracks the number of impressions for an e-commerce website. Each type of impression: manufacturer, product, evaluation is contained is a single objectid column, which references the corresponding dimension table for either manufacturer, product, or evaluation. What I am struggling with is: - manufacturer impressions = manufacturer + product + evaluation impressions - product impressions = product + evaluation impressions I need to be able to query all of the manufacturer impressions, which is a combination of the 3 types of impressions above. If I just query those that match on manufacturer, I will be leaving out the impressions that match on product and/or evaluation. The same thing goes for product impressions. What is the best way to create a reference table or to be able to query all manufacturer impressions? I need to be able to write a query that combines objectid = manufacturerid + objectid = productid (which has a corresponding manufacturerid) + objectid = evaluationid (which has a corresponding productid and then a corresponding manufacturerid that links to the productid). Here's a sample query I'm using, but it's not returning any rows when I add in the second join statement:

select * from tracking t
left join products p on t.objectid = p.productid and t.objecttype = 'Products'
left join evaluations e on t."ObjectID" = e."ProductEvaluationID" and t.[ObjectType] = 'Evaluations'
left join manufacturers m on t.objectid = m.manufacturerid and t.objecttype='Manufacturers'

Any advice is greatly appreciated, thanks!

I have four large dimensions and one of the dimension is not going to be changed, also the particular dimension didn't need to be processed. So, processing the three dimensions individually updates the cube or do I need to process the entire cube every time (which is processing all the dimensions) ?

I have a cube built in SSAS that uses 3 facts and 8 dimensions. Some of the dimensions are shared across 3 facts. When I process one of the dimension, it tries to process the underlying measure groups, however, as the other dimensions are not yet updated with the new data, the measure group processing fails saying the key doesn't exist. How should we trigger a dimension process in such a case? Illustration: 3 Fact Tables C1, C2, C3; 8 Dimensions D1, D2, D3, D4, D5, D6, D7, D8 C1 is built on D1, D2, D3, D4 C2 is built on D1, D5, D6 C3 is built on D1, D2, D5, D7, D8 Assume that I have made updates in the Dimensions D1 and D5 and trigger a Dimension process one after the other using AMO. When I process D1, it processes Dimension D1 and measure groups C1, C2 and C3. When it tries to process the measure group C2, it complains that the updated data in D5 is still not populated. When I Process D5 first, it processes Dimension D5 and measure groups C2 and C3. When it tries to process the measure group C3, it complains that the updated data in D1 is still not populated Note: - It's SSAS 2012 - We are processing dimensions using ProcessUpdate and process using ProcessFull Please do let me know if you need any additional information.

When browsing my cube it shows the correct invoice values by date when I select only a date type (the date table was generated by SSAS with hierarchies), but if I add the customer name it shows the combined value of the invoices for the date period, rather than the total per customer. I expect to see the total value for the customer that month, not the overall monthly total. My tables are joined with foreign keys and the DSV shows a star schema. A simplified version of the schema is below. factInvoice invoicePK customerFK -> customerPK dateFK -> datePK dimDate datePK dimCustomer customerPK The dimension usage tab shows both dimensions are linked to the invoice table. Please can somebody shed some light on why this is happening? I'm sure I'm missing something pretty basic here! Thanks.

How are dimensions typically modeled in a dimensional model if one dimension logically inherits from another dimension? For example, assume I have an Employee dimension and a Driver dimension, where the Driver has all the attributes of an Employee but also has a Truck Number attribute. How should these dimensions be modelled?

We have a fact table with the weight trasported by truck/driver/day. And a dimension with the max weight per truck. We would like to have the % of weight transported, so we started with create MEMBER CURRENTCUBE.[Measures].[% WT] AS [Measures].[Weight]/[Dim Truck].[Max_weight].currentmember.properties("key"), FORMAT_STRING = "Percent"; This only works for individual rows where one truck has only one leaf for the specific day and driver. It doesn't work if a truck does two trips in a day, instead of sum(weight)/sum(max_weight) or if you prefer sum(weight)/(n*max_weight) what we get is sum(weight)/max_weight Exactly the same occurs at any aggregate level. For example, month or year or total. Or simply at truck level. We have been playing with all sorts of formula and scopes without success. Any idea how to define it so the aggregates work as expected?

My cube has a product dimension, and each product has an "availability" status attribute. In the database, SellableItemAvailabilityId in DimSellableItemVersion (which contains products) joins to Id in SellableItemAvailability (which contains the statuses). See the image below for the tables used in the dimension. Each product also has a parent product. These are not the same as the products mentioned above (*this is not an SSAS parent-child relationship*) - think of them as ways to group related products together (e.g. a product style that comes in a number of colours & sizes). Each parent product also has an "availability" status attribute. In the database, SellableItemAvailabilityId in SellableItemParent (which contains parent products) joins to Id in SellableItemAvailability. Because of the foreign keys between the tables, the DSV automatically makes the relationships as seen in the image below. I defined the Product Availability attribute (i.e. the availability of products) with: - Key=DimSellableItemVersion.SellableItemAvailabilityId - Name=SellableItemAvailability.StatusName **However processing fails due to the attribute having more than 1 name for the same key.** I guess this might be due to products where the parent product has a different availability to the products inside it. *If the relationship between SellableItemParent and SellableItemAvailability is deleted from the DSV, processing succeeds*. I want to keep the relationship between SellableItemParent and SellableItemAvailability so I can add a "parent product availability" attribute to the dimension. What do I need to do to make this work?

I have a cube, called metric_cube, with a measure of Metric and a dimension Week with the given output on SSAS from SQL Server 2014. **Current Output:** Week Metric ---- ------ 1 6 2 5 3 4 4 3 5 2 I want to get the cube to output the sum of the Metric for all combinations of a from Week and to Week, where the from Week <= the to Week. **Desired Output:** Week(F) Week(T) Metric ------- ------- ------ 1 1 6 1 2 11 1 3 15 1 4 18 1 5 20 2 2 5 2 3 9 2 4 12 2 5 14 3 3 4 3 4 7 3 5 9 4 4 3 4 5 5 5 5 2 I have tried using the same dimension twice, but I get a cross join error. I have also tried creating two dimensions and applying both of them, but all that does is duplicate the Metric value from the current output for all to Weeks without consideration of the desired constraint for the from Week <= the to Week Is it possible to use a data cube to achieve the desired output? If so, how?

I have a parent-child-hierarchy with MemberWithData=NonLeadDataVisible. I tried different values for MembersWithDataCaption, but I always get name of datamember equal to name of inner node. I am using SSAS 12.0.5538.0. Questions: - Where to set MemberWithDataCaption, Parent or Key? - MemberWithDataCaption is a formatstring, how does it work? For simplicity I set the value "parent" without effect (after deploy and process). - What are the conditions for MemberWithDataCaption to actually work?

I have a dimension hierarchy that is related to multiple fact tables with different granularities. In the case of one fact table, it relates to the middle level of a three level hierarchy. When you drill down that dimension, I would prefer to have values at that granularity and nothing if you drill down further. The default behaviour seems to be just duplicating the parent total for each of the leaf members. I thought there was a property to control this but for the life of me I can't find it now.